CREATE TRIGGER ONmrDepDelete ON [dbo].[mrDep] FOR DELETE AS update mrDep set HiDepID=aa.DepID from deleted aa where aa.DepID=mrDep.HiDepID 发生了:( 操作失败!子查询返回的值多于一个。当子查询跟随在 =、!=、<、、>= 之后,或子查询用作表达式时,这种情况是不允许的。 语句已终止。
insert into mrdep(name) values('第1个')
insert into mrdep(HiDepID,name) values(1,'第1-1个')
insert into mrdep(HiDepID,name) values(1,'第1-2个')
insert into mrdep(HiDepID,name) values(1,'第1-3个')
insert into mrdep(HiDepID,name) values(2,'第1-1-1个')
insert into mrdep(HiDepID,name) values(2,'第1-1-2个')
insert into mrdep(HiDepID,name) values(2,'第1-1-3个')
go
--创建删除触发器
CREATE TRIGGER ONmrDepDelete ON [dbo].[mrDep]
FOR DELETE
AS
update mrDep set HiDepID=aa.HiDepID
from mrdep,deleted aa where aa.DepID=mrDep.HiDepID
go
--创建更新触发器
CREATE TRIGGER onMrDepUpdate ON dbo.mrDep
FOR UPDATE
AS
declare @oldid smallint,@newid smallint
--declare #aa cursor for
select id=identity(int,1,1),cast(DepID as smallint) as DepID into #temp1 from inserted
select id=identity(int,1,1),cast(DepID as smallint) as DepID into #temp2 from deleted
select a.depid as ndepid,b.depid as odepid
into #temp
from #temp1 a,#temp2 b
where a.id=b.id
drop table #temp1,#temp2
Update mrDep set HiDepID=b.nDepID
from mrDep a,#temp b
where a.HiDepID=b.odepid
drop table #temp
应该是这样修改的.
CREATE TRIGGER onMrDepUpdate ON dbo.mrDep
FOR UPDATE
AS
declare @oldid smallint,@newid smallint
--declare #aa cursor for
select id=identity(int,1,1),cast(DepID as smallint) as DepID into #temp1 from inserted
select id=identity(int,1,1),cast(DepID as smallint) as DepID into #temp2 from deleted
select a.depid as ndepid,b.depid as odepid
into #temp
from #temp1 a,#temp2 b
where a.id=b.id
drop table #temp1,#temp2
Update mrDep set HiDepID=b.nDepID
from mrDep a,#temp b
where a.HiDepID=b.odepid
drop table #temp
测试过,是这个影响
另外我这个表中还有一个触发器为:
CREATE TRIGGER onMrDepUpdate ON dbo.mrDep
FOR UPDATE
AS
Update mrDep set HiDepID=inserted.DepID from inserted where mrDep.HiDepID=(select DepID from deleted)
另外我这个表中还有一个触发器为:
CREATE TRIGGER onMrDepUpdate ON dbo.mrDep
FOR UPDATE
AS
Update mrDep set HiDepID=inserted.DepID from inserted where mrDep.HiDepID=(select DepID from deleted)
insert into mrdep(name) values('第1个')
insert into mrdep(HiDepID,name) values(1,'第1-1个')
insert into mrdep(HiDepID,name) values(1,'第1-2个')
insert into mrdep(HiDepID,name) values(1,'第1-3个')
insert into mrdep(HiDepID,name) values(2,'第1-1-1个')
insert into mrdep(HiDepID,name) values(2,'第1-1-2个')
insert into mrdep(HiDepID,name) values(2,'第1-1-3个')
go
--创建删除触发器
CREATE TRIGGER ONmrDepDelete ON [dbo].[mrDep]
FOR DELETE
AS
update mrDep set HiDepID=aa.HiDepID
from mrdep,deleted aa where aa.DepID=mrDep.HiDepID