MySQL Forums
Forum List  »  Triggers

update trigger only on certain changed values
Posted by: mysql
Date: May 12, 2006 11:20AM

I have 2 tables, ads, and ad_status_history. When certain aspects of the ad is changed I want to store what was changed and when. I am trying to do this with the following trigger:

create trigger ad_update BEFORE UPDATE on ads
FOR EACH ROW BEGIN
set @stat= NULL;
IF ( NEW.reserved <> Old.reserved AND NEW.reserved is not null ) THEN
set @stat = 7;
ELSEIF (NEW.created <> Old.created AND NEW.created is not null) THEN
set @stat = 6;
ELSEIF (NEW.approved <> Old.approved AND NEW.approved is not null) THEN
set @stat = 11;
END IF;
IF @stat is not NULL THEN
insert into ad_stat_history set ad_id = NEW.ad_id, stat_id=@stat, stat_date=curdate() on duplicate key update stat_date=curdate();
END IF;
END;
|

the values for stat are diffent id's for the status change.
when I do an update on the ads table now, and change one of those values, it does not insert a record in the ad_stat_history table.

I tried making it a BEFORE UPDATE and AFTER UPDATE trigger, and that didnt change it.

I tried making the if clause only check that the values were not null (ie. IF NEW.reserved is not null THEN) but then it would ad a row to the history eeven when I ran update ads set reserved = NULL where ad_id = ID.

Any insight?

Options: ReplyQuote


Subject
Views
Written By
Posted
update trigger only on certain changed values
3788
May 12, 2006 11:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.