已插入知道新记录的SQL Server 2005 [英] Sql server 2005 knowing new record is inserted

问题描述

有没有办法找出是否插入新记录而不是在sql server 2005中写入触发器。（它可以是tsql或java ...）
由于供应商不允许右触发在那里DB ...
另外如何使用新插入行的数据生成文件..是bcp一个很好的解决方案??

Is there any way to find out whether a new record is inserted other than writing trigger in sql server 2005. (it can be tsql or by java...) Since the vendor is not allowing to right trigger on there DB... Also how to generate a file with data of newly inserted row.. is bcp a good solution??

推荐答案

您可以定义一个审计表，然后将OUTPUT命令添加到INPUT或UPDATE语句（DELETE也是，顺便说一句）。

You can define an audit table and then add an OUTPUT command to your INPUT or UPDATE statements (DELETE's too, btw).

create table fab4 (id int identity(1,1),FName varchar(80));
create table fab4_audit (act varchar(10),id int, FName_old varchar(80), FName_new varchar(80), ts datetime not null default(getdate()));

-- insert
insert into fab4(FName)
output
'INSERTED'
, inserted.*
into fab4_audit([act],id,FName_new)
select 'John' union all
select 'Paul' union all
select 'George' union all
select 'Ringo';
go

-- update
update f
set FName='Walrus'
output
'UPDATED'
, inserted.id
, deleted.FName
, inserted.FName
into fab4_audit([act],id,FName_old,FName_new)
from fab4 f
where FName='Paul';

-- delete
delete f
output
'DELETED'
, deleted.id
, deleted.FName
into fab4_audit([act],id,FName_old)
from fab4 f
where FName in ('John','George');
go

select * from fab4;
select * from fab4_audit;
go

这篇关于已插入知道新记录的SQL Server 2005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！