求一个表变更的触发器

Question

table_a
id
name
dept
uidtable_b
id
name
dept
uid
op想在当a表数据发生变动，即增、删、改的情况下，将当条数据插入b表
并且在op字段内插入具体操作(0:增，1：改，2：删)

Answer 1

http://blog.csdn.net/Haiwer/archive/2008/10/04/3016270.aspx

Answer 2

CREATE TRIGGER trAtoB 
ON table_a  
FOR insert,update,delete
AS 
if not exists(select 1 from deleted)--insert
  begin
    insert table_b selelect *,0 from inserted;
  end
else if exists(select 1 from inserted)--delete
  begin
    insert table_b selelect *,2 from deleted;
  end
else --update
  begin
    insert table_b selelect *,1 from inserted;
    --insert table_b selelect *,1 from inserted; ?
  end
    
更新的时候插入table_b表的是更新前的记录还是更新后的记录？

Answer 3

更新后的数据，sorry没说清楚

Answer 4

CREATE TRIGGER trAtoB 
ON table_a  
FOR insert,update,delete
AS 
if not exists(select 1 from deleted) and exists(select 1 from inserted)
  begin
    insert table_b selelect *,0 from inserted;
  end
else if not exists(select 1 from inserted) and exists (select 1 from deleted)
  begin
    insert table_b selelect *,2 from deleted;
  end
else --update
  begin
    insert table_b selelect *,1 from inserted;
    insert table_b selelect *,1 from deleted; 
  end
    

Answer 5

CREATE TRIGGER trAtoB 
ON table_a  
FOR insert,update,delete
AS 
if not exists(select 1 from deleted)--insert
  begin
    insert table_b select *,'增' from inserted;
  end
else if exists(select 1 from inserted)--delete
  begin
    insert table_b select *,'删' from deleted;
  end
else --update
  begin
    insert table_b select *,'改' from inserted;
    insert table_b select *,1 from inserted; ?
  end
return

Answer 6

2楼的和5楼的都没有考虑全面啊。update的时候也会产生deleted表和inserted表的。if not exists(select 1 from deleted)--只有这一个判断条件的时候很难判断是deleted还是updated

Answer 7

create trigger tri on table_a
for insert,update,delete
as
insert table_b
select *,op=case when not exists(select 1 from deleted) then 0
when not exists(select 1 from inserted) then 2
else 1 end
from inserted
go

Answer 8

if object_id('table_a')is not null drop table table_a
go
create table table_a(id int,name varchar(10),dept varchar(10),uid int) 
if object_id('table_b')is not null drop table table_b
go
create table table_b(id int,name varchar(10),dept varchar(10),uid int,op int) 
go
if object_id('tri_test')is not null drop trigger tri_test
go
CREATE TRIGGER  tri_test
ON table_a  
FOR insert,update,delete
AS 
if not exists(select 1 from deleted)--insert
    insert table_b select *,0 from inserted;
else if not  exists(select 1 from inserted)--delete
    insert table_b select *,1 from deleted;
else --update
    insert table_b select *,2 from inserted;
go
insert table_a select 1,'A','dept1',1
update table_a set uid=2 where id=1
delete table_a where id=1
select * from table_b
/*id          name       dept       uid         op          
----------- ---------- ---------- ----------- ----------- 
1           A          dept1      1           0
1           A          dept1      2           2
1           A          dept1      2           1*/

Answer 9

CREATE TRIGGER [TG_TABLE_A_IN] ON [DBO].[TABLE_A]
FOR INSERT
AS 
BEGIN
  SET NOCOUNT ON
  INSERT INTO TABLE_B(ID,NAME,DEPT,UID,OP)
  SELECT ID,NAME,DEPT,UID,0
  FROM INSERTED
