日期:2014-05-17 浏览次数:20629 次
create trigger trig_001 on dbo.表名 for insert,update,delete as begin insert into 日志表 select * From inserted insert into 日志表 select * From deleted end
------解决方案--------------------
--try简单的例子 create table subject ( col1 int ) create table t_record ( action_id numeric(10) identity(1,1), action_time datetime, action varchar(10) ) --触发器 create trigger trig_subject on subject for insert,update,delete as begin declare @inserted int declare @deleted int select @inserted = count(*) from inserted select @deleted = count(*) from deleted if @inserted > 0 and @deleted > 0 begin insert into t_record values(getdate(),'update') end else if @inserted > 0 and @deleted = 0 begin insert into t_record values(getdate(),'insert') end else if @inserted = 0 and @deleted > 0 begin insert into t_record values(getdate(),'delete') end end --测试 insert into subject values(1) 结果如下 /* 1 2009-06-04 16:36:03.530 insert */ --结果如下 update subject set col1 = 2 where col1=1 /* 1 2009-06-04 16:36:03.530 insert 2 2009-06-04 16:36:22.390 update */ --结果如下 delete subject where col1 = 2 /* 1 2009-06-04 16:36:03.530 insert 2 2009-06-04 16:36:22.390 update 3 2009-06-04 16:36:40.080 delete */
------解决方案--------------------
CREATE TRIGGER tt ON tb FOR INSERT, UPDATE, DELETE AS BEGIN INSERT INTO 日志表 SELECT *, 'old' FROM DELETED INSERT INTO 日志表 SELECT *, 'new' FROM INSERTED END