现在触发器能访问基表了吗

AntTech_ZIPM1O · 2024 年9 月 20 日 17:31

【使用环境】测试环境
【 OB or 其他组件】
【使用版本】
Server version: OceanBase_CE 4.2.1.8 (r108000022024072217-3149c25ca2dadbb7707686ad02a1367b1b43e0b5) (Built Jul 23 2024 02:01:58)
【问题描述】在表行级触发器中，访问基表数据报错
【复现路径】问题出现前后相关操作
create table a(id int NOT NULL AUTO_INCREMENT,pid int,hierarchy varchar(200),primary key (id));

DROP TRIGGER IF EXISTS a_before_insert_trigger;
delimiter |
CREATE TRIGGER a_before_insert_trigger BEFORE INSERT ON a FOR EACH ROW BEGIN

declare parent_hierarchy varchar(300);
  
select  hierarchy into parent_hierarchy from v_a where v_a.id=new.pid;

END
|
delimiter ;

insert into a(id,pid,hierarchy)values(-1,null,’,0,’);
insert into a(id,pid,hierarchy)values(0,null,’,0,’);
insert into a(id,pid,hierarchy)values(3,null,’,0,’);

【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

论坛小助手 · 2024 年9 月 20 日 17:32

根据您的描述，您在使用 OceanBase 4.2.1.8 版本时遇到了在创建表行级触发器时尝试访问基表数据时报错的问题。

推荐使用 obdiag 工具进行日志采集，并且将采集到的日志提供给我们以便确认问题所在。

日志采集场景: SQL 执行出错
日志采集命令: obdiag gather scene run --scene=observer.sql_err --env “{db_connect=’-h[您的主机地址] -P[端口号] -u[用户名]@[租户名] -p[密码] -D[数据库名]’, trace_id=’[触发器执行时的trace_id]’}”

请将上述命令中的 [您的主机地址]、[端口号]、[用户名]、[租户名]、[密码]、[数据库名] 和 [触发器执行时的trace_id] 替换为您实际使用的值。执行完上述命令后，请将生成的日志压缩包发送给我们，以便我们进一步分析问题原因。

为了更好地帮助您解决问题，请您提供一些额外的信息：

您创建触发器的具体 SQL 语句是什么？
当前触发器是在 BEFORE 还是 AFTER 事件上触发？
报错的具体错误信息是什么？如果有错误码的话，请一并提供。
您是否尝试过在不涉及触发器的情况下直接访问基表数据，是否能够正常访问？

以上信息将有助于我们更快地定位问题所在。

详情请参考:

附上敏捷诊断工具 obdiag 使用帮助链接

旭辉 · 2024 年9 月 20 日 17:33

报什么错误呢

AntTech_ZIPM1O · 2024 年9 月 20 日 17:33

SQL 错误 [1442] [HY000]: Can’t update table ‘a’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

AntTech_ZIPM1O · 2024 年9 月 20 日 17:35

从mysql迁移的触发器，感觉目前的ob版本，触发器还不支持访问基表

AntTech_ZIPM1O · 2024 年9 月 20 日 17:38

试了一下想通过视图使触发器绕过基表访问，也不可行。
create view v_a as select * from a;

旭辉 · 2024 年9 月 20 日 17:48

select  hierarchy into parent_hierarchy from v_a where v_a.id=new.pid;

这里的v_a是a表的视图？

论坛小助手 · 2024 年9 月 20 日 17:51

根据您提供的信息，您在尝试创建一个触发器时遇到了错误 Can't update table 'a' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.。这个错误表明触发器试图访问正在被触发该触发器的语句修改的表。这种情况下，触发器不能同时读取或修改同一个表。

解决方案

避免直接访问基表：触发器不应该直接访问正在被修改的基表。您可以考虑将逻辑重构，使得触发器不直接访问基表。例如，您可以创建一个辅助表来存储中间状态，然后在触发器中操作这个辅助表。
使用视图：虽然您提到使用视图也不可行，但确保视图不直接引用正在被修改的表。如果视图仍然引用了基表，那么问题依然存在。
重构触发器逻辑：根据触发器的具体需求，您可以尝试将复杂的逻辑拆分成多个步骤。例如，先执行某些操作，然后再触发另一个独立的触发器来完成后续处理。

示例

假设您有一个表 a，并且希望在插入新行时执行某些操作。您可以创建一个辅助表 temp_a，并在触发器中操作这个辅助表：

CREATE TABLE temp_a (
    id INT NOT NULL AUTO_INCREMENT,
    pid INT,
    hierarchy VARCHAR(200),
    PRIMARY KEY (id)
);

