UPDATE Same Row After UPDATE in Trigger

Question

I want the `epc` column to always be `earnings`/`clicks`. I am using an AFTER UPDATE trigger to accomplish this. So if I were to add 100 clicks to this table, I would want the EPC to update automatically. I am trying 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 tried using OLD as well but also got an error. I could do BEFORE but then if I added 100 clicks it would use the previous # clicks for the trigger (right?) What should I do to accomplish this? EDIT - An example of a query that would be run on this: ``` UPDATE records SET clicks=clicks+100 //EPC should update automatically ```

Accepted Answer

You can't update rows in the table in an after update trigger.

Perhaps 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 a trigger, you have have access to `OLD` and `NEW`. `OLD` are the old values in the record and `NEW` are the new values. In a before trigger, the `NEW` values are what get written to the table, so you can modify them. In an after trigger, the `NEW` values have already been written, so they cannot be modified. I think the MySQL documentation explains this pretty well.

UPDATE Same Row After UPDATE in Trigger

Solution

Problem