November 18, 2009 at 2:22 pm
Is there any way to execute a "create trigger" inside another stored procedure?
I need to re-create a trigger over a table using a stored procedure that makes a control of the trigger presence.
Below a sample of what I intend:
...
...
If NOT EXISTS (SELECT * FROM sysobjects WHERE xtype = 'TR' AND [name] = 'BTPRJ_DeleteRilevazione')
EXEC MySpWithCreateInside
...
...
The problem is that SQL doesn't allow CREATE TRIGGER not in the first position of the sp.
Thanks in advance
Antonio
November 18, 2009 at 2:57 pm
What have you tried so far?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 18, 2009 at 3:46 pm
Realistically you can't create it directly, you have to go at it a little more round-about way.
Within your sproc you could add a statement like:
EXEC ('CREATE TRIGGER trTriggerName ON dbo.SomeTable FOR INSERT
AS
BEGIN
-- Do some work
END' )
CEWII
November 18, 2009 at 5:11 pm
That was going to be my next post after he responded with what had been tried already.
Quetzco, as Elliott outlined - it can be done through the use of either the exec statement or dynamic sql.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 19, 2009 at 2:37 am
Thanks Elliot and Jason.
I'll follow the EXEC ('......') method.
My intent was to find something to avoid to replicate the statements executed by a trigger in an other SP mainly because I have to maintain two sets of procedures (and my trigger is just a little bit complicated).
BR
Antonio
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply