Detecting insert, update and delete in an after trigger

Do you have a simple example with comments explaining whats going on?


"Eric Isaacs" wrote in message

> In an after insert/update/delete trigger, all inserts and updates will
> be in the Inserted table with the (new) primary key. All the deletes
> (and previous values) will be in the Deleted table. If you have
> primary keys that don't change (INT IDENTITY or GUID that your
> application leaves static all the time) you can generally count on
> those PKs to be static for all these operations, so you can determine
> inserts where the ID is in the Inserted, but not in the Deleted. You
> can determine deletes where the ID is in the Delete table, but not in
> the Inserted table, and you can determine updates where the ID is in
> the Inserted AND Deleted tables.
>
> If your primary keys can change (because you're using composit PKs or
> customer data as your PK), you really can only determine these as
> inserts and deletes, as you won't be able to tell when an existing row
> has changed if you can't identify it in both the Inserted and Deleted
> tables. On the other hand, if there is data in both the Inserted and
> Deleted tables for one single execution of the trigger, I suppose you
> could make the assumption that all transactions are updates, whereas
> if there is only data in Inserted and no data in Deleted, you could
> assume they're all inserts, etc. That would probably work too.
>
> -Eric Isaacs
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

Andy
If exists (select * from inserted) and exists (select * from deleted)
'Update happened'
if exists( select * from inserted)
'Insert happened'
else
'Delete happened'




"Andy B" wrote in message

> Do you have a simple example with comments explaining whats going on?
>
>
> "Eric Isaacs" wrote in message
>
>> In an after insert/update/delete trigger, all inserts and updates will
>> be in the Inserted table with the (new) primary key. All the deletes
>> (and previous values) will be in the Deleted table. If you have
>> primary keys that don't change (INT IDENTITY or GUID that your
>> application leaves static all the time) you can generally count on
>> those PKs to be static for all these operations, so you can determine
>> inserts where the ID is in the Inserted, but not in the Deleted. You
>> can determine deletes where the ID is in the Delete table, but not in
>> the Inserted table, and you can determine updates where the ID is in
>> the Inserted AND Deleted tables.
>>
>> If your primary keys can change (because you're using composit PKs or
>> customer data as your PK), you really can only determine these as
>> inserts and deletes, as you won't be able to tell when an existing row
>> has changed if you can't identify it in both the Inserted and Deleted
>> tables. On the other hand, if there is data in both the Inserted and
>> Deleted tables for one single execution of the trigger, I suppose you
>> could make the assumption that all transactions are updates, whereas
>> if there is only data in Inserted and no data in Deleted, you could
>> assume they're all inserts, etc. That would probably work too.
>>
>> -Eric Isaacs
>
>
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

Got it. Now, is there a way to make this database wide instead of having to
create a trigger for each table that needs it?


"Uri Dimant" wrote in message

> Andy
> If exists (select * from inserted) and exists (select * from deleted)
> 'Update happened'
> if exists( select * from inserted)
> 'Insert happened'
> else
> 'Delete happened'
>
>
>
>
> "Andy B" wrote in message
>
>> Do you have a simple example with comments explaining whats going on?
>>
>>
>> "Eric Isaacs" wrote in message
>>
>>> In an after insert/update/delete trigger, all inserts and updates will
>>> be in the Inserted table with the (new) primary key. All the deletes
>>> (and previous values) will be in the Deleted table. If you have
>>> primary keys that don't change (INT IDENTITY or GUID that your
>>> application leaves static all the time) you can generally count on
>>> those PKs to be static for all these operations, so you can determine
>>> inserts where the ID is in the Inserted, but not in the Deleted. You
>>> can determine deletes where the ID is in the Delete table, but not in
>>> the Inserted table, and you can determine updates where the ID is in
>>> the Inserted AND Deleted tables.
>>>
>>> If your primary keys can change (because you're using composit PKs or
>>> customer data as your PK), you really can only determine these as
>>> inserts and deletes, as you won't be able to tell when an existing row
>>> has changed if you can't identify it in both the Inserted and Deleted
>>> tables. On the other hand, if there is data in both the Inserted and
>>> Deleted tables for one single execution of the trigger, I suppose you
>>> could make the assumption that all transactions are updates, whereas
>>> if there is only data in Inserted and no data in Deleted, you could
>>> assume they're all inserts, etc. That would probably work too.
>>>
>>> -Eric Isaacs
>>
>>
>
>
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

