Oh it can't be THAT easy... ;)

-----Original Message-----
From: Robert Davis
[mailto:mssqldba-ezmlmshield-x64905152.[Email address protected]
Sent: Monday, October 31, 2005 4:20 PM
To: LazyDBA Discussion
Subject: RE: Proc to write Trigger

Use Char(). Char(9) = tab, Char(10) = line feed, and char(13) =
carriage return


Robert Davis


-----Original Message-----
From: Ken Ross
[mailto:mssqldba-ezmlmshield-x47198533.[Email address protected]
Sent: Monday, October 31, 2005 1:15 PM
To: LazyDBA Discussion
Subject: RE: Proc to write Trigger

Ok, just a couple more questions on this (fairly minor - I hope). First,
I have this generally doing what I wanted (thanks again!). However, the
generated code isn't very "pretty" and I'd like to correct that by
including carriage returns/line feeds in the string. So, what I'm
looking for is something like this:

Set @myExecString = 'Create Trigger MyTrigger ' + CR + 'On ' +
@parentTable + ' Instead Of Delete' + CR + ...

Also, in one case I need to include a quoted string in the string I'm
building, and I don't know how to get the single quote itself to be part
of the string - any ideas here?

Thanks again!


-----Original Message-----
From: Ken Ross
[mailto:mssqldba-ezmlmshield-x82621939.[Email address protected]
Sent: Monday, October 31, 2005 2:27 PM
To: LazyDBA Discussion
Subject: RE: Proc to write Trigger

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



---------------------------------------------------------------------
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



---------------------------------------------------------------------
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