SQL--create trigger | drop trigger

最新推荐文章于 2025-02-15 20:06:43 发布

Stephanie_0903

最新推荐文章于 2025-02-15 20:06:43 发布

阅读量698

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/Stephanie_0903/article/details/88183343

SQL 专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了如何在SQL Server中创建三种类型的触发器：删除触发器、插入触发器和更新触发器，用于记录Application_Log表上的操作历史。通过具体代码示例，读者可以学习到如何在数据库中实现数据变更审计。

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

1. create "delete" trigger

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Application_Log_Audit_DeleteTrigger]') AND type in (N'TR', N'TRG'))
DROP TRIGGER [dbo].[Application_Log_Audit_DeleteTrigger]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[Application_Log_Audit_DeleteTrigger] ON [dbo].[Application_Log]
AFTER DELETE AS
BEGIN

INSERT INTO [dbo].[Application_Log_Audit]
	(
	[A],
	[B],
	[C],
	[VersionNumber],
	[CreatedBy],
	[CreatedOn],
	[UpdatedBy],
	[UpdatedOn],
	[Audit_Action],
	[Audit_Created_Date]
	)
SELECT [id] As [A],
	[B],
	[C],
	[VersionNumber],
	[CreatedBy],
	[CreatedOn],
	[UpdatedBy],
	[UpdatedOn],
	'Delete',
	GETDATE()
FROM DELETED
	
END
GO

2. create "insert" trigger

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Application_Log_Audit_InsertTrigger]') AND type in (N'TR', N'TRG'))
DROP TRIGGER [dbo].[Application_Log_Audit_InsertTrigger]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Application_Log_Audit_InsertTrigger] ON [dbo].[Application_Log]
AFTER INSERT AS
BEGIN

INSERT INTO [dbo].[Application_Log_Audit]
	(
	[SystemId],
	[B],
	[C],
	[VersionNumber],
	[CreatedBy],
	[CreatedOn],
	[UpdatedBy],
	[UpdatedOn],
	[Audit_Action],
	[Audit_Created_Date]
	)
SELECT [id] As [SystemId],
	[B],
	[C],
	[VersionNumber],
	[CreatedBy],
	[CreatedOn],
	[UpdatedBy],
	[UpdatedOn],
	'Insert',
	GETDATE()
FROM INSERTED

END
GO

3. create "update" trigger

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Application_Log_Audit_UpdateTrigger]') AND type in (N'TR', N'TRG'))
DROP TRIGGER [dbo].[Application_Log_Audit_UpdateTrigger]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Application_Log_Audit_UpdateTrigger] ON [dbo].[Application_Log]
AFTER UPDATE AS
BEGIN

INSERT INTO [dbo].[Application_Log_Audit]
	(
	[SystemId],
	[B],
	[C],
	[VersionNumber],
	[CreatedBy],
	[CreatedOn],
	[UpdatedBy],
	[UpdatedOn],
	[Audit_Action],
	[Audit_Created_Date]
	)
SELECT [id] As [SystemId],
	[B],
	[C],
	[VersionNumber],
	[CreatedBy],
	[CreatedOn],
	[UpdatedBy],
	[UpdatedOn],
	'Update(Before)',
	GETDATE()
FROM DELETED

INSERT INTO [dbo].[Application_Log_Audit]
	(
	[SystemId],
	[B],
	[C],
	[VersionNumber],
	[CreatedBy],
	[CreatedOn],
	[UpdatedBy],
	[UpdatedOn],
	[Audit_Action],
	[Audit_Created_Date]
	)
SELECT [id] As [SystemId],
	[B],
	[C],
	[VersionNumber],
	[CreatedBy],
	[CreatedOn],
	[UpdatedBy],
	[UpdatedOn],
	'Update(After)',
	GETDATE()
FROM INSERTED
	
END
GO