Re: Trigger, Identify Insert, Update AND delete in one
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 26 Jun 2009 03:01:39 -0500
seanD (sean.denney@xxxxxxxxx) writes:
Sorry for the newbie question, but I would like to simply populate a
tracking table with 'I', 'U' or 'D' (and the PK id) based on the type
of change being made to the table with a trigger. My question is: Do
I have to query all the psuedo tables to do that? ? Or is there an
easier or mor efficient way? Do I need to create 3 triggers and do
each seperately? I know the below will not work because updates will
be counted multiple times and it seems like there should be an easier
way than doing some sort of anti join between deleted and inserted.
In Oracle you can use IF inserting... else if updating...else if
deleting...
In SQL Server you can replace this with
IF EXISTS (SELECT * FROM inserted) AND
NOT EXISTS (SELECT * FROM deleted)
For "If inserting..." and similar for the other.
In practice you can write your trigger if you want to save some typing as:
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
INSERT mod_test (id, action)
SELECT id, 'U' FROM deleted
END
ELSE
BEGIN
INSERT mod_test (id, action)
SELECT id, 'U' FROM inserted
UNION ALL
SELECT id, 'D' FROM deleted
END
The alternative is to use Change Data Capture, which is a new feature
in SQL 2008, which uses the same mechanisms as replication, and is
asynchronous. It's probably more work to set up, but may cause less
contention than using a trigger.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Prev by Date: tracking Update, insert, delete during/after replication
- Next by Date: Re: Trigger, Identify Insert, Update AND delete in one
- Previous by thread: Re: Trigger, Identify Insert, Update AND delete in one
- Next by thread: Re: Trigger, Identify Insert, Update AND delete in one
- Index(es):
Relevant Pages
|
