Detecting insert, update and delete in an after trigger

Here In Update code is working But It will insert Inserted and Deleted row is also Added to next Table I want only Updated Data.



TheSQLGuru wrote:

Uri, I think there may have been a logic flaw in your code.
11-Nov-08

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


Previous Posts In This Thread:

On Monday, November 10, 2008 7:43 PM
Andy B wrote:

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

On Monday, November 10, 2008 8:55 PM
Andy B wrote:

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

On Tuesday, November 11, 2008 1:52 AM
Uri Dimant wrote:

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

On Tuesday, November 11, 2008 3:38 AM
Andy B wrote:

Got it.
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?

On Tuesday, November 11, 2008 5:08 AM
Uri Dimant wrote:

AndyI'm not sure that understood you.
Andy
I am 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.

On Tuesday, November 11, 2008 12:49 PM
TheSQLGuru wrote:

Uri, I think there may have been a logic flaw in your code.
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


On Wednesday, November 12, 2008 1:55 AM
Uri Dimant wrote:

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

On Wednesday, November 12, 2008 3:01 AM
Uri Dimant wrote:

Re: Detecting insert, update and delete in an after trigger
Or should be I wanted point him to the right direction )))

On Wednesday, November 12, 2008 4:50 AM
Hugo Kornelis wrote:

Re: Detecting insert, update and delete in an after trigger
On Mon, 10 Nov 2008 19:43:20 -0500, Andy B wrote:


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...)


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

On Wednesday, November 12, 2008 5:11 AM
Uri Dimant wrote:

Hi HugoWhat did you mean?
Hi Hugo

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 Wednesday, November 12, 2008 5:27 AM
Hugo Kornelis wrote:

Re: Detecting insert, update and delete in an after trigger
On Wed, 12 Nov 2008 12:11:15 +0200, Uri Dimant wrote:


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

On Wednesday, November 12, 2008 6:25 AM
Uri Dimant wrote:

Hi HugoOk, it is most likely IF UPDATE(col) will fire even col was really
Hi Hugo

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

On Wednesday, November 12, 2008 7:24 AM
Andy B wrote:

Re: Detecting insert, update and delete in an after trigger
Makes sense...

On Thursday, November 13, 2008 5:44 AM
Eric Isaacs wrote:

In an after insert/update/delete trigger, all inserts and updates willbe in
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

On Thursday, November 13, 2008 5:45 AM
Eric Isaacs wrote:

My suggestion was that you check counts in Inserted and Deleted.
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

On Thursday, November 13, 2008 5:45 AM
Alex Kuznetsov wrote:

Re: Detecting insert, update and delete in an after trigger
As you have seen, the only robust way is to create or generate
separate triggers for insert, delete and update.

On Tuesday, March 31, 2009 12:25 AM
Paul White wrote:

Detecting the type of change inside an INSERT, UPDATE, DELETE trigger
Couldn't resist the challenge

It's not perfect, but it does correctly identify all possible changes including no rows affected.

DROP TABLE dbo.T1
CREATE TABLE dbo.T1 (PK INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, A INT NULL, B INT NULL)
GO
CREATE TRIGGER trg_I ON dbo.T1 AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @rc INT;
SET @rc = @@rowcount;
SET NOCOUNT ON;

IF @rc = 0 BEGIN SELECT 'no action' RETURN END;

IF EXISTS (SELECT 1 FROM inserted)
BEGIN
IF EXISTS (SELECT 1 FROM deleted)
BEGIN
SELECT COUNT_BIG(*) AS rows_updated
FROM inserted AS i
JOIN deleted AS d ON (i.PK = d.PK)
WHERE COALESCE(i.A, 0.5) <> COALESCE(d.A, 0.5)
OR COALESCE(i.B, 0.5) <> COALESCE(d.B, 0.5)
END
ELSE
BEGIN
SELECT COUNT_BIG(*) AS rows_inserted
FROM inserted AS i
END
END
ELSE
BEGIN
SELECT COUNT_BIG(*) AS rows_deleted
FROM deleted AS d
END
END
GO
BEGIN TRAN
INSERT dbo.T1(A, B) VALUES (1, 2) -- rows_inserted = 1
INSERT dbo.T1(A, B) VALUES (1, 2) -- rows_inserted = 1
INSERT dbo.T1(A, B) VALUES (3, 4) -- rows_inserted = 1
INSERT dbo.T1(A, B) SELECT A, B FROM dbo.T1 WHERE A = 1 -- rows_inserted = 2
UPDATE dbo.T1 SET B = 3 WHERE B = 2 -- rows_updated = 4
UPDATE dbo.T1 SET B = 3 WHERE B = 2 -- no_action
UPDATE dbo.T1 SET B = 3 WHERE B = 3 -- no_action
UPDATE dbo.T1 SET A = NULL WHERE A = 3 -- rows_updated = 1
UPDATE dbo.T1 SET A = NULL WHERE A IS NULL -- rows_updated = 0
UPDATE dbo.T1 SET A = 1 WHERE A IS NULL -- rows_updated = 1
DELETE dbo.T1 WHERE A IS NOT NULL -- rows_deleted = 5
ROLLBACK

Cheers,

</Paul>


Submitted via EggHeadCafe - Software Developer Portal of Choice
A Framework to Animate WPF and Silverlight Pages Similar to the PowerPoint Slides
http://www.eggheadcafe.com/tutorials/aspnet/7390a840-dd39-4c35-9940-c7...940d878