sql server 2005触发器有关问题,还请指教

sql server 2005触发器问题,还请各位高手指教
当a表的字段a1,a2,a3被修改或该条记录被删除时,把修改或删除之前的该条记录拷贝到另外一个历史表中

历史表的结果和a表完全一样

求这样的触发器如何编写

------解决方案--------------------
类似这样

SQL code

CREATE TRIGGER trg_history 
ON table1
for UPDATE, delete
as
begin
      if update(a1) or update(a2) or update(a3) 
           begin 
                INSERT INTO history_bak(....)  --列跟原表一致即可。
                SELECT * FROM DELETED 
           end 
  
        IF EXISTS(SELECT 1 FROM DELETED ) AND NOT EXISTS(SELECT 1 FROM INSERTED) 
            BEGIN
                INSERT INTO history_bak(....)  --列跟原表一致即可。
                SELECT * FROM DELETED 
            END
        
end

------解决方案--------------------

SQL code


if OBJECT_ID('tri_test') is not null
drop trigger tri_test
go
create trigger tri_test on tb
for update,delete
as
if (exists(select 1 from deleted)
    and exists(select 1 from inserted))--如果是修改
   or(
   exists(select 1 from deleted)
    and not exists(select 1 from inserted)--如果是删除
   )
begin
insert 历史表
select a1,a2,a3 from deleted
end
go

sql server 2005触发器有关问题,还请指教

相关推荐