Syntax for creating trigger inside a proc with condition 1

[manmaria]

I want the syntax for something like this

create proc
if condition
then create trigger
else
do soemething.

TIA

 

[ousoonerjoe]

Seems like a dangerous way to create TRIGGERs and a fast way to trash your databse, but here you go:

 CREATE PROCEDURE trigger_gen
AS

DECLARE @Sql VARCHAR(255)
SET @Sql = ''

IF [i]Condition[/i] = 1
    BEGIN
    SET @Sql = 'CREATE TRIGGER trigger_name
                AS
                [i]trigger stuff[/i]'
    EXECUTE (@Sql);
END

That's the basics. To my knowledge you cannot use CREATE in a StProc. You have to EXECUTE it separately as shown above. I'm sure someone else has a better way, but that will get there.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[manmaria]

I know it looks like dangerous but it is not. Thanks for your response. Yes, we need to execute this temporary proc to create the trigger. We drop the temporary proc after creating the trigger. Thanks any way.

If I am doing this from the scratch, I do not go with this approach but we are migrating from some old systems to sql08. So I am just following their old methods to convert into the sql. Lets see how it goes.

Thank you.