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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值