一 .server trigger的简单介绍
在sql server数据库中，server trigger 是一种特殊类型的存储过程，它可以对特定表、视图或存储中的必然事件自动响应，不由用户调用。创建触发器时对其进行定义，以便在对特定的数据库对象作特定类型的修改时执行，根据触发器定义的动作做出反应。
其主要被用在保持数据库对象的完整性方面。例如，防止数据库中已建好的表和存储过程被更改或删除。此外还可以 进行更改历史记录的追踪，查看表或存储被修改的记录。
server trigger比database trigger所管控的范围更广，可以管控server下的所有database的对象。
二. 主要表 及创建脚本
表protected_objects ，主要用来存储被保护的数据库对象，例如 表和存储过程。 字段 activeflag设置为y时有效，n是无效。
有新的数据库对象创建，最自动insert一笔数据。
create table [dbo].[protected_objects](
[serverip] [varchar](100) null,
[servername] [varchar](100) null,
[dbname] [varchar](100) null,
[objname] [varchar](100) null,
[objtype] [varchar](100) null,
[creator] [varchar](100) null,
[activeflag] [varchar](10) null,
[transdatetime] [datetime] null
) on [primary]
go
表dbtrigger_log，主要存储数据库对象变动记录。
create table [dbo].[dbtrigger_log](
[serverip] [varchar](20) null,
[servername] [varchar](50) null,
[dbname] [varchar](100) null,
[objectname] [varchar](100) null,
[objecttype] [varchar](100) null,
[eventtype] [varchar](100) null,
[hostname] [varchar](128) null,
[appname] [varchar](128) null,
[eventdata] [xml] null,
[transdatetime] [datetime] null,
[flag] [int] null default ((0))
) on [primary] textimage_on [primary]
go
set ansi_padding off
go
exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'服务器ip' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'dbtrigger_log', @level2type=n'column',@level2name=n'serverip'
go
exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'服务器名称' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'dbtrigger_log', @level2type=n'column',@level2name=n'servername'
go
exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'数据库名称' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'dbtrigger_log', @level2type=n'column',@level2name=n'dbname'
go
exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'对象名称' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'dbtrigger_log', @level2type=n'column',@level2name=n'objectname'
go
exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'对象类型' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'dbtrigger_log', @level2type=n'column',@level2name=n'objecttype'
go
exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'事件类型' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'dbtrigger_log', @level2type=n'column',@level2name=n'eventtype'
go
exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'终端机器名' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'dbtrigger_log', @level2type=n'column',@level2name=n'hostname'
go
exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'名称' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'dbtrigger_log', @level2type=n'column',@level2name=n'appname'
go
exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'触发事件xml' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'dbtrigger_log', @level2type=n'column',@level2name=n'eventdata'
go
exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'发生时间' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'dbtrigger_log', @level2type=n'column',@level2name=n'transdatetime'
go
exec sys.sp_addextendedproperty @name=n'ms_description', @value=n'是否上传' , @level0type=n'schema',@level0name=n'dbo', @level1type=n'table',@level1name=n'dbtrigger_log', @level2type=n'column',@level2name=n'flag'
go
三. 创建server trigger的脚本
use [master]
go
/****** object: ddltrigger [serverdbtrigger_protectobjects] script date: 2018/12/27 13:36:00 ******/
set ansi_nulls on
go
set quoted_identifier on
go
/*###########################################################################################
*program*: <db trigger>
*description*: <protect sql key objects>
*programer*: <>
*date*: 2015-12-03
---0001 2015-12-03 11:12 第一阶段期间只保留修改记录，暂时不阻止（不rollback）
---0002 2015-12-03 15:32 增加发邮件的功能.
---0003 2015-12-04 14:20 出现set ansi_padding off后，还有（如果是script出来的表，其中有索引约束等，
--- 需要alter表时，就会报错。）代码时，@xevent.query会报错。
##############################################################################################*/
create trigger [serverdbtrigger_dba_protectobjects]
on all server
for drop_table,drop_procedure,drop_view,drop_function,
create_table,create_procedure,create_view,create_function,
alter_procedure,alter_view,alter_table,alter_function,rename
as
set nocount on ;
begin try
declare @serverip varchar(20)
declare @servername varchar(50)
declare @appname nvarchar(128)
declare @hostname nvarchar(128)
declare @dbname varchar(100)
declare @objectname varchar(100)
declare @objecttype varchar(100)
declare @eventtype varchar(100)
declare @objectaction varchar(100)
declare @xevent xml
set @xevent = eventdata()
----------------------0003 start ----------
--set @dbname=convert(varchar(100),@xevent.query('data(/event_instance/databasename)'))
--set @objectname=convert(varchar(100),@xevent.query('data(/event_instance/objectname)'))
--set @objecttype=convert(varchar(100),@xevent.query('data(/event_instance/objecttype)'))
--set @objectaction=convert(varchar(100),@xevent.query('data(/event_instance/eventtype)'))
declare @eventdata varchar(max)
select @eventdata=convert(varchar(max),@xevent)
set @dbname= substring (@eventdata, charindex('<databasename>',@eventdata)+14, charindex('</databasename>',@eventdata)-charindex('<databasename>',@eventdata)-14)
set @objectname= substring (@eventdata, charindex('<objectname>',@eventdata)+12, charindex('</objectname>',@eventdata)-charindex('<objectname>',@eventdata)-12)
set @objecttype= substring (@eventdata, charindex('<objecttype>',@eventdata)+12, charindex('</objecttype>',@eventdata)-charindex('<objecttype>',@eventdata)-12)
set @objectaction= substring (@eventdata, charindex('<eventtype>',@eventdata)+11, charindex('</eventtype>',@eventdata)-charindex('<eventtype>',@eventdata)-11)
set @eventtype=substring(@eventdata, charindex('<eventtype>',@eventdata)+11, charindex('</eventtype>',@eventdata)-charindex('<eventtype>',@eventdata)-11)
------------0003 end ---------------
select @hostname=host_name(),@appname=app_name()
select @servername = @@servername
select @serverip = min(local_net_address) from sys.dm_exec_connections where local_net_address is not null
if exists(select top 1 objname from protected_objects with(nolock) where servername = @@servername and dbname=@dbname and objname=@objectname and activeflag='y')
begin
if (@objectname like 'tmp%')or (/*@objectaction like 'alter%' and */@objectname like '[_]%')
begin
insert into dbtrigger_log
( serverip ,servername ,dbname ,objectname ,objecttype,eventtype ,hostname , appname ,[eventdata] ,transdatetime)
values ( @serverip ,@servername , @dbname , @objectname ,@objecttype ,@eventtype,@hostname ,@appname ,@eventdata , getdate())
end
else
begin
-----------------0001 start ---
insert into dbtrigger_log
( serverip ,servername ,dbname ,objectname ,objecttype,eventtype ,hostname , appname ,[eventdata] ,transdatetime)
values ( @serverip ,@servername , @dbname , @objectname ,@objecttype ,@eventtype,@hostname ,@appname ,@eventdata , getdate())
--rollback transaction
-----------------end --------
-------------0002 begin ----------------
declare @subject as nvarchar(200)
declare @body as nvarchar(max)
declare @spname as nvarchar(max)
set @subject = 'serverdbtrigger-重要!;serverip:' + @serverip
set @spname = ''
set @body = '<html><body>dear all,<br> <br> servername:' + @servername+ ' ; serverip:' + @serverip+ '上的object已被改动,请及时检查!!!
<br> you can get detail information from dba_dbtrigger_log. <br><br><table border=1 bgcolor=#aaff11>'
set @body = @body+ '<tr bgcolor=#ffaa11><td>servername</td><td>serverip</td><td>dbname</td><td>eventtype</td><td>objectname</td><td>objecttype</td><td>hostname </td><td>transdatetime</td></tr>'
select @spname = @spname + '<tr bgcolor=#ffaa11><td>'+ cast(@servername as nvarchar(50))+ '</td><td>'+ cast(@serverip as nvarchar(50))+ '</td><td>' + cast(@dbname as nvarchar(50)) + '</td><td>'+ cast(@eventtype as nvarchar(50))+ '</td><td>'+cast(@objectname as nvarchar(50))+ '</td><td>'+ cast(@objecttype as nvarchar(20))+ '</td><td>'+ substring(replace(cast(@hostname as varchar(500)), char(0), ''), 1, 500)+ '</td><td>'+ convert(varchar(100), getdate(), 21)+ '</td></tr>'
set @body = @body + @spname + '</table>'
set @body=replace(@body,'''','')
if replace(@body,' ','')<>''
begin
declare @allemailtoaddress varchar(3000)=''
declare @allemailccaddress varchar(3000)=''
declare @allprofile_name varchar(100)=''
select @allemailtoaddress='收件人的地址'
select @allemailccaddress='抄送人的地址'
select top 1 @allprofile_name=name from msdb.dbo.sysmail_profile order by profile_id
exec msdb..sp_send_dbmail @profile_name = @allprofile_name -- profile 名称
,@recipients = @allemailtoaddress -- 收件人邮箱
,@copy_recipients=@allemailccaddress
,@subject = @subject -- 邮件标题
,@body = @body -- 邮件内容
,@body_format = 'html' -- 邮件格式
,@file_attachments=''
end
------------- 0002 end ------------
end
end
----新建对象自动塞入保护表
else
begin
--print 3
delete from protected_objects where servername = @@servername and dbname=@dbname and objname=@objectname
if @objectaction like 'create%' and @appname like '%microsoft sql server management studio%'
and @objectname not like '[_]%' and @objectname not like 'tmp%'
begin
insert into protected_objects
values ( @serverip, @servername, @dbname, @objectname,@objecttype, @hostname, 'y', getdate() )
insert into dbtrigger_log
( serverip ,servername ,dbname ,objectname ,objecttype,eventtype ,hostname , appname ,[eventdata] ,transdatetime)
values ( @serverip ,@servername , @dbname , @objectname ,@objecttype ,@eventtype,@hostname ,@appname ,@eventdata , getdate())
end
else if @objectaction not like 'create%' and @appname like '%microsoft sql server management studio%' and @objectname not like '[_]%'
begin
insert into dbtrigger_log
( serverip ,servername ,dbname ,objectname ,objecttype,eventtype ,hostname , appname ,[eventdata] ,transdatetime)
values ( @serverip ,@servername , @dbname , @objectname ,@objecttype ,@eventtype,@hostname ,@appname ,@eventdata , getdate())
end
end
end try
begin catch
print '@objectname:'+@objectname
print '@objecttype:'+@objecttype
print error_message()
rollback transaction
end catch
set ansi_nulls off
go
set ansi_nulls off
go
set quoted_identifier off
go
enable trigger [serverdbtrigger_protectobjects] on all server
go
四. 补充
创建server trigger 后，此时表protected_objects是空的，没有被保护的数据库对象。我们可以将数据库下面的对象批量插入。例如，我们将数据库xxxx下除 _和unuse开头之外的所有对象批量插入。其脚本如下：
insert into protected_objects(dbname,objname,objtype,creator,activeflag,transdatetime)
select 'xxxxxx',name, case xtype when 'u' then 'table' when 'p' then 'procedure' when 'fn' then 'function' when 'tf' then 'function' when 'v' then 'view' end,
host_name(),'y' ,convert(varchar(10),dateadd(day,-46,getdate()),120)
from [xxxxxxx].dbo.sysobjects where xtype in ('u','p','fn','v','tf')
and name not like '[_]%'
and name not like 'unuse%'