drop table suivi_parav;

CREATE TABLE SUIVI_PARAV
(
  MODEDAT  VARCHAR2(8),
  DATMOD   VARCHAR2(8),
  HEUMOD   VARCHAR2(6),
  CODSOC   NUMBER,
  CODPAR   VARCHAR2(6),
  CODFCT   VARCHAR2(8),
  PARA1    VARCHAR2(6),
  PARA2    VARCHAR2(6),
  PARA3    VARCHAR2(6),
  PARN1    INTEGER,
  PARN2    INTEGER,
  PARN3    INTEGER,
  PARN4    INTEGER,
  PARN5    INTEGER,
  PARM1    VARCHAR2(12),
  PARM2    VARCHAR2(12),
  PARD1    NUMBER,
  PARD2    NUMBER,
  PARD3    NUMBER,
  ACTIF    VARCHAR2(1)
);

drop table suivi_pev;

CREATE TABLE SUIVI_PEV
(
  MODEDAT     VARCHAR2(8),
  DATMOD      VARCHAR2(8),
  HEUMOD      VARCHAR2(6),
  CODSOC      NUMBER,
  CODPEV      VARCHAR2(8),
  ACHVTE      VARCHAR2(1),
  TYPEVE      VARCHAR2(3),
  CODOSK      VARCHAR2(6),
  ACHVTO      VARCHAR2(1),
  TYPEVO      VARCHAR2(3),
  DEPAUT      VARCHAR2(1),
  MAJCNT      VARCHAR2(1),
  MAJENC      VARCHAR2(1),
  SAIQTE      VARCHAR2(1),
  ACHVTS      VARCHAR2(1),
  TYPEVS      VARCHAR2(3),
  ORDEVE      INTEGER,
  ORDEVS      INTEGER,
  ORDEVO      INTEGER,
  EVOOBL      VARCHAR2(1),
  CODOSK_D    VARCHAR2(6),
  CODOSK_V    VARCHAR2(6),
  CODOSK_VD   VARCHAR2(6),
  ENGAGE      VARCHAR2(1),
  RIDATLIV    VARCHAR2(1),
  RIDATEXP    VARCHAR2(1),
  MODUNI      VARCHAR2(1),
  ANNULE      VARCHAR2(1),
  TYPTIE      VARCHAR2(3),
  ETAEVO      VARCHAR2(1),
  INVALIDATI  VARCHAR2(1),
  REFPROD     VARCHAR2(1),
  PEVOEVO     VARCHAR2(1),
  CODETA      VARCHAR2(1),
  VALETA      VARCHAR2(1),
  CODETT      VARCHAR2(3),
  FORCEV      VARCHAR2(1),
  GESSTK      VARCHAR2(1),
  NBJLIV      INTEGER,
  NBJEXP      INTEGER
);

et le trigger

CREATE OR REPLACE TRIGGER suivi_parametre
BEFORE insert or update or delete ON SOC1.parav
FOR EACH ROW
when (new.codsoc=3030)
DECLARE
       time_rel char(6);
      date_rel char(8);
BEGIN
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') "NOW" INTO date_rel FROM dual;

SELECT TO_CHAR(SYSDATE, 'HH24MISS') "NOW" INTO time_rel FROM dual;   
if inserting then
         INSERT INTO SOC1.suivi_parav VALUES ('INSERT',date_rel,time_rel, :new.codsoc,:new.codpar, :new.codfct, :new.para1,
        :new.para2,:new.para3, :new.parn1, :new.parn2, :new.parn3, :new.parn4, :new.parn5, :new.parm1, :new.parm2, :new.pard1, :new.pard2, :new.pard3, :new.actif);
     elsif deleting then
         INSERT INTO SOC1.suivi_parav VALUES ('DELETE',date_rel,time_rel, :old.codsoc,:old.codpar, :old.codfct, :old.para1,
         :old.para2,:old.para3, :old.parn1, :old.parn2, :old.parn3, :old.parn4, :old.parn5, :old.parm1, :old.parm2, :old.pard1, :old.pard2, :old.pard3, :old.actif);
     elsif updating then
         INSERT INTO SOC1.suivi_parav VALUES ('UPDATE',date_rel,time_rel, :old.codsoc, :old.codpar, :old.codfct, :old.para1,
         :old.para2,:old.para3, :old.parn1, :old.parn2, :old.parn3, :old.parn4, :old.parn5, :old.parm1, :old.parm2, :old.pard1, :old.pard2, :old.pard3, :old.actif);
