oracle 触发器

Oracle触发器

表名:temp1_stock_1_1

表结构:

create table TEMP1_STOCK_1_1
(
  COP_G_NO          VARCHAR2(50) not null,
  ONELINE_IN_COUNT  NUMBER default 0,
  ONELINE_OUT_COUNT NUMBER default 0,
  TWOLINE_IN_COUNT  NUMBER default 0,
  TWOLINE_OUT_COUNT NUMBER default 0,
  SPEC_IN_COUNT     NUMBER default 0,
  SPEC_OUT_COUNT    NUMBER default 0,
  USERID            VARCHAR2(40),
  HSCODE            VARCHAR2(20),
  G_NAME            VARCHAR2(100),
  COLL_IN_COUNT     NUMBER(17,5) default 0,
  COLL_OUT_COUNT    NUMBER(17,5) default 0,
  INC_IN            NUMBER(17,5) default 0,
  INC_OUT           NUMBER(17,5) default 0,
  SIP_IN            NUMBER(17,5) default 0,
  SIP_OUT           NUMBER(17,5) default 0
)
tablespace CBLC_V2
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table TEMP1_STOCK_1_1
  is '保存当天的库存';

 

--更新楚剧触发器

create or replace trigger trigger_g_status
  before update on temp1_stock_1_1
  for each row
declare
begin
  if(updating('COP_G_NO') or updating('ONELINE_IN_COUNT')) then
     insert into TEST1(COP_G_NO,ONELINE_IN_COUNT,COP_G_NO_OLD,ONELINE_IN_COUNT_OLD,STATUS) values
     (:new.COP_G_NO ,:new.ONELINE_IN_COUNT,:old.COP_G_NO,:old.ONELINE_IN_COUNT,'E');
  end if; 
end trigger_g_status;

--新增数据触发器
create or replace trigger trigger_g_status_insert
  before INSERT on temp1_stock_1_1
  for each row
declare
begin
     insert into TEST1(COP_G_NO,ONELINE_IN_COUNT,STATUS) values
     (:new.COP_G_NO ,:new.ONELINE_IN_COUNT,'I');
end trigger_g_status_insert;

--删除数据触发器
create or replace trigger trigger_g_status_del
  before DELETE on temp1_stock_1_1
  for each row
declare
begin
     insert into TEST1(COP_G_NO,ONELINE_IN_COUNT,STATUS) values
     (:old.COP_G_NO ,:old.ONELINE_IN_COUNT,'D');
end trigger_g_status_del;