SET NOCOUNT OFF
ENDCREATE TRIGGER [TG_TABLE_A_UP] ON [DBO].[TABLE_A]
FOR UPDATE
AS
BEGIN
  SET NOCOUNT ON
  INSERT INTO TABLE_B(ID,NAME,DEPT,UID,OP)
  SELECT ID,NAME,DEPT,UID,1
  FROM INSERTED
SET NOCOUNT OFF
ENDCREATE TRIGGER [TG_TABLE_A_DE] ON [DBO].[TABLE_A]
FOR DELETE
AS 
BEGIN
   SET NOCOUNT ON
   INSERT INTO (ID,NAME,DEPT,UID,OP)
  SELECT ID,NAME,DEPT,UID,2
  FROM INSERTED
SET NOCOUNT OFF
END

Answer 10

先看逻辑1、if not exists(select 1 from deleted)--只有delete和update的时候才存在deleted表，所以此判断要是成立的话那肯定是insert

Answer 11

2楼写错了一个CREATE TRIGGER trAtoB 
ON table_a  
FOR insert,update,delete
AS 
if not exists(select 1 from deleted)--insert
  begin
    insert table_b selelect *,0 from inserted;
  end
else if not exists(select 1 from inserted)--delete
  begin
    insert table_b selelect *,2 from deleted;
  end
else --update
  begin
    insert table_b selelect *,1 from inserted;
  end

Answer 12

update既有inserted也有deleted
所以
没有deleted的一定是insert
没有inserted的一定是delete

Answer 13

create trigger tri_T on table_a
for insert,delete,update
as
begin
....
end

Answer 14

SQL05可用XML生成数据
create table Record(ID bigint identity,TableName nvarchar(128),OperationType nvarchar(100),Data nvarchar(max),OperationDate datetime default(getdate()),Falg bit)--记录操作数据CREATE trigger Tr_Table1_Record on [Table1]  
after insert,update,delete  
as  
declare @TableName nvarchar(128),@OperationType nvarchar(100),@S nvarchar(max)   
set @TableName='Table1'  
if exists(select 1 from inserted) and exists(select 1 from deleted)  
set @OperationType='Update'  else if exists(select 1 from inserted)     
set @OperationType='Insert'  else    set @OperationType='Delete'   ;
with I(Col)  as  
(
select [ID]=replace(rtrim([ID]),'''',''''''),[Col]=replace(rtrim([Col]),'''','''''') from deleted i for xml auto)    
select    
@S=cast(replace(     replace(      Replace(Col,'/><i',''' union all select '+quotename(@TableName,'''')+','
+quotename(@OperationType,'''')+',0,''')        ,'/>','''')         
,'<i','insert Record(TableName,OperationType,Falg,Data) select '++quotename(@TableName,'''')
+','+quotename(@OperationType,'''')+',0,''') as nvarchar(Max))   
from I  
exec(@S)      
;with I2(Col)  as  
(select [ID]=replace(rtrim([ID]),'''',''''''),[Col]=replace(rtrim([Col]),'''','''''') from inserted i for xml auto)  
select    
@S=cast(replace(     replace(      Replace(Col,'/><i',''' union all select '+quotename(@TableName,'''')
+','+quotename(@OperationType,'''')+',1,''')        ,'/>','''')        
,'<i','insert Record(TableName,OperationType,Falg,Data) select '+quotename(@TableName,'''')
+','+quotename(@OperationType,'''')+',1,''') as nvarchar(Max))   
from I2  
exec(@S)

Answer 15

以上脚本没有处理空格<>等特殊字符在xml的显示

Answer 16

楼主的表可以这样写CREATE trigger Tr_table_a_Record on table_a  
after insert,update,delete  
as  
begin
declare @OperationType int,@S nvarchar(max)    if exists(select 1 from inserted) and exists(select 1 from deleted)  
set @OperationType=1 
else if exists(select 1 from inserted)     
set @OperationType=0  
else    
set @OperationType=2;
--更新时把更新前和更新后的数据新增到table_b
insert table_b
select *,op=@OperationType from inserted
union all 
select *,op=@OperationType from deleted
end