END IF;
END;
/
;
CREATE OR REPLACE TRIGGER suivi_pev_fct
BEFORE insert or update or delete ON soc1.pev
FOR EACH ROW
/* choisir le codsoc a impacter developpe par SJF  support commerce*/
when (new.codsoc=3030)
DECLARE
       time_rel char(6);
    date_rel char(8);
BEGIN

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') "NOW" INTO date_rel FROM dual;

SELECT TO_CHAR(SYSDATE, 'HH24MISS') "NOW" INTO time_rel FROM dual;   
    if inserting then
         INSERT INTO soc1.suivi_pev VALUES ('INSERT',date_rel,time_rel, :new.CODSOC,:new.CODPEV, :new.ACHVTE, :new.TYPEVE, :new.CODOSK, :new.ACHVTO, :new.TYPEVO, :new.DEPAUT, :new.MAJCNT, :new.MAJENC, :new.SAIQTE, :new.ACHVTS, :new.TYPEVS, :new.ORDEVE, :new.ORDEVS, :new.ORDEVO, :new.EVOOBL, :new.CODOSK_D, :new.CODOSK_V, :new.CODOSK_VD, :new.ENGAGE, :new.RIDATLIV, :new.RIDATEXP, :new.MODUNI, :new.ANNULE, :new.TYPTIE, :new.ETAEVO, :new.INVALIDATI, :new.REFPROD, :new.PEVOEVO, :new.CODETA, :new.VALETA, :new.CODETT, :new.FORCEV, :new.GESSTK, :new.NBJLIV, :new.NBJEXP);
     elsif deleting then
         INSERT INTO soc1.suivi_pev VALUES ('DELETE',date_rel,time_rel, :old.CODSOC, :old.CODPEV, :old.ACHVTE, :old.TYPEVE, :old.CODOSK, :old.ACHVTO, :old.TYPEVO, :old.DEPAUT, :old.MAJCNT, :old.MAJENC, :old.SAIQTE, :old.ACHVTS, :old.TYPEVS, :old.ORDEVE, :old.ORDEVS, :old.ORDEVO, :old.EVOOBL, :old.CODOSK_D, :old.CODOSK_V, :old.CODOSK_VD, :old.ENGAGE, :old.RIDATLIV, :old.RIDATEXP, :old.MODUNI, :old.ANNULE, :old.TYPTIE, :old.ETAEVO, :old.INVALIDATI, :old.REFPROD, :old.PEVOEVO, :old.CODETA, :old.VALETA, :old.CODETT, :old.FORCEV, :old.GESSTK, :old.NBJLIV, :old.NBJEXP);
     elsif updating then
         INSERT INTO soc1.suivi_pev VALUES ('UPDATE',date_rel,time_rel, :old.CODSOC, :old.CODPEV, :old.ACHVTE, :old.TYPEVE, :old.CODOSK, :old.ACHVTO, :old.TYPEVO, :old.DEPAUT, :old.MAJCNT, :old.MAJENC, :old.SAIQTE, :old.ACHVTS, :old.TYPEVS, :old.ORDEVE, :old.ORDEVS, :old.ORDEVO, :old.EVOOBL, :old.CODOSK_D, :old.CODOSK_V, :old.CODOSK_VD, :old.ENGAGE, :old.RIDATLIV, :old.RIDATEXP, :old.MODUNI, :old.ANNULE, :old.TYPTIE, :old.ETAEVO, :old.INVALIDATI, :old.REFPROD, :old.PEVOEVO, :old.CODETA, :old.VALETA, :old.CODETT, :old.FORCEV, :old.GESSTK, :old.NBJLIV, :old.NBJEXP);
END IF;
END;
/
;