开发者社区> 技术小甜> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server DDL 触发器(Trigger)-- 创建数据库级别的DDL触发器

简介:
+关注继续查看

以下针对某个数据库在创建数据表时调用触发器,并将创建该数据表的用户账户写入到Windows的Event Log中。

 

1
2
3
4
5
6
7
CREATE TRIGGER reminder
ON DATABASE
FOR CREATE_TABLE
AS
DECLARE @str NVARCHAR(100)
SET @str=suser_sname() + N'create a new table'
RAISERROR(@str,10,1) WITH LOG

 

创建完DDL触发器后,因该触发器所在的等级,而会显示在“Object Explorer”中不同的位置,上述是创建数据库等级的触发器,因此,显示在某个数据库的“Programmability”中“Database Triggers”节点之下。

 

clip_image001

 

创建完DDL触发器后,请尝试使用下列语法创建数据表,由于该DDL触发器使用RAISERROR系统函数搭配WITH LOG选项,这会将信息写入到Windows操作系统的事件日志中。接着,可以在事件查看器中查询到事件内容。

 

clip_image002

 

由于触发器默认都是与引发该触发器的语法包在相同事务内一起执行,因此,我们可以通过ROLLBACK命令回滚先前指令对系统的影响,让用户的DROP_TABLE、ALTER_TABLE DDL语法无法在DB内执行。

 

1
2
3
4
5
6
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT N'Before drop or alter table,you should drop trigger safety!!!!'
ROLLBACK

 

完成限制的DDL触发器之后,我们通过下列语法测试该触发器:

 

1
ALTER TABLE tblAbc ADD c2 INT

 

由于直接被触发器回滚(Rollback),SQL Server将返回错误信息。

 

clip_image003

 

