How to Distinguish Between Insert/Update in Triggers?

How to Distinguish Between Insert/Update in Triggers?

Post by Mark Pauke » Mon, 04 May 1998 04:00:00



Is there a commonly accepted way to determine whether an
insert/update trigger, was called as the result of an insert or an
update?  Currently I'm using:

IF EXISTS(SELECT * FROM DELETED)
--It's an update
ELSE
--It's an insert

Is this efficient?  Correct?

 
 
 

How to Distinguish Between Insert/Update in Triggers?

Post by Ian Posne » Tue, 05 May 1998 04:00:00


Write two triggers, one for the insert and one for the update - then you'll
know!

--

Ian Posner
MindQuest Solutions Ltd


Quote:>Is there a commonly accepted way to determine whether an
>insert/update trigger, was called as the result of an insert or an
>update?  Currently I'm using:

>IF EXISTS(SELECT * FROM DELETED)
>--It's an update
>ELSE
>--It's an insert

>Is this efficient?  Correct?


 
 
 

How to Distinguish Between Insert/Update in Triggers?

Post by Mark Pauke » Tue, 05 May 1998 04:00:00


Quote:> Write two triggers, one for the insert and one for the update -
then you'll
> know!

But then I'll have to duplicate some of the code in each trigger.
Maintaining duplicate code in multiple places is unacceptable because
of the high probability of bugs creeping in down the line as a
programmer changes one sp without realizing the need to make
identical changes elsewhere.  Code should be normalized just like
data.

-- Mark

 
 
 

How to Distinguish Between Insert/Update in Triggers?

Post by Richard Michael » Tue, 05 May 1998 04:00:00


Mark,I use the same method you are using and it seems to work fine.  I use
it for audit trail purposes.  I can tell which actions were taken at all
times.  I'm tracking Insert, Updates and Deletes...
The multiple triggers whould be great if they didn't have to perform the
same functions.

Rich


Quote:>> Write two triggers, one for the insert and one for the update -
>then you'll
>> know!

>But then I'll have to duplicate some of the code in each trigger.
>Maintaining duplicate code in multiple places is unacceptable because
>of the high probability of bugs creeping in down the line as a
>programmer changes one sp without realizing the need to make
>identical changes elsewhere.  Code should be normalized just like
>data.

>-- Mark

 
 
 

How to Distinguish Between Insert/Update in Triggers?

Post by Kalen Delane » Tue, 05 May 1998 04:00:00


Yours is the solution that I use.

--
Kalen Delaney
MCSE, SQL Server MCT, MVP


Quote:>Is there a commonly accepted way to determine whether an
>insert/update trigger, was called as the result of an insert or an
>update?  Currently I'm using:

>IF EXISTS(SELECT * FROM DELETED)
>--It's an update
>ELSE
>--It's an insert

>Is this efficient?  Correct?

 
 
 

How to Distinguish Between Insert/Update in Triggers?

Post by CRH » Tue, 05 May 1998 04:00:00


This is not quite accurate.  If an Update command changes
no rows, the update trigger still fires.  You can verify this by
temporarily putting a Print statement into any update/insert
trigger and doing UPDATE ... WHERE 1=0.


Quote:>Is there a commonly accepted way to determine whether an
>insert/update trigger, was called as the result of an insert or an
>update?  Currently I'm using:

>IF EXISTS(SELECT * FROM DELETED)
>--It's an update
>ELSE
>--It's an insert

>Is this efficient?  Correct?

 
 
 

How to Distinguish Between Insert/Update in Triggers?

Post by Mark Pauke » Wed, 06 May 1998 04:00:00


Quote:> If an Update command changes no rows, the update trigger still

fires.

That's a good thing to keep in mind.  I guess that checking for an
empty deleted table is not so much a way to verify that it's an
insert as it is a way to verify that it's not an update.  I don't
think that skipping over the update-related code in this case is

trigger code.  Is there anything else that I should be watching out
for in this respect?

-- Mark

 
 
 

How to Distinguish Between Insert/Update in Triggers?

Post by Kalen Delane » Wed, 06 May 1998 04:00:00



in every trigger and just do a return. Any other trigger code I refer to
always assumes I have already made this first check.

--
Kalen Delaney
MCSE, SQL Server MCT, MVP


>This is not quite accurate.  If an Update command changes
>no rows, the update trigger still fires.  You can verify this by
>temporarily putting a Print statement into any update/insert
>trigger and doing UPDATE ... WHERE 1=0.



>>Is there a commonly accepted way to determine whether an
>>insert/update trigger, was called as the result of an insert or an
>>update?  Currently I'm using:

>>IF EXISTS(SELECT * FROM DELETED)
>>--It's an update
>>ELSE
>>--It's an insert

