none
Creating a Trigger Inside a Stored Procedure RRS feed

  • Question

  • Can someone please adviase me int he right direction,

    Basically i am trying to construct some stored procedure which will fully build my database again (if the worst ever happened) However i have it all working except the part regarding the triggers. How are "CReate Trigger COdes saved within the databse, so that they can be easily exectured again incase i drop the table it relates to.

    Now i have read a few forums and i come to believe that Create Trigger must be the only command in the batch, Does anyone know of any alternative places to save "Create Trigger" commands to. This will allow me to referance it within a stored procedure perhaps therefore integrating it into the "database Construction" stored procedure i have created.

    Many Thanks

    Thursday, April 1, 2010 4:04 PM

Answers

  • You can save create trigger statements as a script and then execute using execute (@CreateTriggerScript)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Thursday, April 8, 2010 5:25 AM
    Thursday, April 1, 2010 6:25 PM
    Moderator
  • You need to build a dynamic SQL stored procedure to create a trigger in the sproc. Demo follows. Let us know if helpful.

    USE tempdb;
    SELECT ProductNumber, ListPrice, Color
    INTO Product
    FROM AdventureWorks2008.Production.Product
    GO
    CREATE PROC sprocCreateDynamicTrigger
    AS
    BEGIN
      DECLARE @SQL nvarchar(max)=
    	'CREATE TRIGGER trgProduct
    	on Product for INSERT
    	AS
    	DECLARE @InsProd varchar(32) 
    	SELECT @insProd = ''TRIGGER: '' + ProductNumber FROM inserted
    	PRINT @InsProd'
      EXEC sp_executeSQL @SQL
    END
    GO 
    -- Execute stored procedure to create trigger
    EXEC sprocCreateDynamicTrigger
    GO
    INSERT Product VALUES ('Alpha Romeo 2011', 40000, 'Blue')
    GO
    -- TRIGGER: Alpha Romeo 2011
    
    
    DROP  PROC sprocCreateDynamicTrigger
    DROP TABLE tempdb.dbo.Product
    
    

    Dynamic SQL example links:

    http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/

    http://www.sqlusa.com/bestpractices2005/quotename/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by KJian_ Thursday, April 8, 2010 5:25 AM
    Wednesday, April 7, 2010 6:58 AM
    Moderator

All replies

  • Can someone please adviase me int he right direction,

    Basically i am trying to construct some stored procedure which will fully build my database again (if the worst ever happened) However i have it all working except the part regarding the triggers. How are "CReate Trigger COdes saved within the databse, so that they can be easily exectured again incase i drop the table it relates to.

    Now i have read a few forums and i come to believe that Create Trigger must be the only command in the batch, Does anyone know of any alternative places to save "Create Trigger" commands to. This will allow me to referance it within a stored procedure perhaps therefore integrating it into the "database Construction" stored procedure i have created.

    Many Thanks


    Opps, sorry for my spelling mistakes, i did forget to proof read before i submitted the question
    Thursday, April 1, 2010 4:06 PM
  • You can save create trigger statements as a script and then execute using execute (@CreateTriggerScript)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Thursday, April 8, 2010 5:25 AM
    Thursday, April 1, 2010 6:25 PM
    Moderator
  • You need to build a dynamic SQL stored procedure to create a trigger in the sproc. Demo follows. Let us know if helpful.

    USE tempdb;
    SELECT ProductNumber, ListPrice, Color
    INTO Product
    FROM AdventureWorks2008.Production.Product
    GO
    CREATE PROC sprocCreateDynamicTrigger
    AS
    BEGIN
      DECLARE @SQL nvarchar(max)=
    	'CREATE TRIGGER trgProduct
    	on Product for INSERT
    	AS
    	DECLARE @InsProd varchar(32) 
    	SELECT @insProd = ''TRIGGER: '' + ProductNumber FROM inserted
    	PRINT @InsProd'
      EXEC sp_executeSQL @SQL
    END
    GO 
    -- Execute stored procedure to create trigger
    EXEC sprocCreateDynamicTrigger
    GO
    INSERT Product VALUES ('Alpha Romeo 2011', 40000, 'Blue')
    GO
    -- TRIGGER: Alpha Romeo 2011
    
    
    DROP  PROC sprocCreateDynamicTrigger
    DROP TABLE tempdb.dbo.Product
    
    

    Dynamic SQL example links:

    http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/

    http://www.sqlusa.com/bestpractices2005/quotename/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by KJian_ Thursday, April 8, 2010 5:25 AM
    Wednesday, April 7, 2010 6:58 AM
    Moderator