Mikel
来源： 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
