学习触发器

deleted 和 inserted 是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。例如， 一个简单的示例：

 

CREATE TRIGGER COPYDATAzc
ON dbo.入出库台帐资材仓库
FOR INSERT, UPDATE, DELETE 
AS
declare @ID INT,
        @RCKFL varchar(255),
        @JZHM varchar(255),
        @PLHM varchar(255),
        @SETSL INT,
        @LJHM varchar(255),
        @LJSL INT,
        @RCKR INT,
        @DBMHM VARCHAR(255),
        @DOMOEXP1 SMALLINT,
        @BSQB VARCHAR(255),
        @YYZBXG int,
        @BZ VARCHAR(255),
        @ZBMHM SMALLINT,
        @JZHMG VARCHAR(255),
        @DONO VARCHAR(255)

If not exists(Select 1 from deleted)   ----Insert
    Begin
     select @ID=ID, @RCKFL=入出库分类,@JZHM=机种号码,@PLHM=批量号码,@SETSL=SET数量,@LJHM=零件号码,@LJSL=零件数量,@RCKR=入出库日,@DBMHM=对部门号码,@DOMOEXP1=DOM0EXP1,@BSQB=保税区别,@YYZBXG=用圆珠笔修改,@BZ=备注,@ZBMHM=自部门号码,@DONO=DONO,@JZHMG=left(机种号码,9) from inserted
     insert into 入出库台帐资材仓库A values(@ID,@RCKFL,@JZHM,@PLHM,@SETSL,@LJHM,@LJSL,@RCKR,@DBMHM,@DOMOEXP1,@BSQB,@YYZBXG,@BZ,@ZBMHM,@DONO,@JZHMG)
    End
If not exists(select 1 from inserted)  ---Delete
  Begin     
     select @ID=ID from deleted
     delete 入出库台帐资材仓库A where ID=@ID
  End

If exists(select 1 from inserted) and exists(select 1 from deleted) ---Update
  Begin
     select @ID=ID, @RCKFL=入出库分类,@JZHM=机种号码,@PLHM=批量号码,@SETSL=SET数量,@LJHM=零件号码,@LJSL=零件数量,@RCKR=入出库日,@DBMHM=对部门号码,@DOMOEXP1=DOM0EXP1,@BSQB=保税区别,@YYZBXG=用圆珠笔修改,@BZ=备注,@ZBMHM=自部门号码,@DONO=DONO,@JZHMG=left(机种号码,9) from inserted
     update 入出库台帐资材仓库A set 入出库分类=@RCKFL,机种号码=@JZHM,批量号码=@PLHM,SET数量=@SETSL,零件号码=@LJHM,零件数量=@LJSL,入出库日=@RCKR,对部门号码=@DBMHM,DOM0EXP1=@DOMOEXP1,保税区别=@BSQB,用圆珠笔修改=@YYZBXG,备注=@BZ,自部门号码=@ZBMHM,DONO=@DONO,机种号码改=@JZHMG where ID=@ID
  End