sql触发器

触发器——添加语句如下：

ALTER TRIGGER [dbo].[Sync_ProjectTriggerAdd]
ON [dbo].[File_Project_Test]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @sach int;
select @sach=Synch from inserted;
if(@sach=1 )
begin
-- 将WRP库中新增的项目数据插入到Middle库中
INSERT INTO welding_CFE_Middle.dbo.File_Project_Test (IID,ProjectName,Parent_iid,ProjectNameDic,CustomerName,TwoDFileName,TwoDFileUrl,ThreeDFileName,ThreeDFileUrl,Synch)
SELECT iid,pjt_no,parent_iid,ProjectNameDic,CustomerName,TwoDFileName,TwoDFileUrl,ThreeDFileName,ThreeDFileUrl,1
FROM inserted;
end
END;

ALTER TRIGGER [dbo].[Sync_ProjectTriggerEdit]
ON [dbo].[File_Project_Test]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

-- 如果更新了 Synch 字段并且值为 1，则执行更新或添加操作
IF EXISTS(SELECT * FROM inserted WHERE Synch = 1)
BEGIN
MERGE INTO welding_CFE_Middle.dbo.[File_Project] AS target
USING inserted AS source
ON target.IID = source.iid
WHEN MATCHED THEN
UPDATE SET
target.ProjectName = source.pjt_name,
target.Parent_iid = source.parent_iid,
target.ProjectNameDic = source.ProjectNameDic,
target.CustomerName = source.CustomerName,
target.TwoDFileName = source.TwoDFileName,
target.TwoDFileUrl = source.TwoDFileUrl,
target.ThreeDFileName = source.ThreeDFileName,
target.ThreeDFileUrl = source.ThreeDFileUrl
WHEN NOT MATCHED BY TARGET THEN
INSERT (IID, ProjectName, Parent_iid, ProjectNameDic, CustomerName, TwoDFileName, TwoDFileUrl, ThreeDFileName, ThreeDFileUrl)
VALUES (source.iid, source.pjt_name, source.parent_iid, source.ProjectNameDic, source.CustomerName, source.TwoDFileName, source.TwoDFileUrl, source.ThreeDFileName, source.ThreeDFileUrl);
END
END