create trigger trg_insert on t1 instead of insert as begin --更新已经存在记录的IsDelete字段为1 update a set IsDelete = 1 from T1 a,inserted b where a.Item = b.Item
--插入新记录 insert into T1 select a.* from inserted a end go
create trigger trg_t1_ins instead of insert as begin update t1 set IsDelete=1 where item=(select item from inserted) insert into t1 select * from inserted end
create trigger tri_t1 on t1 for insert as update t1 set isdelete=1 from t1 a,t2 b where a.item=b.item
/* 下面的触发器考虑了多条记录插入的情况,并且考虑了多条记录插入时有的记录存在于t1表中,而有的记录不存在于t1表中 */create trigger tr on t1 instead of insert asdeclare @tb table ( ID int identity, item varchar(20), IsDelete int ) insert @tb(Item,IsDelete) select Item,IsDelete from inserteddeclare @i int select @i=1while @i<=(select max(ID) from @tb) begin if exists(select 1 from t1 where item=(select item from @tb where ID=@i)) begin update t1 set IsDelete=1 where item=(select item from @tb where ID=@i) end else begin insert t1(Item,IsDelete) select Item,IsDelete from @tb where ID=@i end set @i=@i+1 endgo
--建立测试环境:create table t1(item varchar(4),isdelete int) insert t1 values('001',10) insert t1 values('002',11) insert t1 values('003',12)--按要求创建触发器create trigger tt on t1 for insert as if exists(select item from t1 group by item having count(item)>1) begin update t1 set isdelete=1 from inserted a join t1 t on t.item=a.item set rowcount 1 delete from t1 where item in (select item from inserted ) set rowcount 0 end
create trigger ti_t1 on t1 for insert as update t1 set isdelete=1 from t1 a inner join inserted b on a.item=b.item
on t1
instead of insert
as
begin
--更新已经存在记录的IsDelete字段为1
update a set IsDelete = 1 from T1 a,inserted b where a.Item = b.Item
--插入新记录
insert into T1 select a.* from inserted a
end
go
instead of insert
as
begin
update t1 set IsDelete=1 where item=(select item from inserted)
insert into t1 select * from inserted
end
for insert
as
update t1 set isdelete=1 from t1 a,t2 b where a.item=b.item
下面的触发器考虑了多条记录插入的情况,并且考虑了多条记录插入时有的记录存在于t1表中,而有的记录不存在于t1表中
*/create trigger tr
on t1
instead of insert
asdeclare @tb table
(
ID int identity,
item varchar(20),
IsDelete int
)
insert @tb(Item,IsDelete)
select Item,IsDelete from inserteddeclare @i int
select @i=1while @i<=(select max(ID) from @tb)
begin
if exists(select 1
from t1
where item=(select item from @tb where ID=@i))
begin
update t1
set IsDelete=1
where item=(select item from @tb where ID=@i)
end
else
begin
insert t1(Item,IsDelete)
select Item,IsDelete from @tb where ID=@i
end set @i=@i+1
endgo
insert t1 values('001',10)
insert t1 values('002',11)
insert t1 values('003',12)--按要求创建触发器create trigger tt
on t1
for insert
as
if exists(select item from t1 group by item having count(item)>1)
begin
update t1 set isdelete=1 from inserted a join t1 t on t.item=a.item
set rowcount 1
delete from t1 where item in (select item from inserted )
set rowcount 0
end
for insert
as
update t1 set isdelete=1 from t1 a inner join inserted b on a.item=b.item