0

I have the following

TABLE #1 id time sender receiver message status aux_id

TABLE #2 time_to_send sender receiver message aux_id

The TABLE #2 has a process that sends the data to an api which returns a Boolean.

So if the process success

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

If doesn't

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

I want to create a TRIGGER to REINSERT the data to TABLE #2 IF the process insert into the status field ERROR value.

I'm trying this without success

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;||

Any ideas?

1

I think you have the trigger syntax wrong. Try something like (I don't fully understand the scenario so you will have to adjust the logic in the trigger):

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 ;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.