Trigger Help

[thomasw_lrd]

Hello,

 

I'm a trying to create a trigger to divide two fields and update them.  It keeps failing with a

 

ERROR 1442: Can't update table 'transactions' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

 

I've read where you can do this with new.fieldname.  Which I have, any ideas?

 

Here is what I have

CREATE
DEFINER=`access`@`%`
TRIGGER `portal_sosha`.`testTrigger`
AFTER UPDATE ON `portal_sosha`.`transactions`
FOR EACH ROW
UPDATE transactions SET NEW.tr_transaction_pct_paid_to_company = tr_transaction_dol_paid_to_ngfg/tr_premium_or_basis_amount;

[mikosiko]

the error message is telling you exactly what the problem is:

 

ERROR 1442: Can't update table 'transactions' in stored function/trigger because it is already used by statement which invoked this stored function/trigger..

 

that is one of the known limitations of stored functions & triggers

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger."

 

http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html

[thomasw_lrd]

Thank you, I was hoping there was a work around.  Some articles I read said that I could use new.field_name, to do it, but I guess there is no way to do it? 

[mikosiko]

the answer depend on what exactly are you trying to do.... if you want to update a field based on the NEW values of other fields... then yes you can... but don't use the  UPDATE clause...

 

example to test:

 

CREATE
DEFINER=`access`@`%`
TRIGGER `portal_sosha`.`testTrigger`
BEFORE UPDATE ON `portal_sosha`.`transactions`   // to calculate the field BEFORE the update (prevent to execute the trigger more than 1 time)
FOR EACH ROW
    SET NEW.tr_transaction_pct_paid_to_company = NEW.tr_transaction_dol_paid_to_ngfg / NEW.tr_premium_or_basis_amount;

 

[thomasw_lrd]

Thank you very much.  That worked perfectly.  Now can you please explain it to me?  I really want to understand why it works like that versus, why mine didn't. 

 

I think it's because of the UPDATE clause? 

[mikosiko]

...

I think it's because of the UPDATE clause?

 

exactly... hence the error that you had in the beginning ... in a trigger (or stored function) you can't make reference to the table associated to the trigger/function

[thomasw_lrd]

Thank you again.