0

I created two triggers in order to save one data in one table,

I am trying to save some messages in my Table 'ENR' when I have any change or any New data in same table 'ENR'.

I noticed that I cannot use trigger on 'ENR' to update some columns in 'ENR' So I created another Table named 'EmailMessaging' to do triggers to save data there and after another trigger on 'EmailMessaging' to update data in 'ENR' So I will avoid Update or add new data in same table in trigger statment.

My First Trigger for 'ENR' when I have New Data:

delimiter //

create trigger MessageMainEmail
after insert on ENR
for each row
begin
   DO SLEEP(1);
   insert into EmailMessaging(AssetNumber,MainMessage,Subject) values (New.Manufactor,concat('ENR has been changed! The Change occured in asset number: ',New.Manufactor,CHAR(13),'Thanks' ),concat(New.Manufactor,' Has been changed in ENR!'));
end //

delimiter ;

My Second Trigger for 'ENR' when I Update occur in ENR:

delimiter //

create trigger MessageMainEmailUpd
before update on ENR
for each row
begin
   DO SLEEP(1);
   insert into EmailMessaging(AssetNumber,MainMessage,Subject,date) values (New.Manufactor,concat('ENR has been changed! The Change occured in asset number: ',New.Manufactor,CHAR(13),'Thanks' ),concat(New.Manufactor,' Has been changed in ENR!'),Now());
end //

delimiter ;

And Third Trigger on 'EmailMessaging' to update data in ENR:

delimiter //

create trigger MessageMainEmailToENR
after insert on EmailMessaging
for each row
begin
   DO SLEEP(3);
   update ENR set MainMessage=New.MainMessage,Subject=New.Subject,datedate=New.Date where Manufactor=New.AssetNumber ;
end //

delimiter ;

I used Sleep to make sure triggers will not happen in same time.

Error:

Error updating database - Can't update table 'ENR' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I would be appreciate for any help for fixing this or new way.

  • Possible duplicate of [MySql Error: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger](https://stackoverflow.com/questions/15300673/mysql-error-cant-update-table-in-stored-function-trigger-because-it-is-already) – P.Salmon Aug 02 '18 at 07:14
  • Triggers are fully synchronous, so your `SLEEP` statements serve no purpose except to make your code slower. – Michael - sqlbot Aug 02 '18 at 11:33

0 Answers0