Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

How do I create one trigger on multiple tables

I have a database on SQL Server 2005, In this database I have 90 tables.
I want to create a trigger which can monitor all 90 tables Insert, Update, Delete operations and execute a storeporcude.
0
vritti
Asked:
vritti
  • 4
  • 4
  • 2
7 Solutions
 
chapmandewCommented:
Then you need to create 90 different triggers...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1 trigger is always only on 1 table, you cannot have 1 trigger "watch" over several tables at once.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
depending on what you are really trying to do, you might want to run a trace instead of having a trigger...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
chapmandewCommented:
the trace is a good idea, but you wouldn't be able to execute a stored procedure based on the results from the trace.
0
 
pcelbaCommented:
I would recommend to programmatically create the script which will do the whole work for you. It can create script for all 90 tables. The only input is a list of these 90 tables which you could probably obtain from sysobjects.
0
 
pcelbaCommented:
The script should create 90 triggers, of course. If they are all doing the same job the code will be very simple - just one SP call.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>the trace is a good idea, but you wouldn't be able to execute a stored procedure based on the results from the trace.

that's why I write "depending on what you are really trying to do ..."
:)
0
 
chapmandewCommented:
:)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
btw: you can run a trace to fill a table.....
SOOOO, having a trigger on that (single) table, you could then run a procedure :)
anyhow, let's wait for the asker
0
 
chapmandewCommented:
thats true, you can do that...not sure you could do that to do what the user is asking though.

years and years ago I wrote some scripts that would take a table and generate the audit scripts for that table.  however, it would be MUCH more complex to design it so that it looked through the records and called a proc for each row (I know you know this, angel)...just letting the asker know that theres no quick and easy solution for this.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now