UPDATE Same Row after UPDATE in trigger

Question

I want the epc column epc always be earnings / clicks . I am using the AFTER UPDATE trigger to accomplish this. Therefore, if I added 100 clicks to this table, I would like the EPC to automatically update.

I am trying to do this:

 CREATE TRIGGER `records_integrity` AFTER UPDATE ON `records` FOR EACH ROW SET NEW.epc=IFNULL(earnings/clicks,0); 

And getting this error:

 MySQL said: #1362 - Updating of NEW row is not allowed in after trigger 

I also tried to use OLD, but also got an error. I could do BEFORE, but if I added 100 clicks, it would use the previous # clicks for the trigger (right?)

What should I do to do this?

EDIT is an example of a query that will run on this:

 UPDATE records SET clicks=clicks+100 //EPC should update automatically 

Answer 1

You cannot update rows in a table after an update trigger.

Maybe you want something like this:

 CREATE TRIGGER `records_integrity` BEFORE UPDATE ON `records` FOR EACH ROW SET NEW.epc=IFNULL(new.earnings/new.clicks, 0); 

EDIT:

Inside the trigger, you have access to OLD and NEW . OLD are the old values ​​in the record, and NEW are the new values. In the previous trigger, the NEW values ​​are what is written to the table, so you can change them. After the trigger, NEW values ​​are already written, so they cannot be changed. I think the MySQL documentation explains this pretty well.

Answer 2

Perhaps you could write two separate statements in this transaction

  update record set clicks=... update record set epc=... 

or you can put them inside a function, say updateClick () and just call that function. By doing this in this way, you can easily change your logic if necessary.

Enabling logic inside a trigger can create a situation where debugging and tracing become unnecessarily complex.