-- 
-- Custom auditing example created to capture inserts and updates
-- on a table. I used this trigger and table combination to debug
-- a procedure that did not behave the way we expected after an
-- upgrade of our ERP.
--

CREATE TABLE OPS$STOEVER.AUD$_GOREMAL
(
  USERNAME                   VARCHAR2(100 BYTE),
  MODULE                     VARCHAR2(100 BYTE),
  ACTIVITY_DATE              DATE,
  ACTION                     VARCHAR2(25 BYTE),
  OLD_GOREMAL_PIDM           NUMBER(8),
  OLD_GOREMAL_EMAL_CODE      VARCHAR2(4 BYTE),
  OLD_GOREMAL_EMAIL_ADDRESS  VARCHAR2(90 BYTE),
  OLD_GOREMAL_COMMENT        VARCHAR2(60 BYTE),
  NEW_GOREMAL_PIDM           NUMBER(8),
  NEW_GOREMAL_EMAL_CODE      VARCHAR2(4 BYTE),
  NEW_GOREMAL_EMAIL_ADDRESS  VARCHAR2(90 BYTE),
  NEW_GOREMAL_COMMENT        VARCHAR2(60 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;


GRANT DELETE, INSERT, UPDATE ON  AUD$_GOREMAL TO BANINST1;
/

CREATE OR REPLACE TRIGGER baninst1.fts_audit_goremal
   AFTER INSERT OR UPDATE
   ON goremal
   FOR EACH ROW
   WHEN (   OLD.goremal_emal_code IN ('CP', 'FISH')
         OR NEW.goremal_emal_code IN ('CP', 'FISH')
        )
DECLARE
   username     VARCHAR2 (100);
   modulename   VARCHAR2 (100);
   doing        VARCHAR2 (100);
BEGIN
   IF UPDATING
   THEN
      doing := 'UPDATING';
   ELSIF INSERTING
   THEN
      doing := 'INSERTING';
   END IF;

   SELECT SUBSTR (SYS_CONTEXT ('USERENV', 'SESSION_USER'), 1, 100)
     INTO username
     FROM DUAL;

   SELECT SUBSTR (MODULE, 1, 100)
     INTO modulename
     FROM v$session
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid;

   IF UPDATING
   THEN
      INSERT INTO ops$stoever.aud$_goremal
           VALUES (username, modulename, SYSDATE, doing, :OLD.goremal_pidm,
                   :OLD.goremal_emal_code, :OLD.goremal_email_address,
                   :OLD.goremal_comment, :NEW.goremal_pidm,
                   :NEW.goremal_emal_code, :NEW.goremal_email_address,
                   :NEW.goremal_comment);
   ELSIF INSERTING
   THEN
      INSERT INTO ops$stoever.aud$_goremal
           VALUES (username, modulename, SYSDATE, doing, NULL, NULL, NULL,
                   NULL, :NEW.goremal_pidm, :NEW.goremal_emal_code,
                   :NEW.goremal_email_address, :NEW.goremal_comment);
   END IF;
END;