向SQL表添加触发器 [英] Adding trigger to sql tables

查看：91 发布时间：2021/4/28 20:38:25 sql sql-server sql-server-2008 stored-procedures triggers

本文介绍了向SQL表添加触发器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试在数据库中为sql表创建触发器，以跟踪任何表和任何字段的任何插入，更新和删除，这个主题对我来说是新的，我在网上搜索了，发现了一篇对我很有帮助的文章这样做，但是当我运行此查询时，我收到了几条错误消息.

I am trying to create a trigger for sql table in my database to track any insert, update and delete of any table and any fields, this subject was new to me, I searched on net and i found a very helpful article to do this, but when I run this query i got several error message.

消息8197，级别16，状态4，过程BahbyGrade_ChangeTracking，第3行对象"BahbyGrade"不存在或对该操作无效.

Msg 8197, Level 16, State 4, Procedure BahbyGrade_ChangeTracking, Line 3 The object 'BahbyGrade' does not exist or is invalid for this operation.

信息311，级别16，状态1，过程dtproperties_ChangeTracking，第69行不能在已插入"和已删除"表中使用text，ntext或image列.

Msg 311, Level 16, State 1, Procedure dtproperties_ChangeTracking, Line 69 Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

消息311，级别16，状态1，过程dtproperties_ChangeTracking，第71行不能在已插入"和已删除"表中使用text，ntext或image列.

Msg 311, Level 16, State 1, Procedure dtproperties_ChangeTracking, Line 71 Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

信息311，级别16，状态1，过程OutSide_ChangeTracking，第69行不能在已插入"和已删除"表中使用text，ntext或image列.

Msg 311, Level 16, State 1, Procedure OutSide_ChangeTracking, Line 69 Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

Msg 311，级别16，状态1，过程OutSide_ChangeTracking，第71行不能在已插入"和已删除"表中使用text，ntext或image列.

Msg 311, Level 16, State 1, Procedure OutSide_ChangeTracking, Line 71 Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

第8行，状态16，状态4，程序人员New_ChangeTracking，消息8197对象"PersonnelNew"不存在或对该操作无效.

Msg 8197, Level 16, State 4, Procedure PersonnelNew_ChangeTracking, Line 3 The object 'PersonnelNew' does not exist or is invalid for this operation.

USE pr1
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')

CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1), 
TableName varchar(128), 
PrimaryKeyField varchar(1000), 
PrimaryKeyValue varchar(1000), 
FieldName varchar(128), 
OldValue varchar(1000), 
NewValue varchar(1000), 
UpdateDate datetime DEFAULT (GetDate()), 
UserName varchar(128)
)
GO

DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON;
SELECT @TABLE_NAME = MIN(TABLE_NAME) 
FROM INFORMATION_SCHEMA.Tables 
WHERE 
TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
WHILE @TABLE_NAME IS NOT NULL

BEGIN

EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')

SELECT @sql = 

create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete

as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000), 
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)

select @TableName = ''' + @TABLE_NAME+ '''

-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)

-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''

-- get list of columns
select * into #ins from inserted
select * into #del from deleted

-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + '''''''' 
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,    
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c   
where  pk.TABLE_NAME = @TableName   
and CONSTRAINT_TYPE = ''PRIMARY KEY''   
and c.TABLE_NAME = pk.TABLE_NAME   
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 

if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin

select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field

select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')

begin

select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field

select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''

select @sql = @sql + '' select '''''' + @Type + ''''''''

select @sql = @sql + '','''''' + @TableName + ''''''''

select @sql = @sql + '','' + @PKFieldSelect

select @sql = @sql + '','' + @PKValueSelect

select @sql = @sql + '','''''' + @fieldname + ''''''''

select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''

select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''

select @sql = @sql + '','''''' + @UpdateDate + ''''''''

select @sql = @sql + '','''''' + @UserName + ''''''''

select @sql = @sql + '' from #ins i full outer join #del d''

select @sql = @sql + @PKCols

select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname 

select @sql = @sql + '' or (i.'' + @fieldname + '' is null and  d.'' + @fieldname + '' is not null)'' 

select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and  d.'' + @fieldname + '' is null)'' 

exec (@sql)

end

end

SELECT @sql

EXEC(@sql)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables 
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
END

我如何跳过某些专栏?

http://weblogs.asp.net/jongalloway/添加基于简单触发器的审核到您的sql-server-database

推荐答案

几天前，我遇到了类似的问题.我修改了最初在您上面发布的网站上找到的版本.

