SQL触发器简单用法

更改域账号时其他用到域账号的表同步更新，主要记住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