sql server 与 Oracle Triggers 对照

sql server Triggers:
表 Deposit_Body_Change 新增，删除，修改的时候触发
ALTER TRIGGER [dbo].[Deposit_Body_Change_AUTO]
   ON  [dbo].[Deposit_Body_Change]
   FOR  INSERT,DELETE,UPDATE
AS
BEGIN
 
 SET NOCOUNT ON;

 if (exists (select 1 from inserted) and not exists (select 1 from deleted))--新增
 Begin
   insert into Deposit_Body select * from inserted
 End
else if(not exists (select 1 from inserted) and exists (select 1 from deleted) )--删除
Begin
   delete  Deposit_Body from   deleted   D   where   Deposit_Body.AUTOID=D.AUTOID
End
else  --更新
Begin
update   Deposit_Body SET OperaId=I.OperaId,
      DepositId=I.DepositId,
      ProductId=I.ProductId,
      ProductName=I.ProductName,
      [Count]=I.[Count],
      Units=I.Units,
      GoldNum=I.GoldNum,
      Custom_Code=I.Custom_Code,
      Remark2=I.Remark2
  from   Deposit_Body_Change G,inserted   I,deleted  D  
  where   G.AUTOID=D.AUTOID    and   I.AUTOID=D.AUTOID
End

END

对应的orale触发器如下：
Oracle Triggers:

create or replace trigger Deposit_Body_Change_AUTO
  after update or insert  or delete  
  on Deposit_Body_Change
  for each row
--declare
/******************************************************************************
     PURPOSE:
     业务类型：
     REVISIONS:
     Ver        Date        Author           Description
     ---------  ----------  ---------------  ------------------------------------
   
   
  ******************************************************************************/

begin
  if updating then
    update Deposit_Body
       SET OperaId     = :new.OperaId,
           DepositId   = :new.DepositId,
           ProductId   = :new.ProductId,
           ProductName = :new.ProductName,
           Count       = :new.Count,
           Units       = :new.Units,
           GoldNum     = :new.GoldNum,
           Custom_Code = :new.Custom_Code,
           Remark2     = :new.Remark2
    /*from   Deposit_Body_Change G,inserted   I,deleted  D    */
     where AUTOID = :old.AUTOID;
      -- and :new.AUTOID = :old.AUTOID
 
  end if;
  if inserting then
    insert into Deposit_Body
      (OPERAID,
       DEPOSITID,
       PRODUCTID,
       PRODUCTNAME,
       COUNT,
       UNITS,
       GOLDNUM,
       AUTOID,
       CUSTOM_CODE,
       REMARK2)
    values
      (:new.OPERAID,
       :new.DEPOSITID,
       :new.PRODUCTID,
       :new.PRODUCTNAME,
       :new.COUNT,
       :new.UNITS,
       :new.GOLDNUM,
       :new.AUTOID,
       :new.CUSTOM_CODE,
       :new.REMARK2);
  end if;
  if deleting then
    delete from Deposit_Body where Deposit_Body.AUTOID = :old.AUTOID;
  end if;

end Deposit_Body_Change_AUTO;

 

下面的Trigger可以参考
sql server 触发器：
ALTER TRIGGER [dbo].[TR_GP_DECLARATION_HEAD] ON [dbo].[GP_DECLARATION_HEAD]
FOR INSERT
AS

--列更新
if update(BATCH_NO)
begin

declare  @Batch_No  Varchar(30)
declare  @GatePass_No  Varchar(30)
declare  @Start_Date DateTime
declare  @End_Date DateTime

/*更新作业单的报关标志*/
set  @Start_Date = Substring(convert(varchar,(select START_DATE from Inserted),120),1,10)
set  @End_Date =  Substring(convert(varchar,(select End_DATE from Inserted),120),1,10)
set  @Batch_No = (select Batch_No from Inserted)
set  @GatePass_No =(select GatePass_No from Inserted)

