Trigger, Identify Insert, Update AND delete in one
seanD
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...
Thanks for any help.
Tracked Table
CREATE TABLE [dbo].[test]( [id] [int] NULL, [name] [nchar](10) NULL)
Tracking Table (modifications)
CREATE TABLE [dbo].[mod_test]([id] [int] NULL,[code] [nchar](1) NULL)
Trigger
ALTER TRIGGER dbo.blah
ON dbo.test
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
INSERT INTO mod_test SELECT id, 'D' FROM deleted
UNION ALL
SELECT id, 'I' FROM inserted
UNION ALL SELECT i.id, 'U' FROM deleted d INNER JOIN inserted i ON
i.id = d.id
END
Plamen Ratchev
Try this:
INSERT INTO test VALUES(1, 'abc');
UPDATE test
SET name = 'def'
WHERE id = 1;
This will result in:
/*
id code
----------- ----
1 I
1 D
1 I
1 U
*/
You can change the trigger (assuming the ID column is never updated) like this:
INSERT INTO mod_test
SELECT id, 'D' FROM deleted AS D
WHERE NOT EXISTS (SELECT *
FROM inserted AS I
WHERE D.id = I.id)
UNION ALL
SELECT id, 'I' FROM inserted AS I
WHERE NOT EXISTS (SELECT *
FROM deleted AS D
WHERE D.id = I.id)
UNION ALL SELECT i.id, 'U' FROM deleted d
INNER JOIN inserted i ON i.id = d.id;
An alternative is to create 3 separate triggers.
This is also possible in your application layer, for example if you have specific stored procedures that perform
insert/update/delete (and of course, no other interface allow users to perform those actions).
--
Plamen Ratchev
http://www.SQLStudio.com
seanD
suggestion was sort of the way I was headed. I just wasn't sure if
there was a simpler way that I was overlooking being fairly new to
sqlserver. I do have one other question if you might happen to know.
I am using this on a table that is replicated using transactional
replication. Have I missed any more simple and performant way to
identify I, U, D within replication? Unfortunately I don't have the
time to learn more about SSIS, otherwise I assume that would be
ideal.
Anyway THANKS! very much.
Sean
Plamen Ratchev
additional comments on that. SSIS can import/export/transform data but not sure how it can help with change tracking.
Plamen Ratchev
in some cases it may meet the needs.
http://blog.benhall.me.uk/2007/06/sql-server-2008-change-data-capture-cdc.html
http://www.databasejournal.com/features/mssql/article.php/3725476/Microsoft-SQL-Server-2008----Change-Data-Capture--Part-2.htm
Erland Sommarskog
> 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, esq...@sommarskog.se
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
Hugo Kornelis
>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?
Hi seanD,
Frankly, I never understand why so many people seem to be hell-bent on
having a single trigger for INSERT, UPDATE, and DELETE if all they do in
that trigger depends on what operation caused it to execute. So I am
glad that you at least seem to understand that there is an alternative
and that it might be viable. And I'm surprised none of the other who
replied advised you to do exactly that: create 3 triggers, one for each
activity.
If you are in a situation where, for instance, the response to updates
needs to be the same as the response to inserts, then sure, go ahead and
use a single trigger for both INSERT and UPDATE. It won't give you any
performance benefit, but it does save on the amount of code to write and
maintain, and that is A Good Thing (TM).
If the actions for inserts and updates are "almost" the same, both
options might make sense. You'll have to wheigh the performance penalty
and the extra complexity in the code against duplicating a potentially
large amount of code. Tough call.
But in situations where there are more differences than similarities
between the actions required for each operation, then you gain nothing
by using a single trigger. Performance will suffer, and though you did
save one or two objects to write and maintain, the complexity of the
remaining object goes way up, so that the total maintenance burden will
rise, not diminish.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
--CELKO--
Have you read about the log file yet? You have all that info and more
already without extra overhead.
Erland Sommarskog
isn't.
There is a transaction log, but the information only remains there until
you have backed up the log. Since log backups are typically scheduled, this
means that this may happened the second after the DML operation. And if
you are running in simple recovery, the log is gone even faster.
Furthermore the transaction log has a proprietary format, and Microsoft
supplies no supported way to access it. There are third-party tools out
there who can read the log and also log backups. They are developed from
reverse-engineering the log format, and as a consequence of this, they
have difficulties to keep up with the SQL Server versions.
Investigating the use of such third-party tool can certainly be worthwhile,
but you may find the price tag stiff, and depending on what you want the
tracking for, it may not be worth it.
--CELKO--
Too many people re-invent the wheel
>> Investigating the use of such third-party tool can certainly be worthwhile, but you may find the price tag stiff, and depending on what you want the tracking for, it may not be worth it. <<
Yes, you will have to weigh the cost of a package against the cost of
in-house development. Will you be able to afford the performance hit
that comes with triggers? The inability to port them if you move to
another product. When SQL Server changes will you have the time and
money to maintain your extra code. Will you write the nice stats and
graphics that a package will have? Since third party vendors usually
have more than one product and they fit together, in-house code will
probably not be built for add-on and extensions.
This is the classic "built or buy" problem. The last "rule of thumb"
was that if your in-house development costs are over $10/hr per
programmer, then "buy" is a better decision over the lifetime of a
system.
Erland Sommarskog
> Yes, you will have to weigh the cost of a package against the cost of
> in-house development. Will you be able to afford the performance hit
> that comes with triggers? The inability to port them if you move to
> another product.
Triggers are more likely to be portable than a product built on the
transaction log.
> When SQL Server changes will you have the time and money to maintain
> your extra code.
A much more interesting question is whether the vendor will have the
time and money to update their product. After all, triggers are a
supported part of SQL Server, and compatibility is to be expected.
Reading the log on the other hand is not.
For instance, I went to www.lumigent.com. Lumigent were the first to
supply a log-reading tool for SQL Server. I could not find a word about
SQL Server. They have a product AuditDB which they claim to be the
leader - in the Oracle sphere.
And Sean wanted this to track updates on the subscribing end of replicaiton.
I don't know what for, but it does not sound like something a vendor package
wouls solve, anyway. (But the new feature Change Data Capture in SQL 2008
might.)