Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

After Update Trigger?

Status
Not open for further replies.

benderulz

IS-IT--Management
Nov 2, 2010
43
US
I'm trying to use an After Update trigger to compare two column in my table (custom_avail_history) and if they do not match, insert the record into another table (custom_avail_history_changelog) to track changes over time. When I try using the code below it tells me "ERROR 1054: 1054: Unknown column 'cah_avail' in 'field list'" 'cah_avail' is a column in the table being updated and i've check the spelling and there are no extra spaces or anything like that. I'm new to using triggers so my code may be way off. If someone could point me in the right direction, I would greatly appreciate it. Thanks


Code:
CREATE DEFINER=`user123`@`%` TRIGGER `import`.`custom_avail_history_AFTER_UPDATE` AFTER UPDATE ON `custom_avail_history` FOR EACH ROW
BEGIN
IF (cah_avail <> cah_prevail) then
INSERT INTO custom_avail_history_changelog (cahc_variety, cahc_avail, cahc_prevail) VALUES (cah_variety, cah_avail, 
cah_prevail);
End if;
END
 
Your trigger is an On Update Trigger, so your trigger has access to 2 possible values for cah_avail (and every other value for each of the columns), the OLD one and the NEW one (even if you didn't change it and they remain the same value). You have to preface the column name with either OLD.cah_avail or NEW.cah_avail, letting MySQL know which of the 2 you want.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top