if substring(@GatePass_No,1,1)='A'
 update gj_collection_head set IS_DECLARE = '1',Batch_No=@Batch_No
 where Gatepass_No= @GatePass_No
    and status in(dbo.FunGetStatus('FW12'),dbo.FunGetStatus('FG12'))
    /*edit by yuz*/
  /*  and (approve_date between convert(DateTime, @Start_Date + ' 00:00:00',120)
    and convert( DateTime,@End_Date + ' 23:59:59',120))  */
    and approve_date <=  convert( DateTime,@End_Date + ' 23:59:59',120);

else if substring(@GatePass_No,1,1)='J'
    update GJ_DEEPPROCESS_HEAD set IS_DECLARE = '1',Batch_No=@Batch_No
 where Gatepass_No= @GatePass_No
    and status in ('W12','G12')
    and approve_date <=  convert( DateTime,@End_Date + ' 23:59:59',120);

end

 

--Oracle 触发器
create or replace trigger TR_GP_DECLARATION_HEAD
 after  insert
    on GP_DECLARATION_HEAD
         for each row
   /******************************************************************************
     PURPOSE:
     业务类型：
     REVISIONS:
     Ver        Date        Author           Description
     ---------  ----------  ---------------  ------------------------------------
 
  ******************************************************************************/
 
declare
  P_Batch_No    nvarchar2(30);
  P_GatePass_No nvarchar2(30);
  P_Start_Date  date;
  P_End_Date    date;
begin
  if updating('BATCH_NO') then
    P_Start_Date  := :new.START_DATE;
    P_End_Date    := :new.End_DATE;
    P_Batch_No    := :new.Batch_No;
    P_GatePass_No := :new.GatePass_No;
 
    if substr(P_GatePass_No, 1, 1) = 'A' then
      update gj_collection_head
         set IS_DECLARE = '1', Batch_No = P_Batch_No
       where Gatepass_No = P_GatePass_No
         and status in (FunGetStatus('FW12'), FunGetStatus('FG12'))
         and approve_date <= P_End_Date;
   
    end if;
 
    if substr(P_GatePass_No, 1, 1) = 'J' then
   
      update GJ_DEEPPROCESS_HEAD
         set IS_DECLARE = '1', Batch_No = P_Batch_No
       where Gatepass_No = P_GatePass_No
         and status in ('W12', 'G12')
         and approve_date <= P_End_Date;
   
    end if;
  end if;
end TR_GP_DECLARATION_HEAD;

--FunGetStatus 函数
sql server 版本：
ALTER FUNCTION [dbo].[FunGetStatus] (@status varchar(10)) 
RETURNS varchar(10) AS 
BEGIN

DECLARE @result  varchar(10)
 
---01****监控科状态
 if (@status='FW11') 
       set @result='W11'

        else if(@status='FW12')
   set @result='W12'

 else if(@status='FW13')
   set @result='W13'

 else if(@status='FW14')
   set @result='W14'
 
 else if(@status='FW15')
   set @result='W15'

---02****企业端状态

 else if (@status='FG01') 
       set @result='G01'

        else if(@status='FG02')
   set @result='G02'

 else if(@status='FG03')
   set @result='G03'

 else if(@status='FG04')
   set @result='G04'
 
 else if(@status='FG05')
   set @result='G05'

 else if(@status='FG06')
   set @result='G06'

----03****主卡口状态

 else if (@status='FG11') 
       set @result='G11'

        else if(@status='FG12')
   set @result='G12'

 else if(@status='FG13')
   set @result='G13'

 else if(@status='FG14')
   set @result='G14'

 else if(@status='FG15')
   set @result='G15'

----04****西卡口状态

 else if (@status='FG21') 
       set @result='G21'

        else if(@status='FG22')
   set @result='G22'

 else if(@status='FG23')
   set @result='G23'

 else if(@status='FG24')
   set @result='G24'

 else if(@status='FG25')
   set @result='G25'

