触发器

最新推荐文章于 2025-06-09 09:00:00 发布

CalvinR

最新推荐文章于 2025-06-09 09:00:00 发布

阅读量698

点赞数

CC 4.0 BY-SA版权

分类专栏： MS SQL 文章标签： encryption null insert table delete sql

本文链接：https://blog.youkuaiyun.com/TERMINATOR_LZQ/article/details/6757532

MS SQL 专栏收录该内容

6 篇文章

订阅专栏

本文详细介绍了如何在SQL Server中创建AFTER DML触发器，用于实时记录表的插入、更新和删除操作，进而实现数据审计功能。通过创建触发器，可以将关键操作数据保存到审计表中，便于后续查询和分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建触发器

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION]
{
{ { FOR | AFTER | INSTEAD OF } { [INSERT] [,] [UPDATE]}
[ WITH APPEND]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( columns_updated ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement[ ...n ]
}
}

-----------创建 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)

--检测审核表--