List:General Discussion« Previous MessageNext Message »
From:(Hal Date:December 18 2011 3:46am
Subject:Re: UPDATE triggers with REPLACE statements
View as plain text  
>>>> 2011/12/16 16:00 -0800, Jim McNeely >>>>
I have a set of tables that get populated and changed a lot from lots of REPLACE
statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets
triggered because REPLACES are all deletes and inserts.

The trigger is going to populate another table as a queue for a system to do something
whenever a particular field changes.

SO, does anyone have some slick idea how to handle this little problem? I have an idea but
I have a feeling there is something better out there.
<<<<<<<<
Exactly what do you mean? You want the DELETE-trigger to talk to the INSERT-trigger for to
learn whether the field changed? Try user-defined variable.

I do not like that, but the only other that comes to mind is no better, a tiny table that
exists only for the DELETE-trigger to save the field value, and the INSERT-trigger to
look at it.

Here is a trigger that inserts into a table PaimentLog after there is inserting into a
table GiftAndShare:

CREATE TRIGGER newGiftAft AFTER INSERT ON GiftAndShare FOR EACH ROW BEGIN
	...
	INSERT INTO PaimentLog
	SELECT NEW.MemberID, Surname, GivenName, NEW.Why, NEW.Amount, NEW.Date, NEW.Method,
NEW.byWhom
	FROM Nam WHERE MemberID = NEW.MemberID AND Rank = 0;
END

Thread
UPDATE triggers with REPLACE statementsJim McNeely17 Dec
  • Re: UPDATE triggers with REPLACE statementshsv18 Dec
  • Re: UPDATE triggers with REPLACE statementsClaudio Nanni18 Dec
    • Re: UPDATE triggers with REPLACE statementsJim McNeely19 Dec
    • Re: UPDATE triggers with REPLACE statementsJim McNeely19 Dec
      • Re: UPDATE triggers with REPLACE statementsClaudio Nanni19 Dec
        • Re: UPDATE triggers with REPLACE statementsJim McNeely19 Dec
          • Re: UPDATE triggers with REPLACE statementshsv20 Dec
      • Re: UPDATE triggers with REPLACE statementshsv19 Dec
        • Re: UPDATE triggers with REPLACE statementsJim McNeely19 Dec
          • Re: UPDATE triggers with REPLACE statementshsv23 Jan