----05****北卡口状态 
 else if(@status='FG31')
   set @result='G31'

 else if(@status='FG32')
   set @result='G32'

 else if(@status='FG33')
   set @result='G33'

 else if(@status='FG34')
   set @result='G34'

 else if(@status='FG35')
   set @result='G35'

return(@result)
end

oracle 版本：

create or replace function FunGetStatus(P_status in varchar2
                                         )
  return varchar2 as
  /* ----------------------------------------------------------------------------------------------------------------------------------------------
     PURPOSE:    返回合同结果，参数：为合同有效期，企业代码
     REVISIONS:
     Ver             Date               Author           Description
  ---------    --------------    ---------------  ------------------------------------
  ------------------------------------------------------------------------------------------------------------------------------------------------*/
  P_result  varchar2(10);
 
BEGIN

 
---01****监控科状态
  if (P_status='FW11') then
         P_result:='W11' ;
  end if;
        if(P_status='FW12') then
     P_result:='W12';
  end if;
  if(P_status='FW13') then
      P_result:='W13';
  end if;
  if(P_status='FW14') then
     P_result:='W14';
  end if;
  if(P_status='FW15') then
     P_result:='W15';
  end if;
---02****企业端状态

  if (P_status='FG01')  then
          P_result:='G01' ;
  end if;
        if(P_status='FG02') then
      P_result:='G02';
end if;
  if(P_status='FG03') then
      P_result:='G03';
end if;
  if(P_status='FG04') then
      P_result:='G04';
 end if;
  if(P_status='FG05') then
      P_result:='G05';
end if;
  if(P_status='FG06') then
      P_result:='G06';
end if;
----03****主卡口状态

  if (P_status='FG11') then
        P_result:='G11';
end if;
        if(P_status='FG12') then
    P_result:='G12';
end if;
 if(P_status='FG13') then
    P_result:='G13';
end if;
 if(P_status='FG14') then
    P_result:='G14';
end if;
 if(P_status='FG15') then
    P_result:='G15';
end if;
----04****西卡口状态

 if (P_status='FG21') then
        P_result:='G21' ;
end if;
        if(P_status='FG22') then
    P_result:='G22';
end if;
 if(P_status='FG23') then
    P_result:='G23';
end if;
 if(P_status='FG24') then
    P_result:='G24';
end if;
 if(P_status='FG25') then
    P_result:='G25';
end if;
----05****北卡口状态 
 if(P_status='FG31') then
    P_result:='G31';
end if;
 if(P_status='FG32') then
    P_result:='G32';
end if;
 if(P_status='FG33') then
    P_result:='G33';
end if;
 if(P_status='FG34')then
    P_result:='G34';
end if;
 if(P_status='FG35') then
    P_result:='G35';
end if;
return(P_result);
end;

 表出现变更的时候,对应的触发操作

 

--新增的trigger
create or replace trigger Tr_GJ_LineOne_Head_web
  after insert   on GJ_LineOne_Head  --OR UPDATE
  for each row
begin
         insert into gj_lineone_head_web(gatejob_no,status,send_mark,send_mark2,STATUS_UPDATE)
         values(:new.gatejob_no,:new.status,:new.send_mark,:new.send_mark2,'insert');

end;
--修改的trigger
create or replace trigger Tr_GJ_LineOne_Head_web_U
  after UPDATE   on GJ_LineOne_Head  --OR UPDATE
  for each row
begin
         insert into gj_lineone_head_web(gatejob_no,status,send_mark,send_mark2,STATUS_UPDATE)
         values(:new.gatejob_no,:new.status,:new.send_mark,:new.send_mark2,'Update');

end;
--删除的trigger
create or replace trigger Tr_GJ_LineOne_Head_web_D
  after delete  on GJ_LineOne_Head
  for each row
begin
         insert into gj_lineone_head_web(gatejob_no,status,send_mark,send_mark2,STATUS_UPDATE)
         values(:old.gatejob_no,:old.status,:old.send_mark,:old.send_mark2,'delete');

end;