Andy
I'm not sure that understood you. You can create three triggers each for DML
opeartion, or much better not using triggers at all, i mean keep all logic
within a stored procedure.



"Andy B" wrote in message

> Got it. Now, is there a way to make this database wide instead of having
> to create a trigger for each table that needs it?
>
>
> "Uri Dimant" wrote in message
>
>> Andy
>> If exists (select * from inserted) and exists (select * from deleted)
>> 'Update happened'
>> if exists( select * from inserted)
>> 'Insert happened'
>> else
>> 'Delete happened'
>>
>>
>>
>>
>> "Andy B" wrote in message
>>
>>> Do you have a simple example with comments explaining whats going on?
>>>
>>>
>>> "Eric Isaacs" wrote in message
>>>
>>>> In an after insert/update/delete trigger, all inserts and updates will
>>>> be in the Inserted table with the (new) primary key. All the deletes
>>>> (and previous values) will be in the Deleted table. If you have
>>>> primary keys that don't change (INT IDENTITY or GUID that your
>>>> application leaves static all the time) you can generally count on
>>>> those PKs to be static for all these operations, so you can determine
>>>> inserts where the ID is in the Inserted, but not in the Deleted. You
>>>> can determine deletes where the ID is in the Delete table, but not in
>>>> the Inserted table, and you can determine updates where the ID is in
>>>> the Inserted AND Deleted tables.
>>>>
>>>> If your primary keys can change (because you're using composit PKs or
>>>> customer data as your PK), you really can only determine these as
>>>> inserts and deletes, as you won't be able to tell when an existing row
>>>> has changed if you can't identify it in both the Inserted and Deleted
>>>> tables. On the other hand, if there is data in both the Inserted and
>>>> Deleted tables for one single execution of the trigger, I suppose you
>>>> could make the assumption that all transactions are updates, whereas
>>>> if there is only data in Inserted and no data in Deleted, you could
>>>> assume they're all inserts, etc. That would probably work too.
>>>>
>>>> -Eric Isaacs
>>>
>>>
>>
>>
>
>
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

Uri, I think there may have been a logic flaw in your code. Since you don't
have BEGIN/END and an ELSE after the first IF, the second IF will hit for
UPDATES and print 'Insert happened', right?

maybe this (untested)??

If exists (select * from inserted) and exists (select * from deleted)
BEGIN
'Update happened'
END
ELSE
BEGIN
if exists( select * from inserted)
BEGIN
'Insert happened'
END
ELSE
BEGIN
'Delete happened'
END
END


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Uri Dimant" wrote in message

> Andy
> If exists (select * from inserted) and exists (select * from deleted)
> 'Update happened'
> if exists( select * from inserted)
> 'Insert happened'
> else
> 'Delete happened'
>
>
>
>
> "Andy B" wrote in message
>
>> Do you have a simple example with comments explaining whats going on?
>>
>>
>> "Eric Isaacs" wrote in message
>>
>>> In an after insert/update/delete trigger, all inserts and updates will
>>> be in the Inserted table with the (new) primary key. All the deletes
>>> (and previous values) will be in the Deleted table. If you have
>>> primary keys that don't change (INT IDENTITY or GUID that your
>>> application leaves static all the time) you can generally count on
>>> those PKs to be static for all these operations, so you can determine
>>> inserts where the ID is in the Inserted, but not in the Deleted. You
>>> can determine deletes where the ID is in the Delete table, but not in
>>> the Inserted table, and you can determine updates where the ID is in
>>> the Inserted AND Deleted tables.
>>>
>>> If your primary keys can change (because you're using composit PKs or
>>> customer data as your PK), you really can only determine these as
>>> inserts and deletes, as you won't be able to tell when an existing row
>>> has changed if you can't identify it in both the Inserted and Deleted
>>> tables. On the other hand, if there is data in both the Inserted and
>>> Deleted tables for one single execution of the trigger, I suppose you
>>> could make the assumption that all transactions are updates, whereas
>>> if there is only data in Inserted and no data in Deleted, you could
>>> assume they're all inserts, etc. That would probably work too.
>>>
>>> -Eric Isaacs
>>
>>
>
>
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

