sql-server - 我可以为所有表循环创建触发器吗?
问题描述
我有这样的脚本,理论上允许我为数据库中的每个表创建相同的触发器:
DECLARE @insertTriggers VARCHAR(MAX) = '';
SELECT @insertTriggers = @insertTriggers1 + *Some Trigger creation code ending with 'GO'"
FROM INFORMATION_SCHEMA.TABLES
EXEC(@insertTriggers);
当我使用 PRINT 并将其复制到不同的查询时,它可以工作,因为我可以使用关键字 GO 单独创建触发器。在这里它不起作用。我能做些什么?我必须为每个触发器制作单独的代码吗?
解决方案
根据我的评论,我为审计目的开发了这个 SP,它最初可能不适合你,但如果你改变它,我相信它会帮助你。
否则,改变你需要触发器做什么的问题,然后我会改变我的答案为你工作......
if exists(select 1 from sysobjects where name = 'kv_sp_dbAudits_tr_AW')
drop procedure kv_sp_dbAudits_tr_AW
go
set ansi_nulls on
go
set quoted_identifier on
go
create procedure kv_sp_dbAudits_tr_AW(@tablename varchar(150),@switch bit)
as
BEGIN
if exists(select name from sysobjects where name = 'TempTableTriggers')
drop table TempTableTriggers;
create table TempTableTriggers (tablename varchar(100),columns varchar(max),columnvars varchar(max),columnvarst varchar(max),columnalloc varchar(max))
declare @query varchar(max)
, @loop int
, @dbname varchar(50) = (select db_name())+'_Audit'
, @table varchar(100)
, @tableau varchar(100)
, @trigger varchar(100)
, @column varchar(max)
, @columnvars varchar(max)
, @columnvarst varchar(max)
, @columnalloc varchar(max)
, @crlf char(2) = char(13)+char(10)
declare @Tables table
( id int identity primary key
, TableName varchar(100)
, TriggerName varchar(100)
, Columns varchar(max))
if @tablename like ''+'%'+''
set @tablename = @tablename
else
set @tablename = ''+@tablename+''
set @trigger = @tablename+'_Audit_kvtr_AW'
begin
if @switch = 1
begin
if exists(select name from sysobjects where name = @trigger)
begin
set @tablename = (select name from sysobjects where name = @tablename)
set @query = @crlf
set @query = @query + @crlf
set @query = @query + @crlf
set @query = @query + @crlf
set @query = @query + replicate('-',80)+@crlf
set @query = @query + @crlf
set @query = @query + 'The audit trigger "'+@tablename+'_Audit_kvtr_AW'+'" already exists!'
print (@query)
end
else
begin
if @tablename = 'all'
insert into @Tables
( TableName )
select
name
from sysobjects
where xtype = 'u'
and name not like '%audit%'
and name <> 'TempTableTriggers'
order by name
else
insert into @Tables
( TableName )
select
name
from sysobjects
where xtype = 'u'
and name not like '%audit%'
and name <> 'TempTableTriggers'
and name = @tablename
order by name
select @loop = min(id) from @Tables
while @loop is not null
begin
begin
set @query = ' declare @columns varchar(max)
select @columns = stuff((select '','' + char(10)+quotename(Column_Name)
from information_schema.columns
where table_name = '''+(select TableName from @Tables where id = @loop)+'''
and Column_Name <> ''cAllocs''
and data_type not in (''text'',''image'',''ntext'')
group by column_name, data_type, character_maximum_length, ordinal_position
order by ordinal_position
for xml path(''''), type).value(''.'', ''nvarchar(max)''),1,1,'''')
insert into TempTableTriggers (tablename,columns)
select distinct
table_name
, @columns
from information_schema.columns
where table_name = '''+(select TableName from @Tables where id = @loop)+''''
exec (@query)
end
select @loop = min(id) from @Tables where id>@loop
end
insert into @Tables
(TriggerName,TableName,Columns)
select
TableName,TableName,columns
from TempTableTriggers
select @loop = min(id) from @Tables
while @loop is not null
begin
begin
select @trigger = TriggerName+'_Audit_kvtr_AW'
, @tableau = TableName+'_Audit'
, @table = TableName
, @column = Columns
from @Tables
where id = @loop
set @query = 'create trigger '+@trigger+' on '+@table+@crlf
set @query = @query+ 'with encryption'+@crlf
set @query = @query+ 'after insert, update, delete'+@crlf
set @query = @query+ 'as'+@crlf
set @query = @query+ '/***********************************************************************************************************************************'+@crlf
set @query = @query+ 'Description : To insert any change made in '+@table+' into '+@dbname+'.dbo.'+@table+'_Audit'+@crlf
set @query = @query+ 'Author : Attie Wagner'+@crlf
set @query = @query+ 'Creation Date : 30 October 2018'+@crlf
set @query = @query+ 'Modified By : Attie Wagner'+@crlf
set @query = @query+ 'Modified Date : 28 January 2019'+@crlf
set @query = @query+ '************************************************************************************************************************************/'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'begin'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'set nocount on'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'if (select trigger_nestlevel(object_id('''+@trigger+'''))) > 1'+@crlf
set @query = @query+ 'return'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'declare @inserted varchar(15) = '''''+@crlf
set @query = @query+ 'declare @deleted varchar(15) = '''''+@crlf
set @query = @query+ 'declare @updated varchar(15) = '''''+@crlf
set @query = @query+ 'declare @action varchar(15) = '''''+@crlf
set @query = @query+ 'if((exists(select * from inserted)) and (exists(select * from deleted)))'+@crlf
set @query = @query+ ' set @updated = ''updated'''+@crlf
set @query = @query+ 'else'+@crlf
set @query = @query+ 'if(exists(select * from inserted))'+@crlf
set @query = @query+ ' set @inserted = ''new'''+@crlf
set @query = @query+ 'else'+@crlf
set @query = @query+ 'if(exists(select * from deleted))'+@crlf
set @query = @query+ ' set @deleted = ''deleted'''+@crlf
set @query = @query+ 'set @action = (select case when @inserted = ''new'' then ''new'' when @updated = ''updated'' then ''updated'' when @deleted = ''deleted'' then ''deleted'' else '''' end)'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'declare @kvAgent varchar(100) = (select cAgentName from _rtblAgents where idAgents = [dbo]._efnAgentIDFromConnection(''dbo''))'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'if @action in (''new'',''updated'')'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'begin'+@crlf
set @query = @query+ 'insert into ['+@dbname+'].dbo.'+@tableau+' ('+@column+','+@crlf+'kvUsername,'+@crlf+'kvDTStamp,'+@crlf+'kvAction)'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'select '+@column+','+@crlf+'@kvAgent,'+@crlf+'getdate(),'+@crlf+'@action'+@crlf
set @query = @query+ 'from inserted'+@crlf
set @query = @query+ 'end;'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'if @action = ''deleted'''+@crlf
set @query = @query+ @crlf
set @query = @query+ 'begin'+@crlf
set @query = @query+ 'insert into ['+@dbname+'].dbo.'+@tableau+' ('+@column+','+@crlf+'kvUsername,'+@crlf+'kvDTStamp,'+@crlf+'kvAction)'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'select '+@column+','+@crlf+'@kvAgent,'+@crlf+'getdate(),'+@crlf+'@action'+@crlf
set @query = @query+ 'from deleted'+@crlf
set @query = @query+ 'end;'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'begin'+@crlf
set @query = @query+ 'delete '+quotename(@dbname)+'.dbo.'+@table+'_Audit'+@crlf
set @query = @query+ 'where datediff(month,kvDTStamp,getdate()) > 12'+@crlf
set @query = @query+ 'end;'+@crlf
set @query = @query+ @crlf
set @query = @query+ 'end;'+@crlf
exec (@query)
end
select @loop = min(id) from @Tables where id>@loop
end
end
drop table TempTableTriggers;
end
else
begin
if @tablename = 'all'
insert into @Tables
( TriggerName )
select name from sysobjects
where xtype = 'tr'
and name like '%_Audit_kvtr_AW'
order by name
else
insert into @Tables
( TriggerName )
select name from sysobjects
where xtype = 'tr'
and name like '%_Audit_kvtr_AW'
and name = @tablename+'_Audit_kvtr_AW'
order by name
select @loop = min(id) from @Tables
while @loop is not null
begin
begin
set @trigger = (select TriggerName from @Tables where id = @loop)
set @query = 'if exists(select name from sysobjects where name = '''+@trigger+''')'+@crlf
set @query = @query+'drop trigger '+@trigger
exec (@query)
end
select @loop = min(id) from @Tables where id>@loop
end
end
end
END;
推荐阅读
- css - 使用 css 网格的垂直和水平居中的 4x4 方形网格
- git - 为什么没有自动生成master分支?
- angular - 对于自定义表单组件,是否可以使用 DefaultValueAccessor 而不是 ControlValueAccessor?
- c# - 如何在不使用 asp.net mvc 中的模型类的情况下从视图下拉列表中获取数据到控制器
- vuejs2 - Vue 组件 prop 更改不会触发重新渲染
- html - 我的徽标不适合导航栏
- coldfusion - writedump 显示的不仅仅是结构上的循环
- git - 了解 Git:捆绑与克隆
- chapel - 使用类类型作为教堂中的字段
- row - SnappyData 每行 TTL