one trigger for every table ?

Question

one trigger for every table ?

joshcsmith13

Hall of Fame

Points: 3381
More actions
August 11, 2003 at 9:09 am

#159837

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 1

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

Andy Warren SSC Guru Points: 119902 More actions · Answer 2

You'd have to put a trigger on each table.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

HenryTr SSC Eights! Points: 845 More actions · Answer 3

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.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

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]

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 5

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.

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 6

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.