Kevin
Could be , I have not tested too , but only tried to direct the OP to the
right point




"TheSQLGuru" wrote in message

> Uri, I think there may have been a logic flaw in your code. Since you
> don't have BEGIN/END and an ELSE after the first IF, the second IF will
> hit for UPDATES and print 'Insert happened', right?
>
> maybe this (untested)??
>
> If exists (select * from inserted) and exists (select * from deleted)
> BEGIN
> 'Update happened'
> END
> ELSE
> BEGIN
> if exists( select * from inserted)
> BEGIN
> 'Insert happened'
> END
> ELSE
> BEGIN
> 'Delete happened'
> END
> END
>
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Uri Dimant" wrote in message
>
>> Andy
>> If exists (select * from inserted) and exists (select * from deleted)
>> 'Update happened'
>> if exists( select * from inserted)
>> 'Insert happened'
>> else
>> 'Delete happened'
>>
>>
>>
>>
>> "Andy B" wrote in message
>>
>>> Do you have a simple example with comments explaining whats going on?
>>>
>>>
>>> "Eric Isaacs" wrote in message
>>>
>>>> In an after insert/update/delete trigger, all inserts and updates will
>>>> be in the Inserted table with the (new) primary key. All the deletes
>>>> (and previous values) will be in the Deleted table. If you have
>>>> primary keys that don't change (INT IDENTITY or GUID that your
>>>> application leaves static all the time) you can generally count on
>>>> those PKs to be static for all these operations, so you can determine
>>>> inserts where the ID is in the Inserted, but not in the Deleted. You
>>>> can determine deletes where the ID is in the Delete table, but not in
>>>> the Inserted table, and you can determine updates where the ID is in
>>>> the Inserted AND Deleted tables.
>>>>
>>>> If your primary keys can change (because you're using composit PKs or
>>>> customer data as your PK), you really can only determine these as
>>>> inserts and deletes, as you won't be able to tell when an existing row
>>>> has changed if you can't identify it in both the Inserted and Deleted
>>>> tables. On the other hand, if there is data in both the Inserted and
>>>> Deleted tables for one single execution of the trigger, I suppose you
>>>> could make the assumption that all transactions are updates, whereas
>>>> if there is only data in Inserted and no data in Deleted, you could
>>>> assume they're all inserts, etc. That would probably work too.
>>>>
>>>> -Eric Isaacs
>>>
>>>
>>
>>
>
>
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

> Could be , I have not tested too , but only tried to direct the OP to the
> right point

Or should be I wanted point him to the right direction )))



"Uri Dimant" wrote in message

> Kevin
> Could be , I have not tested too , but only tried to direct the OP to the
> right point
>
>
>
>
> "TheSQLGuru" wrote in message
>
>> Uri, I think there may have been a logic flaw in your code. Since you
>> don't have BEGIN/END and an ELSE after the first IF, the second IF will
>> hit for UPDATES and print 'Insert happened', right?
>>
>> maybe this (untested)??
>>
>> If exists (select * from inserted) and exists (select * from deleted)
>> BEGIN
>> 'Update happened'
>> END
>> ELSE
>> BEGIN
>> if exists( select * from inserted)
>> BEGIN
>> 'Insert happened'
>> END
>> ELSE
>> BEGIN
>> 'Delete happened'
>> END
>> END
>>
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>>
>> "Uri Dimant" wrote in message
>>
>>> Andy
>>> If exists (select * from inserted) and exists (select * from deleted)
>>> 'Update happened'
>>> if exists( select * from inserted)
>>> 'Insert happened'
>>> else
>>> 'Delete happened'
>>>
>>>
>>>
>>>
>>> "Andy B" wrote in message
>>>
>>>> Do you have a simple example with comments explaining whats going on?
>>>>
>>>>
>>>> "Eric Isaacs" wrote in message
>>>>
>>>>> In an after insert/update/delete trigger, all inserts and updates will
>>>>> be in the Inserted table with the (new) primary key. All the deletes
>>>>> (and previous values) will be in the Deleted table. If you have
>>>>> primary keys that don't change (INT IDENTITY or GUID that your
>>>>> application leaves static all the time) you can generally count on
>>>>> those PKs to be static for all these operations, so you can determine
>>>>> inserts where the ID is in the Inserted, but not in the Deleted. You
>>>>> can determine deletes where the ID is in the Delete table, but not in
>>>>> the Inserted table, and you can determine updates where the ID is in
>>>>> the Inserted AND Deleted tables.
>>>>>
>>>>> If your primary keys can change (because you're using composit PKs or
>>>>> customer data as your PK), you really can only determine these as
>>>>> inserts and deletes, as you won't be able to tell when an existing row
>>>>> has changed if you can't identify it in both the Inserted and Deleted
>>>>> tables. On the other hand, if there is data in both the Inserted and
>>>>> Deleted tables for one single execution of the trigger, I suppose you
>>>>> could make the assumption that all transactions are updates, whereas
>>>>> if there is only data in Inserted and no data in Deleted, you could
>>>>> assume they're all inserts, etc. That would probably work too.
>>>>>
>>>>> -Eric Isaacs
>>>>
>>>>
>>>
>>>
>>
>>
>
>
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

On Mon, 10 Nov 2008 19:43:20 -0500, Andy B wrote:

>Is there a way to detect insert, delete and update actions in an after
>trigger?

Hi Andy,

The easiest way to detect this is to code seperate triggers for each of
the three actions. That way, you always know for sure what operation
fired the trigger!

(The method suggested by Eric and Uri will fail if the trigger fires on
an insert, update, or delete that affects no rows at all - though in
many triggers that won't really matter much...)

>Now, is there a way to make this database wide instead of having to
>create a trigger for each table that needs it?

There is no such way, and frankly, I see very little situations wherre
this would be practical. When a trigger is fired, don't you want it to
do something with the actual changes that caused it to fire? So that
would mean handling different columns in different triggers - far easier
and more efficient to have a trigger per table than to have some generic
code that would require hefty dynamic SQL generation and execution to do
something sensible.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

Hi Hugo
> (The method suggested by Eric and Uri will fail if the trigger fires on
> an insert, update, or delete that affects no rows at all - though in
> many triggers that won't really matter much...)

What did you mean?
create table t ( c int)

insert into t select 1

create trigger tt on t for update

as

select * from

(select * from inserted

union all

select * from deleted) as d



update t set c=1







"Hugo Kornelis" wrote in message

> On Mon, 10 Nov 2008 19:43:20 -0500, Andy B wrote:
>
>>Is there a way to detect insert, delete and update actions in an after
>>trigger?
>
> Hi Andy,
>
> The easiest way to detect this is to code seperate triggers for each of
> the three actions. That way, you always know for sure what operation
> fired the trigger!
>
> (The method suggested by Eric and Uri will fail if the trigger fires on
> an insert, update, or delete that affects no rows at all - though in
> many triggers that won't really matter much...)
>
>>Now, is there a way to make this database wide instead of having to
>>create a trigger for each table that needs it?
>
> There is no such way, and frankly, I see very little situations wherre
> this would be practical. When a trigger is fired, don't you want it to
> do something with the actual changes that caused it to fire? So that
> would mean handling different columns in different triggers - far easier
> and more efficient to have a trigger per table than to have some generic
> code that would require hefty dynamic SQL generation and execution to do
> something sensible.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

On Wed, 12 Nov 2008 12:11:15 +0200, Uri Dimant wrote:

>Hi Hugo
>> (The method suggested by Eric and Uri will fail if the trigger fires on
>> an insert, update, or delete that affects no rows at all - though in
>> many triggers that won't really matter much...)
>
>What did you mean?

Hi Uri,

What I meant is that any DML action that affects no rows (e.g. UPDATE
.... WHERE 1 = 2) will cause the trigger to fire with no rows in inserted
and no rows in deleted.

CREATE TABLE Test (a int PRIMARY KEY, b int);
go
CREATE TRIGGER TestTrigger
ON Test AFTER INSERT, UPDATE, DELETE
AS
IF EXISTS (SELECT * FROM inserted)
AND EXISTS (SELECT * FROM deleted)
PRINT 'Update happened';
ELSE
IF EXISTS (SELECT * FROM inserted)
PRINT 'Insert happened';
ELSE
PRINT 'Delete happened';
go
UPDATE Test SET b = 2;
go
DROP TABLE Test;
go

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

Makes sense...


"Hugo Kornelis" wrote in message

> On Mon, 10 Nov 2008 19:43:20 -0500, Andy B wrote:
>
>>Is there a way to detect insert, delete and update actions in an after
>>trigger?
>
> Hi Andy,
>
> The easiest way to detect this is to code seperate triggers for each of
> the three actions. That way, you always know for sure what operation
> fired the trigger!
>
> (The method suggested by Eric and Uri will fail if the trigger fires on
> an insert, update, or delete that affects no rows at all - though in
> many triggers that won't really matter much...)
>
>>Now, is there a way to make this database wide instead of having to
>>create a trigger for each table that needs it?
>
> There is no such way, and frankly, I see very little situations wherre
> this would be practical. When a trigger is fired, don't you want it to
> do something with the actual changes that caused it to fire? So that
> would mean handling different columns in different triggers - far easier
> and more efficient to have a trigger per table than to have some generic
> code that would require hefty dynamic SQL generation and execution to do
> something sensible.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

Hi Hugo

Ok, it is most likely IF UPDATE(col) will fire even col was really updated
)



"Hugo Kornelis" wrote in message

> On Wed, 12 Nov 2008 12:11:15 +0200, Uri Dimant wrote:
>
>>Hi Hugo
>>> (The method suggested by Eric and Uri will fail if the trigger fires on
>>> an insert, update, or delete that affects no rows at all - though in
>>> many triggers that won't really matter much...)
>>
>>What did you mean?
>
> Hi Uri,
>
> What I meant is that any DML action that affects no rows (e.g. UPDATE
> ... WHERE 1 = 2) will cause the trigger to fire with no rows in inserted
> and no rows in deleted.
>
> CREATE TABLE Test (a int PRIMARY KEY, b int);
> go
> CREATE TRIGGER TestTrigger
> ON Test AFTER INSERT, UPDATE, DELETE
> AS
> IF EXISTS (SELECT * FROM inserted)
> AND EXISTS (SELECT * FROM deleted)
> PRINT 'Update happened';
> ELSE
> IF EXISTS (SELECT * FROM inserted)
> PRINT 'Insert happened';
> ELSE
> PRINT 'Delete happened';
> go
> UPDATE Test SET b = 2;
> go
> DROP TABLE Test;
> go
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: Detecting insert, update and delete in an after trigger 

My suggestion was that you check counts in Inserted and Deleted. If
no rows are actually updated, then there won't be any data in those
tables, even if the trigger fires. I think you make a valid point,
however, that Uri's IF block would return that rows were deleted even
if NO rows were deleted or even updated. You would also need to check
the Deleted table.

The one other case to consider is if the rows are updated to their
existing values (the data is updated, but no changes are actually
made.)

USE tempdb

CREATE TABLE Test (a int PRIMARY KEY, b int);
go

INSERT Test (a, b) SELECT 1, 2

GO

CREATE TRIGGER TestTrigger
ON Test AFTER INSERT, UPDATE, DELETE
AS
IF EXISTS (SELECT * FROM inserted)
AND EXISTS (SELECT * FROM deleted)
PRINT 'Update happened';
ELSE
IF EXISTS (SELECT * FROM inserted)
PRINT 'Insert happened';
ELSE IF EXISTS (SELECT * FROM Deleted) --Uri's code also needs this
additional check in the case that nothing is updated.
PRINT 'Delete happened';
go
UPDATE Test SET b = 2;
go
DROP TABLE Test;
go

results in:


(1 row(s) affected)
Update happened

(1 row(s) affected)


....In this case, technically an update happened, but physically
nothing was updated. (The value 2 was updated to 2.) If you need to
know that level of detail, you'll need to have static primary keys or
other static unique values (that you don't update) and you'll need to
compare the inserted and deleted values in each column.

-Eric Isaacs
 >> Stay informed about: Detecting insert, update and delete in an after trigger