DROP TRIGGER IF EXISTS `a_before_insert_trigger`;
DELIMITER |
CREATE TRIGGER `a_before_insert_trigger` BEFORE INSERT ON `a` FOR EACH ROW BEGIN
    DECLARE parent_hierarchy VARCHAR(300);
    SELECT hierarchy INTO parent_hierarchy FROM temp_a WHERE temp_a.id = NEW.pid;
    SET NEW.hierarchy = CONCAT(parent_hierarchy, ',');
END |
DELIMITER ;

在这个例子中，触发器通过辅助表 temp_a 来获取父节点的层次信息，而不是直接访问基表 a。

参考文档

触发器使用限制

请尝试上述方法，并观察是否解决了您的问题。如果仍有问题，请提供更多详细信息，以便进一步分析。

更多信息请查看:

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

AntTech_ZIPM1O · 2024 年9 月 20 日 18:05

是的，直接访问a表不行，我试了一下对a表建了一个视图

AntTech_ZIPM1O · 2024 年9 月 20 日 18:17

CREATE TABLE temp_a (
id INT NOT NULL AUTO_INCREMENT,
pid INT,
hierarchy VARCHAR(200),
PRIMARY KEY (id)
);

DROP TRIGGER IF EXISTS a_before_insert_trigger;
DELIMITER |
CREATE TRIGGER a_before_insert_trigger BEFORE INSERT ON a FOR EACH ROW BEGIN
DECLARE parent_hierarchy VARCHAR(300);
SELECT hierarchy INTO parent_hierarchy FROM temp_a WHERE temp_a.id = NEW.pid;
SET NEW.hierarchy = CONCAT(parent_hierarchy, ‘,’);
END |
DELIMITER ;

temp_a的数据如何维护，操作a表的时候触发更新temp_a吗？hierarchy无法入到temp_a里面吧

AntTech_ZIPM1O · 2024 年9 月 20 日 18:21

此种方案tmp_a表的数据维护是个问题？

旭辉 · 2024 年9 月 20 日 18:27

是一样的，这个场景目前OB是不支持的

AntTech_ZIPM1O · 2024 年9 月 20 日 18:36

结合论坛小助手，有个外招搞定了，
create table a(id int NOT NULL AUTO_INCREMENT,pid int,hierarchy varchar(200),primary key (id));
create table a_tmp(id int NOT NULL AUTO_INCREMENT,pid int,hierarchy varchar(200),primary key (id));

DROP TRIGGER IF EXISTS a_after_insert_trigger;
delimiter |
CREATE TRIGGER a_after_insert_trigger AFTER INSERT ON a FOR EACH ROW BEGIN
declare parent_hierarchy varchar(300);
INSERT INTO a_tmp(ID,pid,hierarchy)VALUES(new.id,new.pid,new.hierarchy);
END
|
delimiter ;

DROP TRIGGER IF EXISTS a_before_insert_trigger;
delimiter |
CREATE TRIGGER a_before_insert_trigger BEFORE INSERT ON a FOR EACH ROW BEGIN

declare parent_hierarchy varchar(300);
  
select  ifnull(hierarchy,',1,') into parent_hierarchy from a_tmp where a_tmp.id=new.pid;
if(new.id=1)then
  SET NEW.hierarchy = ',1,';
else
  SET NEW.hierarchy = CONCAT(parent_hierarchy, new.id,',');
 end if;

END
|
delimiter ;

TRUNCATE table a;
TRUNCATE table a_tmp;

insert into a(id,pid,hierarchy)values(-1,null,’,0,’);
insert into a(id,pid,hierarchy)values(1,0,’,0,’);
insert into a(id,pid,hierarchy)values(2,1,’,0,’);
insert into a(id,pid,hierarchy)values(3,1,’,0,’);
insert into a(id,pid,hierarchy)values(4,3,’,0,’);
insert into a(pid,hierarchy)values(2,’,0,’);
insert into a(pid,hierarchy)values(2,’,0,’);
insert into a(pid,hierarchy)values(3,’,0,’);
insert into a(pid,hierarchy)values(4,’,0,’);

AntTech_ZIPM1O · 2024 年9 月 20 日 18:51

还是无法实现全部业务逻辑，必须能操作基表，否则更新pid的时候，所有下级节点无法同时更新hierarchy

DROP TRIGGER IF EXISTS a_before_update_trigger;
delimiter |
CREATE TRIGGER a_before_update_trigger BEFORE UPDATE ON a FOR EACH ROW BEGIN

declare parent_hierarchy varchar(300);
  
select  ifnull(hierarchy,',1,') into parent_hierarchy from a_tmp where a_tmp.id=new.pid;
if(new.id=1)then
  SET NEW.hierarchy = ',1,';
else
  SET NEW.hierarchy = CONCAT(parent_hierarchy, new.id,',');
end if;

更新所有下级节点，此句还是无法执行

update a set hierarchy=replace(hierarchy,oldparent_hierarchy,parent_hierarchy) where hierarchy like concat(oldparent_hierarchy,’%’);

END
|
delimiter ;