主页 / dba / 问题 / 105293
Accepted
tachomi
Asked: 2015-06-27 13:15:27 +0800 CST

插入特定值后创建TRIGGER插入

  • 772

我有以下

表格1 id time sender receiver message status aux_id

表 #2 time_to_send sender receiver message aux_id

TABLE #2有一个将数据发送到返回布尔值的 api 的过程。

所以如果过程成功

INSERT INTO `TABLE #1` (time,sender,receiver,message,status,aux_id) 
VALUES (CURRENT_TIMESTAMP(),'sender','receiver','message','OK','1')

如果没有

INSERT INTO `TABLE #1` (time,sender,receiver,message,status,aux_id) 
VALUES (CURRENT_TIMESTAMP(),'sender','receiver','message','ERROR','1')

我想创建一个 TRIGGER 以将数据重新插入到TABLE #2 IF过程插入到status字段ERROR值中。

我正在尝试但没有成功

mysql> DELIMITER ||
mysql> USE db 
mysql> CREATE TRIGGER resend
-> ON TABLE #1
-> AFTER INSERT
-> AS
-> IF EXISTS (SELECT * FROM TABLE #1 WHERE status LIKE 'ERROR' AND aux_id =! 2)
-> BEGIN
-> INSERTO INTO TABLE #2 (time_to_send,sender,receiver,message,aux_id)
-> VALUES (DATE_ADD(NOW(),INTERVAL 10 MIN),NEW.sender,NEW.receiver,NEW.message,'2')
-> END;||

有任何想法吗？

mysql trigger

1 个回答

  1. Best Answer

    我认为您的触发器语法错误。尝试类似的东西（我不完全理解这个场景，所以你必须调整触发器中的逻辑）：

    delimiter @
create trigger resend 
after insert on t1 
for each row 
    IF NEW.status = 'ERROR' THEN     
        INSERT INTO t2 (time,sender,receiver,status,aux_id)     
        VALUES (NEW.time,NEW.sender,NEW.receiver, NEW.status, NEW.aux_id); 
    END IF @

delimiter ;
    • 1

相关问题