Create trigger within a procedure?

Question

Create trigger within a procedure?

Navigation

#1 by (3 votes)

4

I would like to know if it is possible to create a Trigger within a procedure? (SQL SERVER)

The reason is that I drop the table that is triggered at the end of the day, and then recreates it with a procedure, and after creating the table, it re-creates the trigger too.

sql-server

asked by anonymous 03.09.2014 / 19:24

1 answer

How to force the download of a file, which is stored in the database Transform an array to string in python

score 3 · Answer 1

Yes you can do yes as follows in the example of microsoft itself:

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