How to create trigger for all tables in database on insert, update table?

Question

How to create trigger for all tables in database on insert, update table?

Viewing 4 posts - 16 through 18 (of 18 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Glad we could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

chethan.cn Old Hand Points: 315 More actions · Answer 2

I have to create a trigger for all the insert ,update and delete

Can we have only one trigger for the entire database to monitor the insert ,update and delete on the tables?

If any one tries to update/delete to any of the tables in the database then it should capture the use name, workstation and the command along with time stamp to the Xtable.

I have created the Xtable to store the information of user name,workstaion and time stamp,

Please let me know how the trigger will store the information in Xtable

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

chethan.cn (8/4/2009)
I have to create a trigger for all the insert ,update and delete
Can we have only one trigger for the entire database to monitor the insert ,update and delete on the tables?

You cannot do this with a single trigger, you need to have at least one for each table. However, you CAN have one trigger code "template" and copy it to every table. How easy or difficult this is depends on what it needs to do and how standardized your table definitions & design are.

If any one tries to update/delete to any of the tables in the database then it should capture the use name, workstation and the command along with time stamp to the Xtable.

Capturing the SQL Command text itself is not something that a DML trigger can reasonably do. Unlike DDL triggers, the command text is not automatically provided to it and although it may be accessible in the system_tables, the whole issue of Session to Requests/batches/procedures nesting makes it extremely difficult to figure out which one is the one that caused the trigger. You may want to look at the C2 Auditing feature for this.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

chethan.cn Old Hand Points: 315 More actions · Answer 4

i am using sqlserver 2005 ,in this version can we have on one trigger for the entire database