/* 利用触发器禁止修改一些特殊的数据表 */
CREATE TRIGGER Table_Monitor ON DATABASE
FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
Declare @S Varchar(800)
Set @S=EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘,‘nvarchar(max)‘)
Print @S
Create Table dbo.Test2(ID Int Not Null) --创建表
Go
Alter Table dbo.Test2 Add Name Varchar(20) --添加字段
Go
Alter Table dbo.Test2 Alter Column Name Varchar(40) --修改字段
Go
Alter Table dbo.Test2 Add Constraint PK_Test2 Primary Key (ID) --添加主键
Go
Alter Table dbo.Test2 Drop Column Name --删除字段
Go
Drop Table dbo.Test2 --删除测试表
--监控表，若名为表，若名为表，若名为Test2Test2Test2Test2Test2、Test3Test3Test3Test3Test3，
--则取消操作则取消操作则取消操作
Create TRIGGER Table_Monitor_Deny ONDATABASE
FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
Declare @S Varchar(800)
Set @S=EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘,‘nvarchar(max)‘)
Set @S=SubString(@S,CharIndex(‘Table‘,@S)+6,200) --截取关键词Table后面的字符串，以准备取表名
If CharIndex(‘ ‘,@S)>0
Set @S=SubString(@S,1,CharIndex(‘ ‘,@S)-1) --截取表名，若表名含有空格，则会误判
If CharIndex(‘.‘,@S)>0 --若含. 通常是方案名, 如dbo.Test2
Set @S=SubString(@S,CharIndex(‘.‘,@S)+1,200)
Set @S=Replace(@S,‘[‘,‘‘) --表名可能以[]界定表示，如dbo.[Test2]
Set @S=Replace(@S,‘]‘,‘‘)
If CharIndex(@S,‘Test2,Test3,‘)>0 --若为Test2、Test3，则取消操作
Begin
Set @S=‘表：‘+@S+‘ 禁止任何DDL 操作‘
RaisError(@S,10,1)
Rollback
End
--创建后，再创建表名为Test2（或已有表Test2，对其修改、删除表结构），就会返回错误
--删除、删除DDLDDL 触发器触发器
Drop Trigger Table_Monitor On Database
Drop Trigger Table_Monitor_Deny OnDatabase
--创建服务器级DDLDDL 触发器，此处以登录为例触发器，此处以登录为例触发器
Use Master
Go
--创建日志表
Create Table dbo.SysLog(_Date DateTime,Remark Varchar(800))
Go
--创建测试登录帐号
Create Login LoginTest With Password=‘qwe123!@#‘
--创建测试用户
Create User LoginUser For Login LoginTest
--授予插入权限，否则以LoginTest登录时会报错
Grant Insert To LoginUser
Go
Create TRIGGER Logon_Monitor ON All SERVER
FOR LOGON
AS
Set NoCount On
Declare @S Varchar(800)
Select @S=‘登录时间：‘+Convert(Char(19),Login_Time,121)+‘ 电脑名：‘+Host_Name
+‘ 登录名：‘+Login_Name+‘ SPID：‘+Cast(@@SPID as Varchar(10))
From Sys.dm_exec_sessions Where session_id=@@SPID
Insert Master.dbo.SysLog(_Date,Remark) Values (GetDate(),@S)
--开启SSMS以SQL登录帐号LoginTest、sa或Windows管理员帐号登录SQL SERVER，再执行查询验证效果：
Select _Date,Remark From dbo.SysLog order By _Date Desc
--删除服务器级删除服务器级删除服务器级DDLDDL 触发器
Drop Trigger LogOn_Monitor On All Server
本文出自 “畅想天空” 博客，请务必保留此出处http://kinwar.blog.51cto.com/3723399/1549072