Create triggers on all tables
-
Hi all!
I'm a little bit lost here. I have a db with ~300 tables. I want to create a trigger on all tables containing a certain column.
The trigger should update a datetime field in the same table and insert a log entry in another table on updates and inserts.
This is a development server, so I would happily use sp_msforeachtable. I also have a working function that checks for the existence of the column but I can't get it together. That's what I got so far:
exec sp_msforeachtable
'if dbo.checkforcolumn(?,''columnname'')=''exists''
begin
create trigger foo on ?
for insert, update
as
do stuff
end'
This gives me a syntax error near "trigger".
The code for the trigger itself is working, too, I just don't want to check all the tables manually and add the trigger.
I'm sure I'm doing something stupid here, but my google-foo failed me :-(.
TIA,
Chris
-
From BOL:
"CREATE TRIGGER must be the first statement in the batch and can apply to only one table."
_____________
Code for TallyGenerator -
Ha! Got it. Forgot about the first statement thing. So I tried:
exec sp_msforeachtable
'if dbo.checkforcolumn(''?'',''columnname'')=''exists''
begin
exec ''create trigger foo on ?
for insert, update
as
do stuff''
end'
Still syntax error. So I replaced exec with print to check the syntax and it was correct. Huh? So I ended up with
exec sp_msforeachtable
'if dbo.checkforcolumn(''?'',''columnname'')=''exists''
begin
print ''create trigger foo_'' + substring(''?'',9,len(''?'')-9) + '' on ?
for insert, update
as
do stuff
GO''
end'
which prints a script that perfectly works
. The substring part strips the tablename from the owner prefix and square brackets and adds the table name to the trigger name to make it unique. Has to be adjusted depending on the owner.Using exec instead of print doesn't work, though, probaby because with exec, create trigger still isn't the first statement. But who cares. It's a dirty hack with an undocumented sp anyway, and the resulting script does the job.
Thank you so much for pointing me in the right direction!
-
exec sp_msforeachtable
'if dbo.checkforcolumn(''?'',''columnname'')=''exists''
begin
exec (''create trigger foo on ?
for insert, update
as
do stuff'')
end'
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply