问题是这样的:系统后台管理时,如果部门状态更新那么他子部门的状态也更新,他下面的人员也更新,子部门人员也更新,并且同时更新他的账户信息。
我是同过触发器实现的,具体代码如下--:更新部门状态字段触发器:
--:Log Begin:
-- 2010.10.17 zz 创建此触发器
--:Log End:
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' AND NAME = 'ROSS_PLF_DEPARTMENT_UPDATE' )
DROP TRIGGER ROSS_PLF_DEPARTMENT_UPDATE
GO
CREATE TRIGGER ROSS_PLF_DEPARTMENT_UPDATE
ON ROSS_PLF_DEPARTMENT
FOR UPDATE
AS
IF UPDATE(IS_VALID)
BEGIN
DECLARE @DEPARTMENT_ID INT,@IS_VALID INT
SELECT @DEPARTMENT_ID = DEPARTMENT_ID,@IS_VALID = IS_VALID FROM INSERTED
--更新子部门状态
UPDATE ROSS_PLF_DEPARTMENT SET IS_VALID = @IS_VALID WHERE PARENT_DEPARTMENT_ID = @DEPARTMENT_ID
--更新部门人员状态
UPDATE ROSS_PLF_EMPLOYEE SET IS_VALID = @IS_VALID WHERE PARENT_DEPARTMENT_ID = @DEPARTMENT_ID
END
--:更新员工状态字段触发器:
--:Log Begin:
-- 2010.10.17 zz 创建此触发器
--:Log End:
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' AND NAME = 'ROSS_PLF_EMPLOYEE_UPDATE' )
DROP TRIGGER ROSS_PLF_EMPLOYEE_UPDATE
GO
CREATE TRIGGER ROSS_PLF_EMPLOYEE_UPDATE
ON ROSS_PLF_EMPLOYEE
FOR UPDATE
AS
IF UPDATE(IS_VALID)
BEGIN
DECLARE @EMPLOYEE_ID INT,@IS_VALID INT
SELECT @EMPLOYEE_ID = EMPLOYEE_ID,@IS_VALID = IS_VALID FROM INSERTED
--更新人员账户
UPDATE ROSS_PLF_USER SET IS_VALID = @IS_VALID WHERE EMPLOYEE_ID = @EMPLOYEE_ID
END按照触发器原理来讲这样做就会实现上述功能但是
我发现更新更新一个部门他的子部门是更新了但是他的子部门下的人员没有更新,账户子部门人员的账户也没有更新,
难道触发器内部更新不能触发触发器,希望各位高手指点迷津。
我是同过触发器实现的,具体代码如下--:更新部门状态字段触发器:
--:Log Begin:
-- 2010.10.17 zz 创建此触发器
--:Log End:
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' AND NAME = 'ROSS_PLF_DEPARTMENT_UPDATE' )
DROP TRIGGER ROSS_PLF_DEPARTMENT_UPDATE
GO
CREATE TRIGGER ROSS_PLF_DEPARTMENT_UPDATE
ON ROSS_PLF_DEPARTMENT
FOR UPDATE
AS
IF UPDATE(IS_VALID)
BEGIN
DECLARE @DEPARTMENT_ID INT,@IS_VALID INT
SELECT @DEPARTMENT_ID = DEPARTMENT_ID,@IS_VALID = IS_VALID FROM INSERTED
--更新子部门状态
UPDATE ROSS_PLF_DEPARTMENT SET IS_VALID = @IS_VALID WHERE PARENT_DEPARTMENT_ID = @DEPARTMENT_ID
--更新部门人员状态
UPDATE ROSS_PLF_EMPLOYEE SET IS_VALID = @IS_VALID WHERE PARENT_DEPARTMENT_ID = @DEPARTMENT_ID
END
--:更新员工状态字段触发器:
--:Log Begin:
-- 2010.10.17 zz 创建此触发器
--:Log End:
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' AND NAME = 'ROSS_PLF_EMPLOYEE_UPDATE' )
DROP TRIGGER ROSS_PLF_EMPLOYEE_UPDATE
GO
CREATE TRIGGER ROSS_PLF_EMPLOYEE_UPDATE
ON ROSS_PLF_EMPLOYEE
FOR UPDATE
AS
IF UPDATE(IS_VALID)
BEGIN
DECLARE @EMPLOYEE_ID INT,@IS_VALID INT
SELECT @EMPLOYEE_ID = EMPLOYEE_ID,@IS_VALID = IS_VALID FROM INSERTED
--更新人员账户
UPDATE ROSS_PLF_USER SET IS_VALID = @IS_VALID WHERE EMPLOYEE_ID = @EMPLOYEE_ID
END按照触发器原理来讲这样做就会实现上述功能但是
我发现更新更新一个部门他的子部门是更新了但是他的子部门下的人员没有更新,账户子部门人员的账户也没有更新,
难道触发器内部更新不能触发触发器,希望各位高手指点迷津。
--:Log Begin:
-- 2010.10.17 zz 创建此触发器
--:Log End:
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' AND NAME = 'ROSS_PLF_DEPARTMENT_UPDATE' )
DROP TRIGGER ROSS_PLF_DEPARTMENT_UPDATE
GO
CREATE TRIGGER ROSS_PLF_DEPARTMENT_UPDATE
ON ROSS_PLF_DEPARTMENT
FOR UPDATE
AS
IF UPDATE(IS_VALID)
BEGIN
--更新子部门状态
update a SET a.IS_VALID = b.IS_VALID from ROSS_PLF_DEPARTMENT a inner join INSERTED b on a.PARENT_DEPARTMENT_ID = b.DEPARTMENT_ID
--更新部门人员状态
update a SET a.IS_VALID = b.IS_VALID from ROSS_PLF_EMPLOYEE a inner join INSERTED b on a.PARENT_DEPARTMENT_ID = b.DEPARTMENT_ID
END--:更新员工状态字段触发器:
--:Log Begin:
-- 2010.10.17 zz 创建此触发器
--:Log End:
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' AND NAME = 'ROSS_PLF_EMPLOYEE_UPDATE' )
DROP TRIGGER ROSS_PLF_EMPLOYEE_UPDATE
GO
CREATE TRIGGER ROSS_PLF_EMPLOYEE_UPDATE
ON ROSS_PLF_EMPLOYEE
FOR UPDATE
AS
IF UPDATE(IS_VALID)
BEGIN
--更新人员账户
UPDATE a SET a.IS_VALID = b.IS_VALID from ROSS_PLF_USER inner join INSERTED b on a.EMPLOYEE_ID = b.EMPLOYEE_ID
END
你测试了吗?把测试写上看看结果
update a SET a.IS_VALID = b.IS_VALID from ROSS_PLF_EMPLOYEE a inner join INSERTED b on a.PARENT_DEPARTMENT_ID = b.DEPARTMENT_ID
是不是你的员工表里没有PARENT_DEPARTMENT_ID字段啊???
--:更新部门状态字段触发器 & 更新员工状态字段触发器
--:Log Begin:
-- 2010.10.17 zz 创建此触发器
--:Log End:
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' AND NAME = 'ROSS_PLF_DEPARTMENT_UPDATE' )
DROP TRIGGER ROSS_PLF_DEPARTMENT_UPDATE
GO
CREATE TRIGGER ROSS_PLF_DEPARTMENT_UPDATE
ON ROSS_PLF_DEPARTMENT
FOR UPDATE
AS
SET NOCOUNT ON
if update(IS_VALID)
begin
declare @layer int = 0
select *, layer=@layer into # from inserted
while @@rowcount>0
begin
set @layer = @layer + 1
insert # select a.*, @layer from ROSS_PLF_DEPARTMENT a join # on a.PARENT_DEPARTMENT_ID = #.DEPARTMENT_ID and #.layer=@layer-1
end
--更新子部门状态
update a set a.IS_VALID = #.IS_VALID from ROSS_PLF_DEPARTMENT a join # on a.DEPARTMENT_ID = #.DEPARTMENT_ID and #.layer>0
--更新部门人员状态
update a set a.IS_VALID = #.IS_VALID from ROSS_PLF_EMPLOYEE a join # on a.DEPARTMENT_ID=#.DEPARTMENT_ID
--更新人员账户
update a set a.IS_VALID = #.IS_VALID from ROSS_PLF_USER a join # on a.DEPARTMENT_ID=b.DEPARTMENT_ID
end
SET NOCOUNT OFF
GO