A couple days ago I was having a similar issue. I modified the version originally found on the website you posted above.

下面的代码使您可以包含具有text，ntext或image列的表.但是，有几件事要牢记.

The code below allows you to include tables that have text, ntext or image columns. however there are a couple of things to keep in mind.

由于mssql的工作原理，您无法引用text，ntext或image列，但"INSTEAD OF"触发器除外.为了解决这个问题，我仅检查更新中是否包括这些禁止的列类型之一.我不知道它是否实际上发生了变化，还是捕获了它的旧值.因此，只要其中任何一列包含在更新中，我都认为它已更改.
表将需要更新其触发器.否则您在将任何列更改为text，ntext或image数据类型时，表也将需要更新其触发器.

due to how mssql works you cannot reference text, ntext, or image columns except in "INSTEAD OF" triggers. to get around this i only check if one of these forbidden column types was included in the update. i can't tell whether it actually changed, or capture its old value. so any time one of these columns are included in the update, i assume it has changed.
tables with text, ntext or image columns will need their triggers updated if you make any schema changes. or a table will also need its trigger updated it you change any columns to or from text, ntext or image data type.

但是，如果您使用的是2008年或更高版本，则可能建议使用 SQL内置的在变更跟踪中

However, If you have 2008 or later It is probably recommended to use SQL's built in change tracking

--Author: Rickac
--Date: 2015.03.23
--purpose: track changes to a database
--this has been modified but was originally based on http://weblogs.asp.net/jongalloway/adding-simple-trigger-based-auditing-to-your-sql-server-database
--note: this cannot properly detect changes to text,ntext or image columns.  it will track any update that includes one of these columns even if no change was made.
--      tables with text, ntext or image columns will need their trigger updated if a column is added to or removed from that table or in cases where a data type changes either to or from some other type to a text, ntext, or image type.

USE MY_DB -- TODO change this to your DB
GO

--hold some global script variables.
create table #vars (name sysname, value varchar(8000))
insert #vars values ('AUDIT_TABLE_NAME','Audit') -- this is the name you want the audit table to be.
insert #vars values ('TRIGGER_SUFFIX','_ChangeTracking') -- this is used for naming the trigger

Declare @sql varchar(max),@AUDIT_TABLE_NAME sysname
--retrieve global variable
select @AUDIT_TABLE_NAME=value from #vars where name='AUDIT_TABLE_NAME'

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= @AUDIT_TABLE_NAME)
set @sql='CREATE TABLE '+ @AUDIT_TABLE_NAME +'
(
AuditID int IDENTITY(1,1) NOT NULL,
Type char(1),
TableName varchar(128),
PrimaryKeyField varchar(1000),
PrimaryKeyValue varchar(1000),
FieldName varchar(128),
OldValue varchar(1000),
NewValue varchar(1000),
UpdateDate datetime DEFAULT (GetDate()),
UserName varchar(128),
query varchar(4000)
)'
exec(@sql)
GO

DECLARE @sql varchar(max), @TABLE_NAME sysname, @AUDIT_TABLE_NAME sysname, @TRIGGER_SUFFIX varchar(255)
SET NOCOUNT ON
--retrieve global variable
select @AUDIT_TABLE_NAME=value from #vars where name='AUDIT_TABLE_NAME'
select @TRIGGER_SUFFIX=value from #vars where name='TRIGGER_SUFFIX'


