Mysql触发器将行从表移动到相同的其他表

Question

我需要制作触发器，当检查的行数据值为 1 时，将行从表传输到另一个具有相同名称行的表，以便检查是否已移动。当插入或更改某些内容时应该会发生这种情况。有任何想法吗？

Answer 1

MySQL 错误 1442:

Error Code: 1442. Can't update table 'xyz' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

由于服务器知道它位于同一调用堆栈中，因此会在级联或递归(如触发器调用)的情况下发生。

您能做的最好的事情就是利用事件清理策略，您可以选择自己选择的频率。如果您使用它，请记住它利用全局事件调度程序，并且您不会忘记它正在运行。此外，在大多数共享托管环境中，由于共享资源，事件不受支持(人们倾向于滥用它们，这会影响每个人)。

也就是说，

架构:

create table orig101
(   id int auto_increment primary key,
    thing1 varchar(100) not null,
    checked int not null
);

create table copy101
(   id int primary key,
    thing1 varchar(100) not null,
    checked int not null
);

事件:

DROP EVENT IF EXISTS cleanUpCopied;
DELIMITER $$
CREATE EVENT cleanUpCopied
  ON SCHEDULE EVERY 2 MINUTE STARTS '2016-01-01 00:00:00'
  ON COMPLETION PRESERVE
DO
  BEGIN
    DELETE FROM orig101 WHERE checked=1;
  END $$
DELIMITER ;
-- -------------------------------------------------
select @@event_scheduler; -- mine is currently OFF
SET GLOBAL event_scheduler = ON;  -- turn her on and confirm below
select @@event_scheduler; -- yep it is ON

触发器:

DROP TRIGGER IF EXISTS aft_ins_orig;
delimiter $$
CREATE TRIGGER aft_ins_orig AFTER INSERT ON orig101
FOR EACH ROW 
BEGIN
    IF NEW.checked=1 THEN
        INSERT IGNORE copy101 (id,thing1,checked) 
        SELECT NEW.id,NEW.thing1,NEW.checked;

    END IF;
END;$$
delimiter ;
-- -------------------------------------------------
DROP TRIGGER IF EXISTS aft_upd_orig;
delimiter $$
CREATE TRIGGER aft_upd_orig AFTER UPDATE ON orig101
FOR EACH ROW
BEGIN
    IF NEW.checked=1 THEN
        INSERT IGNORE copy101 (id,thing1,checked) 
        SELECT NEW.id,NEW.thing1,NEW.checked;
    END IF;
END;$$
delimiter ;

测试:

truncate orig101;
truncate copy101;

INSERT orig101 (thing1,checked) values ('frog',0);
select * from orig101; -- 1 row
select * from copy101; -- 0 rows

INSERT orig101 (thing1,checked) values ('fish',1); -- when evt fires, cleanup occurs
select * from orig101; -- until evt fires, checked=1 row(s) still there
select * from copy101;

INSERT orig101 (thing1,checked) values ('fish2',1); -- when evt fires, cleanup occurs
INSERT orig101 (thing1,checked) values ('fish3',1); -- when evt fires, cleanup occurs
INSERT orig101 (thing1,checked) values ('fish4',1); -- when evt fires, cleanup occurs
INSERT orig101 (thing1,checked) values ('fish5',1); -- when evt fires, cleanup occurs

select * from orig101; -- until evt fires, checked=1 row(s) still there
select * from copy101;

UPDATE orig101 set checked=1 where id=1;

-- Tweak the frequency of event firing (say every minute, or every hour, whatever)

我的个人资料页面上有大约 3 个事件链接。祝你好运。