>>Is this efficient?  Correct?

 
 
 

How to Distinguish Between Insert/Update in Triggers?

Post by Isaac Bla » Wed, 06 May 1998 04:00:00




>in every trigger and just do a return. Any other trigger code I refer to
>always assumes I have already made this first check.

>--
>Kalen Delaney
>MCSE, SQL Server MCT, MVP

There can be a situation when  "deleted" has more rows than "inserted"
for an UPDATE statement.  ( there should be a certain combination of
updated values and index options to make it possible) What will be the

        Just curious.  I would never set my indexes this way (or
update primary keys) - you may end up deleting rows instead of
updating.

Also, if the whole idea is to have a single source for all trigger
actions - what about DELETE operations?  One might have a decision
table like this:
--------------------------------
deleted  : inserted : statement
not empty: not empty: type
--------------------------------
   Y          Y         UPDATE
   Y          N         DELETE
   N          Y         INSERT
   N          N           ?????

Makes things a bit more complicated, especialyy if one uses triggers
for audit trial purposes.



>>>Is there a commonly accepted way to determine whether an
>>>insert/update trigger, was called as the result of an insert or an
>>>update?  Currently I'm using:

>>>IF EXISTS(SELECT * FROM DELETED)
>>>--It's an update
>>>ELSE
>>>--It's an insert

>>>Is this efficient?  Correct?

Have fun!!!

Isaac

 
 
 

How to Distinguish Between Insert/Update in Triggers?

Post by Mark Pauke » Fri, 08 May 1998 04:00:00


Quote:> Also, if the whole idea is to have a single source for all trigger
> actions - what about DELETE operations?

I didn't mean to imply that there should be a single source for all
actions; only that code shouldn't be duplicated in different
triggers.  There is often some duplication in functionality between
insert and update triggers, so in those cases I want to combine them
so the code is in one place.  If there is no shared code then I don't
combine them (pretty much all of my delete triggers stand alone).

-- Mark

 
 
 

How to Distinguish Between Insert/Update in Triggers?

Post by Isaac Bla » Sat, 09 May 1998 04:00:00



>> Also, if the whole idea is to have a single source for all trigger
>> actions - what about DELETE operations?

>I didn't mean to imply that there should be a single source for all
>actions; only that code shouldn't be duplicated in different
>triggers.  There is often some duplication in functionality between
>insert and update triggers, so in those cases I want to combine them
>so the code is in one place.  If there is no shared code then I don't
>combine them (pretty much all of my delete triggers stand alone).

It all depends on the application, but often INSERT and UPDATE
triggers share some functionality for the 'inserted' portion of the
data as well as DELETE and UPDATE triggers do for the 'deleted' data -
especially if you want to enforce foreign key constraints through
triggers.  

Quote:

>-- Mark

Isaac
 
 
 

How to Distinguish Between Insert/Update in Triggers?

Post by Torben Ko » Tue, 21 Jul 1998 04:00:00


Hi everybody,

Just felt like commenting on this.

The problem (at least one of them) with having two triggers - one
update, and one for insert - is that if you in the insert trigger
updates the table (to which the trigger belongs) the update trigger
will also be run. This gives a very unwanted performance hit - and it
may also give some unintended side effects.

Example: I have a default that sets a field called "recordversion" to
one (1) on insert. And I also have a trigger that does some
"house-keeping" and therefore updates the table. This causes the
update trigger (if any) to be run, and since the update trigger in
this example would increment the "recordversion" by 1 - an insert
would actually result in the "recordversion" being 2!!

This puzzled me quite a lot in the beginning, but then I found the
solution in this posting. This also gave me a performance win on
insert of about 60-75% !!

So be careful with your triggers! Although there  are not much
overhead on starting/running a trigger, you have to be careful not to
do very much inside them. And always put insert and update in the same
trigger if you make updates on the table!

*********

On Mon, 4 May 1998 02:38:09 +0100, "Ian Posner"


> Write two triggers, one for the insert and one for the update - then you'll
> know!

> --

> Ian Posner
> MindQuest Solutions Ltd



> >Is there a commonly accepted way to determine whether an
> >insert/update trigger, was called as the result of an insert or an
> >update?  Currently I'm using:

> >IF EXISTS(SELECT * FROM DELETED)
> >--It's an update
> >ELSE
> >--It's an insert

> >Is this efficient?  Correct?

______________________________________________________________

Torben Koch                     Tel.    +45 4527 0533 (Direct)  

Olicom A/S                      Tel.    +45 4527 0000 (Reception)
Nybrovej 114                    Fax.    +45 4527 0101            
DK-2800 Lyngby                                                

______________________________________________________________