0 Replies Latest reply: Nov 17, 2011 5:52 PM by 900866 RSS

    Trigger error

    900866
      Hi;
      I am trying to creat Bi temporal(histroy) for another tabl, here is the structur and trigger but the Delete trigger doesnt update the VET, can anybody help me sinc i ned to deliver today. Thanks.

      Create TABLE system.PROJ_BT_HIST
      (
      PNAME VARCHAR2(255) NOT NULL,
      PNUMBER INTEGER NOT NULL,
      PLOCATION VARCHAR2(4000) NOT NULL,
      DNUM INTEGER NOT NULL,
      VST DATE default '01-JAN-11',
      VET DATE,
      TST TIMESTAMP default sysdate,
      TET TIMESTAMP default sysdate
      , CONSTRAINT PROJ_BT_HIST_PK PRIMARY KEY
      (
      PNUMBER, VST, TST
      )
      )
      ;


      Create TABLE system.PROJ
      (
      PNAME VARCHAR2(255) NOT NULL,
      PNUMBER INTEGER NOT NULL,
      PLOCATION VARCHAR2(4000) NOT NULL,
      DNUM INTEGER NOT NULL,
      CONSTRAINT PROJ_PK PRIMARY KEY
      (
      PNUMBER
      )
      )
      ;


      Insert into proj
      values('Product100',100,'Balgica',1,default,null,sysdate,null);
      ----------------------------------

      create or replace trigger "PROJ_Update"
      AFTER update on PROJ
      for each row
      begin
      if :new.VST=:old.VST
      then
      update proj_bt_hist set TET=sysdate where pnumber=:old.pnumber and TET IS NULL and VET IS NULL ;
      insert into PROJ_BT_HIST (PNAME, PNUMBER, PLOCATION, DNUM, VST, VET, TST, TET)
      values(:old.PNAME, :old.PNUMBER, :new.PLOCATION, :new.DNUM, :old.VST, null, sysdate, null);
      else
      update proj_bt_hist set TET=sysdate where pnumber=:old.pnumber and TET IS NULL and VET IS NULL;
      insert into PROJ_BT_HIST (PNAME, PNUMBER, PLOCATION, DNUM, VST, VET, TST, TET)
      values(:old.PNAME, :old.PNUMBER, :old.PLOCATION, :old.DNUM, :old.VST, :new.VST-1, sysdate, null);
      insert into PROJ_BT_HIST (PNAME, PNUMBER, PLOCATION, DNUM, VST, VET, TST, TET)
      values(:old.PNAME, :old.PNUMBER, :new.PLOCATION, :new.DNUM, :new.VST, null, sysdate, null);
      end if;
      END;
      ------------------------------
      create or replace trigger PROJ_Delete
      BEFORE delete on PROJ
      for each row
      begin
      update proj_bt_hist set TET=sysdate, VET=sysdate where (pnumber=:old.pnumber and VET IS NULL and TET IS NULL);
      END;