更改域账号时其他用到域账号的表同步更新,主要记住Inserted b,Deleted c 即可,Inserted 为更改后的表,Deleted 为更改前的表。
USE [EIP2]
GO/****** Object: Trigger [dbo].[eTR_Employee_UPT] Script Date: 2015/8/21 16:17:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[eTR_Employee_UPT]
On [dbo].[eEmployee]
For Update
As
Begin
Set nocount on
If Update(ADAccount)
Begin
---报表权限
Update a Set a.ADAccount=b.ADAccount From otRole_Emp a,Inserted b Where a.Badge=b.Badge
---费控配置表
Update a Set a.Account=b.ADAccount
From tCD_BugAgent a,Inserted b,Deleted c
Where a.Account=c.ADAccount And b.EID=c.EID
Update a Set a.Approver=b.ADAccount
From bCD_BudgetUnit a,Inserted b,Deleted c
Where a.Approver=c.ADAccount And b.EID=c.EID
Update a Set a.Approver=b.ADAccount
From cCD_BudgetApprovalNode a,Inserted b,Deleted c
Where a.Approver=c.ADAccount And b.EID=c.EID
---流程表
Update a Set a.CurrentApprovalUser=b.ADAccount
From dbo.ct_TravelExpendBaseInfo a,Inserted b,Deleted c
Where a.CurrentApprovalUser=c.ADAccount And b.EID=c.EID
Update a Set a.Requestor=b.ADAccount
From dbo.ct_TravelExpendBaseInfo a,Inserted b,Deleted c
Where a.Requestor=c.ADAccount And b.EID=c.EID
Update a Set a.Operator=b.ADAccount
From dbo.ct_TravelExpendBaseInfo a,Inserted b,Deleted c
Where a.Operator=c.ADAccount And b.EID=c.EID
Update a Set a.CustomerApprover=b.ADAccount
From dbo.ct_TravelExpendBaseInfo a,Inserted b,Deleted c
Where a.CustomerApprover=c.ADAccount And b.EID=c.EID
Update a Set a.PeopleAD=b.ADAccount
From dbo.ct_TravelExpendStayDetail a,Inserted b,Deleted c
Where a.PeopleAD=c.ADAccount And b.EID=c.EID
Update a Set a.BusinessUserAD=b.ADAccount
From dbo.ct_CommonBaoXiao a,Inserted b,Deleted c
Where a.BusinessUserAD=c.ADAccount And b.EID=c.EID
Update a Set a.CustomUserAD=b.ADAccount
From dbo.ct_CommonBaoXiao a,Inserted b,Deleted c
Where a.CustomUserAD=c.ADAccount And b.EID=c.EID
Update a Set a.BusinessUserAD=b.ADAccount
From dbo.ct_GoodsEnterTainDetail a,Inserted b,Deleted c
Where a.BusinessUserAD=c.ADAccount And b.EID=c.EID
Update a Set a.CustomUserAD=b.ADAccount
From dbo.ct_GoodsEnterTainDetail a,Inserted b,Deleted c
Where a.CustomUserAD=c.ADAccount And b.EID=c.EID
--modify by 卜庆龙
--2015-08-21
--更改域账号时修改任务系统关联表
--预计bug,字段存放多账号时可能无法修改。
--月度计划主体
update a Set a.Originator=b.ADAccount
From dbo.ttMonthlyPlan a,Inserted b,Deleted c
Where a.Originator=c.ADAccount
update a Set a.Approver=b.ADAccount
From dbo.ttMonthlyPlan a,Inserted b,Deleted c
Where a.Approver=c.ADAccount
update a Set a.DutyMan=b.ADAccount
From dbo.ttMonthlyPlan a,Inserted b,Deleted c
Where a.DutyMan=c.ADAccount
--月度计划详细
Update a Set a.[User]=b.ADAccount
From dbo.ttMonthlyplanDetails a,Inserted b,Deleted c
Where a.[User]=c.ADAccount
Update a Set a.InspectUser=b.ADAccount
From dbo.ttMonthlyplanDetails a,Inserted b,Deleted c
Where a.InspectUser=c.ADAccount
Update a Set a.RateUser=b.ADAccount
From dbo.ttMonthlyplanDetails a,Inserted b,Deleted c
Where a.RateUser=c.ADAccount
--专项计划
Update a Set a.Originator=b.ADAccount
From dbo.ttTaskPlan a,Inserted b,Deleted c
Where a.Originator=c.ADAccount
Update a Set a.Approver=b.ADAccount
From dbo.ttTaskPlan a,Inserted b,Deleted c
Where a.Approver=c.ADAccount
Update a Set a.DutyMan=b.ADAccount
From dbo.ttTaskPlan a,Inserted b,Deleted c
Where a.DutyMan=c.ADAccount
--专项计划明细
Update a Set a.[User]=b.ADAccount
From dbo.ttTaskPlanDetail a,Inserted b,Deleted c
Where a.[User]=c.ADAccount
Update a Set a.HelpUser=b.ADAccount
From dbo.ttTaskPlanDetail a,Inserted b,Deleted c
Where a.HelpUser=c.ADAccount
Update a Set a.InspectUser=b.ADAccount
From dbo.ttTaskPlanDetail a,Inserted b,Deleted c
Where a.InspectUser=c.ADAccount
--协作任务
Update a Set a.Originator=b.ADAccount
From dbo.ttTaskJoin a,Inserted b,Deleted c
Where a.Originator=c.ADAccount
Update a Set a.Approver=b.ADAccount
From dbo.ttTaskJoin a,Inserted b,Deleted c
Where a.Approver=c.ADAccount
Update a Set a.DutyMan=b.ADAccount
From dbo.ttTaskJoin a,Inserted b,Deleted c
Where a.DutyMan=c.ADAccount
--协作任务明细
Update a Set a.Recipient=b.ADAccount
From dbo.ttTaskJoinDetails a,Inserted b,Deleted c
Where a.Recipient=c.ADAccount
Update a Set a.SendUser=b.ADAccount
From dbo.ttTaskJoinDetails a,Inserted b,Deleted c
Where a.SendUser=c.ADAccount
--工作安排
Update a Set a.Originator=b.ADAccount
From dbo.ttTaskWorks a,Inserted b,Deleted c
Where a.Originator=c.ADAccount
Update a Set a.Recipient=b.ADAccount
From dbo.ttTaskWorks a,Inserted b,Deleted c
Where a.Recipient=c.ADAccount
Update a Set a.RateUser=b.ADAccount
From dbo.ttTaskWorks a,Inserted b,Deleted c
Where a.RateUser=c.ADAccount
End
End