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
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
*/
FOR INSERT, UPDATE, DELETE
AS
BEGIN INSERT INTO 日志表 SELECT *, 'old' FROM DELETED
INSERT INTO 日志表 SELECT *, 'new' FROM INSERTED
END