另外,通过DDL触发器搭配EVENTDATA系统函数,经由SQL Server 2005所提供的XQuery语句的Query函数查询,取出想要的数据。再将所有DDL行为记录到另外一个数据表中。

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
USE [AdventureWorks2012]
GO
/****** Object: Table [dbo].[DatabaseLog] Script Date: 2014/12/31 11:33:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DatabaseLog](
[DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
[PostTime] [datetime] NOT NULL,
[DatabaseUser] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[TSQL] [nvarchar](maxNOT NULL,
[XmlEvent] [xml] NOT NULL,
CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED
(
[DatabaseLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for DatabaseLog records.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'DatabaseLogID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The date and time the DDL change occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'PostTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The user who implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'DatabaseUser'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The type of DDL statement that was executed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Event'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The schema to which the changed object belongs.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Schema'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The object that was changed by the DDL statment.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Object'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The exact Transact-SQL statement that was executed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'TSQL'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The raw XML data generated by database trigger.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'XmlEvent'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (nonclustered) constraint' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'CONSTRAINT',@level2name=N'PK_DatabaseLog_DatabaseLogID'
GO

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]''sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]''sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]''sysname')
IF @object IS NOT NULL
PRINT ' ' + @eventType + ' - ' + @schema '.' + @object;
ELSE
PRINT ' ' + @eventType + ' - ' + @schema;
IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
INSERT [dbo].[DatabaseLog]
(
[PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(
GETDATE(),
CONVERT(sysname, CURRENT_USER),
@eventType,
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]''nvarchar(max)'),
@data
);
END;
GO

 

1
2
3
4
CREATE TABLE TestTable(a int);
ALTER TABLE TestTable ADD b nvarchar(10);
DROP TABLE TestTable;
GO

 

1
SELECT FROM dbo.DatabaseLog ORDER BY DatabaseLogID;

 

clip_image004

 

若要删除存放触发器所产生log的数据表dbo.DatabaseLog时,必须先删除使用到这个数据表的触发器。否则会有奇怪的错误信息如下:

 

clip_image005

 

当然,你在删除任何对象前,可以先检查一下对象依赖关系,以确认是否可以删除。

 

创建具有主外键关机的表,来测试利用DDL触发器避免数据表被删除。

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
USE [AdventureWorks2012]
GO
/****** Object: Table [dbo].[Test] Script Date: 2014/12/31 15:10:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
[id] [int] IDENTITY(1,1) NOT NULL,
[DetailID] [intNULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]
ON [PRIMARY]
GO
/****** Object: Table [dbo].[TestDetail] Script Date: 2014/12/31 15:10:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Desc] [varchar](50) NULL,
CONSTRAINT [PK_TestDetail] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]
ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_DetailID] FOREIGN KEY([DetailID])
REFERENCES [dbo].[TestDetail] ([ID])
GO
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_DetailID]
GO

 

搭配EventData系统函数与XQuery语句解析其内容,例如,以VALUE函数判断对象名称,而后决定是否允许用户删除。将safety触发器修改了下,如果用户删除的是dbo.Test数据表,才会出现错误信息。

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ALTER TRIGGER safety
ON DATABASE
FOR DROP_TABLE
AS
DECLARE @data XML=EVENTDATA()
DECLARE @SchemaName nvarchar(max)
DECLARE @TableName nvarchar(max)
SET @SchemaName=EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','sysname')
SET @TableName=EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')
IF @SchemaName='dbo' AND @TableName='Test'
BEGIN
DECLARE @msg NVARCHAR(MAX)=N'You can''t Delete the table: [' + @SchemaName + '].[' + @TableName + ']'
RAISERROR(@msg,16,1)
ROLLBACK TRAN
END

 

正常情况而言,数据表会被FOREIGN KEY条件约束保护,而无法删除,但是FOREIGN KEY约束只保护主数据表,这里对应dbo.TestDetail,而不保护参照数据表,这里对应dbo.Test。

 

clip_image006

 

利用XQuery中的Value,取得SchemaName以及ObjectName,再通过假设句判断是否为所要保护的对象,即可保护目标数据表。此时,若执行如下的语句:

 

1
DROP TABLE dbo.Test

 

将会得到如下的错误信息:

 

clip_image007

 

若要删除数据库级别的DDL触发器,只要参照如下语法即可:

 

1
2
DROP TRIGGER safety ON DATABASE
DROP TABLE dbo.Test

 

在删除DDL触发器时,要搭配ON DATABASE或ON ALL SERVER选项,否则SQL Server会以为要删除的是一般DML触发器,因此,会返回找不到对象的错误信息。

 

查看[ddlDatabaseTriggerLog]触发器记录的信息:

 

1
SELECT FROM dbo.DatabaseLog ORDER BY DatabaseLogID

 

clip_image008

 

最后,显示EVENTDATA函数返回的XML内容如下。

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<EVENT_INSTANCE>
<EventType>DROP_TABLE</EventType>
<PostTime>2014-12-31T15:30:01.010</PostTime>
<SPID>53</SPID>
<ServerName>WIN-LLPKR5BUV6S</ServerName>
<LoginName>WIN-LLPKR5BUV6S\Administrator</LoginName>
<UserName>dbo</UserName>
<DatabaseName>AdventureWorks2012</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Test</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>DROP TABLE dbo.Test</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

 

另外,SQL Server也针对触发器提供查询元数据的方式,以前述创建的DDL触发器为对象,查询语法如下:

 

1
2
3
SELECT FROM sys.triggers WHERE name=’safety’
SELECT definition FROM sys.sql_modules
WHERE object_id=(SELECT object_id FROM sys.triggers WHERE name=’safety’)

 

clip_image009





















本文转自UltraSQL51CTO博客,原文链接: http://blog.51cto.com/ultrasql/1598185,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server——SQL Server触发器及事务和锁
SQL Server——SQL Server触发器及事务和锁
123 0
SQL Server触发器总结
触发器的简介: 触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。 触发器可以查询其他表,而且可以包含复杂的sql语句。它们主要用于强制服从复杂的业务规则或要求。例如:您可以根
98 0
SQL Server触发器
转自:http://www.cnblogs.com/360S/archive/2011/06/10/2078035.html 一﹕ 触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。 二﹕ SQL Server为每个触发器都创建了两个专用表﹕Inserted表和Delet
1208 0
SQL Server触发器
简单介绍:     触发器其实是一种特殊的存储过程,它只有在特定的事件发生时自动执行。存储过程和触发器都是SQL语句和流程控制语句的集合,存储过程通过存储过程的名字被直接调用,而触发器主要通过时间进行触发而被执行。
778 0
SQL Server触发器的基本语法与作用
原文 http://www.cnblogs.com/xiongzhuang/archive/2013/05/13/3076943.html 什么是触发器? 触发器是在对表进行插入、更新或删除操作时自动执行的存储过程 触发器通常用于强制业务规则 触发器是一种高级约束,可以定义比用CHECK 约束更为复杂的约束 :可执行复杂的SQL语句(if/while/case)、可引用其它表中的列 触发器定义在特定的表上,与表相关。
1386 0
C#添加SQL SERVER 2008的触发器
C#可以为SQL SERVER 2008添加触发器,步骤为 在C#中,选择"新建项目"->"数据库"->"SQL Server"->"Visual C# SQL CLR 数据库" 在项目中,单击右键选择"添加"->"触发器",代码如下(ValidateYear.
1074 0
SQL SERVER TRIGGER 触发器
1.触发器简介 触发器是一种特殊的存储过程,它的执行不是由程序调用,也不是手动执行,而是由事件来触发。触发器是当对某一个表进行操作。例如:update、insert、delete这些操作的时候,系统会自动调用执行该表上对应的触发器。
1323 0
SQL Server如何用触发器捕获DML操作的会话信息
需求背景        上周遇到了这样一个需求,维护人员发现一个表的数据经常被修改,由于历史原因;文档缺少;以及维护人员的经常变更,导致他们对系统也业务也不完全熟悉,他们也不完全清楚哪些系统和应用程序会对这个表的数据进行操作。
949 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Sever迁移PG经验
立即下载
SQL Server 2017
立即下载
时序数据库TSDB新功能 - 如何用SQL进行时序查询
立即下载