TRIGGER

CREATE TABLE tempTable_A
(
    ID INT IDENTITY(1,1),
    Name NVARCHAR(10) NOT NULL
)
CREATE TABLE tempTable_B 
(
    Message NVARCHAR(100) NOT NULL
)

◎ INSERT/UPDATE/DELETE TRIGGER

IF EXISTS(SELECT * FROM SYS.objects WHERE type = 'TR' AND name = N'trg_InsteadOfInsertA'
)
BEGIN
  DROP TRIGGER [trg_InsteadOfInsertA]
END
GO

--INSERT TRIGGER
--此範例是模擬如果有一筆資料新增至tempTable_A，則會觸發TRIGGER，也新增一筆資料至tempTable_B 
CREATE TRIGGER trg_InsteadOfInsertA ON tempTable_A--也適用於VIEW
AFTER INSERT
AS
BEGIN
  INSERT INTO tempTable_B 
  SELECT [Value]
  FROM   Inserted;
END
GO

◎ INSTEAD OF INSERT/UPDATE/DELETE TRIGGER

IF EXISTS(SELECT * FROM SYS.objects WHERE type = 'TR' AND name = N'trg_InsteadOfInsertA'
)
BEGIN
  DROP TRIGGER [trg_InsteadOfInsertA]
END
GO

CREATE TRIGGER trg_InsteadOfInsertA ON tempTable_A--也適用於VIEW
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @ID INT;
    DECLARE @NAME NVARCHAR(10);

    SELECT @ID = ID,
           @NAME = NAME
    FROM inserted

    BEGIN
        INSERT tempTable_A SELECT NAME FROM inserted
        INSERT tempTable_B SELECT '新增一筆' + NAME FROM inserted
    END;
END

--試著INSERT兩筆資料
INSERT tempTable_A VALUES ('A'),('B')
--撈資料看看變化
SELECT * FROM tempTable_A
SELECT * FROM tempTable_B

◎ Create一個監看所有CREATE、ALTER、DROP TABLE的TRIGGER

CREATE TABLE AuditTableStructureChanges
(
      EventType NVARCHAR(300) ,
      PostTime DATETIME ,
      SPID NVARCHAR(300) ,--Server Process ID
      ServerName NVARCHAR(300) ,
      LoginName NVARCHAR(300) ,
      UserName NVARCHAR(300) ,
      DatabaseName NVARCHAR(300) ,
      SchemaName NVARCHAR(300) ,
      ObjectName NVARCHAR(300) ,
      ObjectType NVARCHAR(300) ,
      TSQLCommand NVARCHAR(MAX)
);

CREATE TRIGGER trgAuditTableStructureChanges ON ALL SERVER
  FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
  BEGIN

  DECLARE @EventData XML;
  SELECT  @EventData = EVENTDATA();
  INSERT  INTO [Sample].[dbo].[AuditTableStructureChanges]
  ( EventType , PostTime , SPID , ServerName , LoginName , UserName ,
    DatabaseName , SchemaName , ObjectName , ObjectType , TSQLCommand 
  )
  VALUES  ( @EventData.value('(/EVENT_INSTANCE/EventType)[1]','NVARCHAR(300)') ,
            @EventData.value('(/EVENT_INSTANCE/PostTime)[1]','DATETIME') ,
            @EventData.value('(/EVENT_INSTANCE/SPID)[1]','NVARCHAR(300)') ,
            @EventData.value('(/EVENT_INSTANCE/ServerName)[1]','NVARCHAR(300)') ,
            @EventData.value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(300)') ,
            @EventData.value('(/EVENT_INSTANCE/UserName)[1]','NVARCHAR(300)') ,
            @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(300)') ,
            @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','NVARCHAR(300)') ,
            @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(300)') ,
            @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]','NVARCHAR(300)') ,
            @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','NVARCHAR(MAX)')
  );
  END;
GO

◎ 撈出所有的 TRIGGER

SELECT *
FROM SYS.objects
WHERE type = 'TR'