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)--The updated field is the primary key
Begin
--Increase deletion record
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;
--Add new records
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--The updated word is not a primary key
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
SQL Server triggers