Automatically generating triggers via a procedure that is run by an
existing trigger sounds risky. Triggers can hinder performance and I
would never create a trigger without testing it first.

That aside, I'm guessing that you're trying to execute the create
trigger code directly in the stored procedure. As you can see from the
ensuing error messages, you can't do that. You'll need to generate the
trigger code as a dynamic string and then execute that string using
sp_executesql or Exec().


Robert Davis


-----Original Message-----
From: Ken Ross
[mailto:mssqldba-ezmlmshield-x53397067.[Email address protected]
Sent: Monday, October 31, 2005 11:06 AM
To: LazyDBA Discussion
Subject: Proc to write Trigger

I'm trying to write a stored procedure that can generate Delete
Triggers. I have a situation where, depending on another database
modification, I want to automatically write a trigger. I've trapped the
database change in a DDL trigger but now need to have it call a proc,
passing in a table name and other parameters, in order to create the
corresponding delete trigger.

If anyone has any experience with generating triggers through TSQL code,
I'd really appreciate some insight. I'm having a lot of trouble getting
this going, not the least of which is just getting a basic "create
trigger" to run from within a stored procedure (maybe this is entirely
the wrong place??).

Thanks much!


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page