sqlserver 觸發器

標籤：

CREATE TRIGGER IC_Deploy_SNP_TRIGGER
ON IC_Deploy
FOR Insert,Update
AS
BEGIN
DECLARE @LocalOrgCode CHAR(3);
DECLARE @OutOrgCode char(3);
DECLARE @InOrgCode char(3);
DECLARE @OrderType SMALLINT;
DECLARE @SPARE2 int;
IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
begin
select @LocalOrgCode=value FROM t_SysParaDic WHERE para=1;
select @OutOrgCode=deployoutstation from inserted;
select @InOrgCode=deployinstation from inserted;
select @OrderType=ordertype from inserted;

IF (@OrderType=3 or @OrderType=4) AND @[email protected]
BEGIN
INSERT INTO IC_Deploy_SNP_ ( type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, INSERTTIME ) select ‘I‘ type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, GETDATE() INSERTTIME from inserted;
END
end
ELSE IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
begin
select @LocalOrgCode=value FROM t_SysParaDic WHERE para=1;
select @OutOrgCode=deployoutstation from inserted;
select @InOrgCode=deployinstation from inserted;
select @OrderType=ordertype from inserted;
select @SPARE2=SPARE2 from inserted;

if update(wasteSN) --更新的欄位是主鍵
begin
--增加刪除記錄
INSERT INTO IC_Deploy_SNP_ ( type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, INSERTTIME ) select ‘D‘ type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, GETDATE() INSERTTIME from deleted;
--增加新增記錄
INSERT INTO IC_Deploy_SNP_ ( type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, INSERTTIME ) select ‘I‘ type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, DATEADD(ss,1,GETDATE()) INSERTTIME from inserted;
end
else --更新的字不是主鍵
begin
IF @SPARE2=1 and (@OrderType=1 OR @OrderType=3) and @[email protected]
BEGIN
INSERT INTO IC_Deploy_SNP_ ( type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, INSERTTIME ) select ‘U‘ type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, GETDATE() INSERTTIME from inserted;
end
ELSE IF @SPARE2=1 and (@OrderType=2 OR @OrderType=4) and @[email protected]
BEGIN
INSERT INTO IC_Deploy_SNP_ ( type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, INSERTTIME ) select ‘U‘ type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, GETDATE() INSERTTIME from inserted;
end
ELSE IF @SPARE2=2 and @OrderType=3 and @[email protected]
BEGIN
INSERT INTO IC_Deploy_SNP_ ( type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, INSERTTIME ) select ‘U‘ type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, GETDATE() INSERTTIME from inserted;
end
ELSE IF @SPARE2=2 and @OrderType=4 and @[email protected]
BEGIN
INSERT INTO IC_Deploy_SNP_ ( type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, INSERTTIME ) select ‘U‘ type, wasteSN,DeployOutCenter,DeployOutStation,DeployOutIP,DeployInCenter,DeployInStation,DeployInIP,PlanCount,OptCount,RealCount,SendOrderTime,SendOrderOper,SendOrderOrg,SendOrderOrgType,OrderStatus,OrderType,ChangeOrderTime,ChangeOrderOper,ChangeOrderOrg,ChangeOrderOrgType,spare1,spare2,spare3, GETDATE() INSERTTIME from inserted;
end

end
end
END
GO

sqlserver 觸發器