Thanks (and thanks to Paul too) for pointing me at Exec(). The situation
is, admittedly, a little odd but is a work-around for a bug/limitation
in SQL 2005 related to Logical Records; LRs aren't compatible with
CASCADE and so you have to write your own triggers to handle things like
cascade delete if you plan to use Logical Records with those tables.
By using a DDL trigger, I can detect when sometimes *tries* to use
CASCADE and undo it, and write a cascade delete trigger instead. Helps
keep it simple for the developers and ensure consistency with the delete
trigger.
-----Original Message-----
From: Robert Davis
[mailto:mssqldba-ezmlmshield-x35969470.[Email address protected]
Sent: Monday, October 31, 2005 2:21 PM
To: LazyDBA Discussion
Subject: RE: Proc to write Trigger
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
---------------------------------------------------------------------
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