Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

mysql - UPDATE Same Row After UPDATE in Trigger

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...