Create Rigger within a Precedent?

Asked

Viewed 466 times

4

I wonder if it is possible to create a Trigger within a Database? (SQL SERVER)

The reason is that I drop the table that is with Rigger at the end of the day, and then recreate it with a precedent, and after the creation of the table, he recreates Rigger as well....

sql-server

1 answer

3

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

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

  • Vlw man, good to know from sp_executeSQL!

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.