34,588
社区成员
发帖
与我相关
我的任务
分享
create trigger tr_delete_t2 on t2
Instead of delete
as
begin
SET NOCOUNT ON;
UPDATE a
SET detail=NULL,isFinish=0
FROM t2 AS a
INNER JOIN deleted AS d ON a.ID=d.ID AND NOT EXISTS(SELECT 1 FROM t2 WHERE ID=a.ID AND iid<a.iid)
DELETE a
FROM t2 AS a
INNER JOIN deleted AS d ON a.ID=d.ID AND EXISTS(SELECT 1 FROM t2 WHERE ID=a.ID AND iid<a.iid)
END
create trigger tr_delete_t2
on t2
Instead of
delete
as
declare @_iid int,@_id int,@_count int
declare curTemp cursor for
select iid,id from deleted
open curTemp
fetch next from curTemp into @_iid,@_id
while @@fetch_status=0
begin
select @_count=count(id) from t2 where id=@_id
if(@_count=1)
begin
update t2 set detail=null,isFinish=0 where iid=@_iid
end
else
begin
delete from t2 where iid=@_iid
end
fetch next from curTemp into @_iid,@_id
end
close curTemp
deallocate curTemp
go--這樣改
create trigger tr_delete_t2 on t2
Instead of delete
as
begin
SET NOCOUNT ON;
UPDATE b
SET detail=NULL,isFinish=0
FROM (SELECT a.id FROM t2 AS a INNER JOIN deleted AS d ON a.ID=d.ID HAVING COUNT(1)=1)as a
INNER JOIN t2 AS b ON a.ID=b.ID
INNER JOIN DELETED ON c ON c.iid=b.iid AND d.ID=b.ID
delete b
FROM (SELECT a.id FROM t2 AS a INNER JOIN deleted AS d ON a.ID=d.ID HAVING COUNT(1)>1)as a
INNER JOIN t2 AS b ON a.ID=b.ID
INNER JOIN DELETED ON c ON c.iid=b.iid AND d.ID=b.ID
END
insert into t1 select * from t3
t3内容
/*
num
---
业务名称
业务名称
业务名称
业务名称
业务名称
业务名称
业务名称
业务名称
*/
insert t1 select '业务名称'
insert t1 select '业务名称'
insert t1 select '业务名称'
insert t1 select '业务名称'
insert t1 select '业务名称'
insert t1 select '业务名称'
insert t1 select '业务名称'
insert t1 select '业务名称'
create trigger tr_insert_t1
on t1
After insert
as
insert into t2 (id,pName,detail,isFinish) select id,pName,null,0 from inserted
declare @_id int,@_pName varchar(10)
select @_id=id,
@_pName=pName
from inserted ---一定要学会运用inserted和deleted临时表