CREATE TRIGGER ONmrDepDelete ON [mrDep]
FOR DELETE
AS
update mrDep set HiDepID=aa.DepID from (select HiDepID,sum(DepID) DepID from deleted group by HiDepID) aa where aa.DepID=mrDep.HiDepID
FOR DELETE
AS
update mrDep set HiDepID=aa.DepID from (select HiDepID,sum(DepID) DepID from deleted group by HiDepID) aa where aa.DepID=mrDep.HiDepID
你的语句可能写错了.
update mrDep set HiDepID=aa.DepID from deleted aa where aa.DepID=mrDep.HiDepID 既然aa.DepID=mrDep.HiDepID ,何必还要set HiDepID=aa.DepID ?
我的表是一个部门表(mrDep),是无限分级的,当删除一个上级部门(DepID)时和这个表的上级字段(HiDepID)为这个表时就进行更新,更表为删除部门的上一级部门(HiDepID)
FOR DELETE
ASupdate mrDep set HiDepID=aa.DepID
from mrdep,deleted aa where aa.DepID=mrDep.HiDepID
[DepID] [smallint] IDENTITY (1, 1) NOT NULL ,(这是部门ID)
[HiDepID] [smallint] NULL CONSTRAINT [DF_mrDep_HiDepID] DEFAULT (0),(这是上级部门) [Layer] [tinyint] NOT NULL CONSTRAINT [DF_mrDep_Layer] DEFAULT (0),
[Name] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Manager] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[District] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[Street] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[PostCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Status] [bit] NOT NULL CONSTRAINT [DF_mrDep_Status] DEFAULT (1),
CONSTRAINT [PK_mrDep] PRIMARY KEY CLUSTERED
(
[DepID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TRIGGER ONmrDepDelete ON [dbo].[mrDep]
FOR DELETE
ASupdate mrDep set HiDepID=aa.HiDepID
from mrdep,deleted aa where aa.DepID=mrDep.HiDepID
CREATE TABLE [mrDep] (
[DepID] [smallint] IDENTITY (1, 1) NOT NULL ,
[HiDepID] [smallint] NULL CONSTRAINT [DF_mrDep_HiDepID] DEFAULT (0),
[Name] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL)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
ASupdate mrDep set HiDepID=aa.HiDepID
from mrdep,deleted aa where aa.DepID=mrDep.HiDepID go--显示当前数据
select * from mrdep--删除
delete from mrdep where depid=2--查看删除后的结果
select * from mrdep--删除测试环境
drop table mrdep
执行删除前的内容
DepID HiDepID Name
------ ------- --------------------------------
1 0 第1个
2 1 第1-1个
3 1 第1-2个
4 1 第1-3个
5 2 第1-1-1个
6 2 第1-1-2个
7 2 第1-1-3个删除后的结果.depid为 5,6,7的记录,上级编码已经替换为1啦.
DepID HiDepID Name
------ ------- --------------------------------
1 0 第1个
3 1 第1-2个
4 1 第1-3个
5 1 第1-1-1个
6 1 第1-1-2个
7 1 第1-1-3个
你那里错误提示什么?触发器中还有其他内容吗?
CREATE TRIGGER ONmrDepDelete ON [dbo].[mrDep]
FOR DELETE
AS
update mrDep set HiDepID=aa.HiDepID
from mrdep,deleted aa where aa.DepID=mrDep.HiDepID操作失败!子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。另外我这个表中还有一个触发器为:
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)
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 deletedselect a.depid as ndepid,b.depid as odepid
into #temp
from #temp1 a,#temp2 b
where a.id=b.id
drop table #temp1,#temp2Update mrDep set HiDepID=b.nDepID
from mrDep a,#temp b
where a.HiDepID=b.odepid
drop table #temp
CREATE TABLE [mrDep] (
[DepID] [smallint] IDENTITY (1, 1) NOT NULL ,
[HiDepID] [smallint] NULL CONSTRAINT [DF_mrDep_HiDepID] DEFAULT (0),
[Name] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL)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
ASupdate 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 deletedselect a.depid as ndepid,b.depid as odepid
into #temp
from #temp1 a,#temp2 b
where a.id=b.id
drop table #temp1,#temp2Update mrDep set HiDepID=b.nDepID
from mrDep a,#temp b
where a.HiDepID=b.odepid
drop table #tempgo--显示当前数据
select * from mrdep--删除
delete from mrdep where depid=2--查看删除后的结果
select * from mrdep--删除测试环境
drop table mrdep