MERGE INTO and trigger firing

Database
Enthusiast

MERGE INTO and trigger firing

It is said that the placement of the INSERT before or after the UPDATE statement affects the order in which triggers fire. 

If MERGE WHEN MATCHED is specified first then

  1. before triggers for update fired
  2. then before triggers for insert are fired.

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/ch03...

 

It seems like both triggers fire for a MERGE WHEN MATCHED operation if so why do both triggers need to fire as for an update I would only expect a Update trigger to fire or am I missing something ?

 

Thanks

 

Norman

 

 

3 REPLIES
Enthusiast

Re: MERGE INTO and trigger firing

In the documentation before triggers are reference however when I try create a before trigger I am told before triggers are not supported. How does that work ?

Enthusiast

Re: MERGE INTO and trigger firing

What error message are you getting?

The documentation illustrates the ORDER of the triggers.  If you code the insert portion of the merge first, followed by the update, the insert before trigger(s) will fire THEN the update before trigger(s).

If you code the update portion of the merge first, followed by the insert, then the Update trigger(s) will fire before the Insert trigger(s).  The order of the before and/or after triggers is dependent upon the order of the merge functions.

Cheers

Enthusiast

Re: MERGE INTO and trigger firing

I have after some investigation found that infact the teradata documentation is incorrect when reffering to before triggers as before triggers essentially are not supported in Teradata. Also depending on which statement is first it only fires one of the triggers and the other will fire both triggers when ZI createa after update and after insert. (I physically tested this).

For me this is more about the understanding as I am hoping to write the Physical design and implementation exam soon :-)