创建 AFTER DML 触发器

转载

mb5fed6f751964c 2011-12-26 23:29:00

-----------创建 AFTER DML 触发器----------

CREATE TRIGGER [ scema_name . ]trigger_name ----可选的架构拥有者以及必需的用户定义的新触发器的名字

on table ----------应用触发器的表名

--------允许你指定ENCRYPTION和/EXECUTE AS子句。ENCRYPTION回加密T-SQL的定义
[ WITH <dml_trigger_option>[...,n]]

AFTER 
{
[INSERT][,][UPDATE] [,][DELETE]
}
[NOT FOR REPLICATION]

AS{sql_statement[...n]} ------一个触发器允许多个T-SQL语句

 

--跟踪所有的插入、更新和删除--

CREATE TABLE Production.ProductInventoryAudit

( ProductID int NOT NULL,
LocationID smallint NOT NULL,
Shelf nvarchar(10) NOT NULL,
Bin tinyint NOT NULL,
Quantity smallint NOT NULL,
rowguid uniqueidentifier NOT NULL,
ModifiedDate datetime NOT NULL,
InsOrUPD char(1) NOT NULL
)


GO


--创建触发器来填充Production.ProductInventoryAudit表

CREATE TRIGGER Production.trg_uid_ProductInventoryAudit

ON Production.ProductInventory
AFTER INSERT,DELETE
AS


SET NOCOUNT ON

--插入的行--
INSERT Production.ProductInventoryAudit
(
ProductID,LocationID,Shelf,Bin,Quantity,
rowguid,ModifiedDate,InOrUPD
)

SELECT DISTINCT i.Location,i.ProductID,i.Bin,i.Shelf,i.Quantity,i.rowguid,GETDATE(),'I' FROM inserted i


--删除的行--
INSERT Production.ProductInventoryAudit
(
ProductID,LocationID,Shelf,Bin,Quantity,
rowguid,ModifiedDate,InOrUPD
)
SELECT DISTINCT D.Location,D.ProductID,D.Bin,D.Shelf,D.Quantity,D.rowguid,GETDATE(),'D' FROM DELETED D

GO

--插入一个新行--
INSERT Production.ProductInventory
(ProductID,LocationID,Shelf,Bin,Quantity)
VALUES(316,6,'A',4,22)

--检测审核表--