one trigger for every table ?
-
I've taken a cursory look at the FAQ's and current topics and can't find anything that would address this issue, so here goes... Is there a way to create one trigger that would fire for a deletion on any table in the databse? I'd like to track deletions in my database and store them in a common table with a structure something like this:
TD_DELETE (
TableName varchar(250),
ID integer,
DeletedDate datetime
)
You assistance is greatly appreciated.
-
what a difference a Subject line makes... SO, now that I have "read" my own post 10 times, and only two other people have looked at it, I will post my own reply in an effort to attract attention. I guess this just can't be done and I'll have to make 200+ delete triggers instead of one.
Edited by - joshcsmith13 on 08/11/2003 12:50:16 PM
-
You'd have to put a trigger on each table.
Andy
-
Need to do one for each table, it wouldn't be that hard to script however. Also there are some audit tools that do this for you.
-
Hi joshcsmith13,
quote:
Is there a way to create one trigger that would fire for a deletion on any table in the databse?
what is the reason for this?
Just asking, because I asked some time ago somewhere else a same question with the intention to track admins activity on the Server. It turned out that I 'simply' had to remove BUILTIN\Administrators to get rid of this problem.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The reason for this trigger is this: the project I am working on has many independant databases running on various client sites. A communication process in the application scans all tables for records with a "LastChanged" date greater than the last communication and sends them up for insertion or update in a large corporate database/warehouse. This process has been in place and working fine for a while now. In striving to keep a more accurate depiction of each clients data, we would like to track their deletions as well.
-
this blows. Now that I've gone to the trouble of creating a trigger for every table, I realize that there is some lookup/processing necessary before the record is deleted, so an AFTER DELETE trigger doesn't work. And when I try an INSTEAD OF DELETE trigger, I get errors because the majority of my tables have cascading delete Foreign Keys!! AAAAAAAAARGH!
Any magical suggestions/solutions are welcomed, as well as empathic confirmation that it is hopeless.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply