List:General Discussion« Previous MessageNext Message »
From:(Hal Date:January 23 2012 3:44pm
Subject:Re: UPDATE triggers with REPLACE statements
View as plain text  
;>>> 20111219 03:42 PM -0800, Jim McNeely >>>>
Not if you are using innoDB tables. For these, you use INSERT and UPDATE triggers. 

Jim McNeely
On Dec 19, 2011, at 11:58 AM, Hal
>>>>> 2011/12/19 11:30 -0800, Jim McNeely >>>>
> In the MySQL documentation, we find this tantalizing statement:
> 
> "It is possible that in the case of a duplicate-key error, a storage engine may
> perform the REPLACE as an update rather than a delete plus insert, but the semantics are
> the same. There are no user-visible effects other than a possible difference in how the
> storage engine increments Handler_xxx status variables." 
> <<<<<<<<
> Well, try it--but beware of these statements: "the semantics are the same. There are
> no user-visible effects other than a possible difference in how the storage engine
> increments Handler_xxx status variables."
> 
> If accurate, the triggers are those of DELETE & INSERT, not UPDATE.
<<<<<<<<
In my experimenting I find (version 5.5.8) that DELETE & INSERT are triggered, not
UPDATE. The statement "There are no user-visible effects..." is simply wrong. The
SQL-programmer has to be ready for either outcome.

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