sqlserver触发器insert,delete,update - Coder_小菜 - 博客园

baacloud免费翻墙vpn注册使用

来源: sqlserver触发器insert,delete,update – Coder_小菜 – 博客园


-- =============================================
-- Author: 王伟
-- Create date: 2017-07-06
-- Description: 增、删、改开放时间表时,同步数据至考勤安排表
-- =============================================
CREATE TRIGGER [dbo].[TR_LM_SYS_KaiFangShiJian_IDU]
ON [dbo].[LM_SYS_KaiFangShiJian]
FOR INSERT,DELETE,UPDATE
AS
BEGIN
--新增
if(exists(select 1 from inserted) and not exists(select 1 from deleted))
begin
INSERT INTO LM_KQ_KaoQingAnPai
( KaoQingAnPaiID ,
ShiYanShiID ,
KaiShiRiQi ,
JieShuRiQi ,

XingQingBianHao ,
KaiShiShiJian ,
JieShuShiJian ,
SystemType ,

CreateOn ,
CreateDate ,
UpdateOn ,
UpdateDate
)
SELECT CONVERT(VARCHAR(100), A.KaiFangShiJianID) AS KaiFangShiJianID ,
CONVERT(VARCHAR(100), A.ShiYanShiID) AS ShiYanShiID ,
A.KaiShiRiQi ,
A.JieShuRiQi ,

A.XingQingBianHao ,
A.KaiShiShiJian ,
A.JieShuShiJian ,
AS SystemType ,

NULL ,
GETDATE() ,
NULL ,
GETDATE()
FROM Inserted AS A
INNER JOIN LM_SYS_ShiYanShiXinXi AS B ON A.ShiYanShiID = B.ShiYanShiID
INNER JOIN LM_MJ_Men AS C ON B.MenID = C.MenID
WHERE A.ShiFouMoRen = 0
AND B.IsDelete = 0
AND C.IsDelete = 0;
end

–删除
if(not exists(select 1 from inserted) and exists(select 1 from deleted))
begin
delete from LM_KQ_KaoQingAnPai
where KaoQingAnPaiID in(select KaiFangShiJianID from deleted)
end

–更新
if(exists(select 1 from inserted) and exists(select 1 from deleted))
begin
update LM_KQ_KaoQingAnPai set ShiYanShiID=a.KaiFangShiJianID
,KaiShiRiQi=a.KaiShiRiQi,JieShuRiQi=a.JieShuRiQi
,XingQingBianHao=a.XingQingBianHao,KaiShiShiJian=a.KaiShiShiJian
,JieShuShiJian=a.JieShuShiJian,UpdateDate=GETDATE()
from inserted a
where KaoQingAnPaiID=a.KaiFangShiJianID
end

END

GO

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