DECLARE table_cursor CURSOR FOR 
  SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.Tables
    WHERE
      TABLE_TYPE= 'BASE TABLE'
      AND TABLE_NAME not in ('sysdiagrams',@AUDIT_TABLE_NAME)
      --use the below clause if you only want a specific table or list of tables
      -- AND TABLE_NAME in ('mytable')
    ORDER BY TABLE_NAME

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
print 'adding '+@TABLE_NAME+ @TRIGGER_SUFFIX 
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ @TRIGGER_SUFFIX +''', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ @TRIGGER_SUFFIX)
set @sql =
'
create trigger ' + @TABLE_NAME+ @TRIGGER_SUFFIX +' on [' + @TABLE_NAME+ '] for insert, update, delete
as
set nocount on

declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(MAX),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000),
@changes int


select @TableName = ''' + @TABLE_NAME+ '''
-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''
-- get list of columns
'
declare @contains_forbidden_columns int
set @contains_forbidden_columns=0
if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TABLE_NAME and DATA_TYPE in ('text','ntext','image'))
BEGIN
--darn, they have a text, ntext, or image column that means our job just got difficult
--lets try to list the columns individually.
set @contains_forbidden_columns=1

--get the primary keys so we can run a join
DECLARE @PKCols varchar(1000), @ColumnList varchar(max)
--reset our vars
set @ColumnList=NULL
set @PKCols=NULL

-- Get primary key columns for join
select @PKCols = coalesce(@PKCols + ' and', '') + ' t.[' + c.COLUMN_NAME + '] = c.[' + c.COLUMN_NAME +']'
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TABLE_NAME
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

--get the column list (excluding any text,ntext, or image columns)
select @ColumnList = coalesce(@ColumnList + ',', '') + ' c.['+ c.COLUMN_NAME +']'
 from INFORMATION_SCHEMA.COLUMNS c where TABLE_NAME = @TABLE_NAME and DATA_TYPE not in ('text','ntext','image')

select @ColumnList = coalesce(@ColumnList + ',', '') + ' t.['+ c.COLUMN_NAME +']'
 from INFORMATION_SCHEMA.COLUMNS c where TABLE_NAME = @TABLE_NAME and DATA_TYPE in ('text','ntext','image')

set @sql=@sql+
'
select '+@ColumnList+' into #ins from ['+@TABLE_NAME+'] t join inserted c on ('+@PKCols+')
select '+@ColumnList+' into #del from ['+@TABLE_NAME+'] t join deleted c on ('+@PKCols+')
'

END
ELSE
BEGIN

set @sql=@sql+
'
select * into #ins from inserted
select * into #del from deleted
'

END

set @sql=@sql+
'
CREATE TABLE #inputbuffer 
 (
  EventType nvarchar(30), 
  Parameters int, 
  EventInfo nvarchar(4000)
 )

 INSERT INTO #inputbuffer EXEC (''DBCC INPUTBUFFER(''+@@SPID+'') with NO_INFOMSGS'')

-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.['' + c.COLUMN_NAME + ''] = d.['' + c.COLUMN_NAME +'']''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.['' + COLUMN_NAME + ''],d.['' + COLUMN_NAME + '']))''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = ''insert '+@AUDIT_TABLE_NAME+' (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName, query)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' + @fieldname + ''''''''
select @sql = @sql + '',convert(varchar(1000),d.['' + @fieldname + ''])''
select @sql = @sql + '',convert(varchar(1000),i.['' + @fieldname + ''])''
select @sql = @sql + '','''''' + @UpdateDate + ''''''''
select @sql = @sql + '','''''' + @UserName + ''''''''
select @sql = @sql + '',(SELECT top 1 EventInfo FROM #inputbuffer)''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
'
declare @where varchar(1000)
set @where=
'
  select @sql = @sql + '' where ''
  select @sql = @sql + ''i.['' + @fieldname + ''] <> d.['' + @fieldname + '']''
  select @sql = @sql + '' or (i.['' + @fieldname + ''] is null and d.['' + @fieldname + ''] is not null)''
  select @sql = @sql + '' or (i.['' + @fieldname + ''] is not null and d.['' + @fieldname + ''] is null)''
'

--does this table contain forbidden columns?
if @contains_forbidden_columns>0
BEGIN
--yes this table contains forbidden columns so we need to adjust the trigger accordingly and detect if this particular column is a forbidden type
set @sql=@sql+
'
-- see if this column contains a forbidden value
if not exists(select ORDINAL_POSITION from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '''+@TABLE_NAME+''' and DATA_TYPE in (''text'',''ntext'',''image'') and sys.fn_IsBitSetInBitmask(COLUMNS_UPDATED(),ORDINAL_POSITION)!=0 and COLUMN_NAME=@fieldname)
BEGIN
  -- it does not contain a forbidden value so add the where clause for a clean column
'
+@where+
'
END
'
END
ELSE
BEGIN
--this table does not contain forbidden columns
set @sql=@sql+@where

END

set @sql=@sql+
'
exec (@sql)
end
end
'

SELECT @TABLE_NAME as 'table',@sql as 'trigger'
EXEC(@sql)

FETCH NEXT FROM table_cursor INTO @TABLE_NAME

END 

--******************************************
--BEGIN cleanup
CLOSE table_cursor;
DEALLOCATE table_cursor;

IF OBJECT_ID('tempdb..#vars') IS NOT NULL
BEGIN
  Drop table #vars
END
--END cleanup
--******************************************

这篇关于向SQL表添加触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

向SQL表添加触发器 [英] Adding trigger to sql tables

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

向SQL表添加触发器 [英] Adding trigger to sql tables

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