SQL Server 中 觸發器 簡單樣本

注意 對 inserted,deleted 的查詢 使用
-------------------------

--當person表 新增 一條person記錄時
--將 該記錄的主鍵personName 儲存在changeTable表中
USE TestDB
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'personInsert' AND type = 'TR')
   DROP TRIGGER personInsert
GO
CREATE TRIGGER personInsert
ON person
FOR INSERT
AS

   INSERT INTO TestDB.dbo.changeTable
  (changeTableName, changeType, changeTablePK, changeTablePKValue)
  SELECT 'person','INSERT','personName',personName FROM INSERTED
GO

--當person表 修改 一條person記錄時
--將 該記錄的主鍵personName 儲存在changeTable表中

USE TestDB
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'personUpdate' AND type = 'TR')
   DROP TRIGGER personUpdate
GO
CREATE TRIGGER personUpdate
ON person
FOR UPDATE
AS

   INSERT INTO TestDB.dbo.changeTable
  (changeTableName, changeType, changeTablePK, changeTablePKValue)
  SELECT 'person','UPDATE','personName',personName FROM INSERTED
GO

--當person表 刪除 一條person記錄時
--將 該記錄的主鍵personName 儲存在changeTable表中

USE TestDB
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'personDelete' AND type = 'TR')
   DROP TRIGGER personDelete
GO
CREATE TRIGGER personUpdate
ON person
FOR DELETE
AS

   INSERT INTO TestDB.dbo.changeTable
  (changeTableName, changeType, changeTablePK, changeTablePKValue)
  SELECT 'person','DELETE','personName',personName FROM DELETED
GO