sqlserver 触发器

 

 

 

/**

  触发器  ，当对表  有 插入 和修改操作时，会触发 该方法，做相应

   操作

*/CREATE TRIGGER IC_Deploy_SNP_TRIGGER ON IC_Deploy     //  为 表 IC_deploy 创建触发器 FOR Insert,UpdateASBEGIN

//声明 变量  ，获取 插入 记录的字段；或者修改 的字段 DECLARE @LocalOrgCode CHAR(3); DECLARE @OutOrgCode char(3); DECLARE @InOrgCode char(3); DECLARE @OrderType SMALLINT; DECLARE @SPARE2 int;

//如果 存在 插入 操作（inserted 和 deleted 是两个触发表，临时存放 变化数据），不是删除操作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 @LocalOrgCode=@OutOrgCode

//判断 不同值后 ，同时 插入另一张表 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 endELSE 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 --相应 操作 end else --更新的字不是主键 begin IF @SPARE2=1 and (@OrderType=1 OR @OrderType=3) and @LocalOrgCode=@OutOrgCode BEGIN end ELSE IF @SPARE2=1 and (@OrderType=2 OR @OrderType=4) and @LocalOrgCode=@InOrgCode BEGIN end ELSE IF @SPARE2=2 and @OrderType=3 and @LocalOrgCode=@OutOrgCode BEGIN endENDGO

转载于:https://www.cnblogs.com/li-xy/p/4225079.html

相关资源：sqlserver触发器加密破解