CREATE OR REPLACE TRIGGER TRG_BACKUP_SOURCE
BEFORE CREATE
ON DATABASE
DECLARE
  lvRec tab_backup_source%ROWTYPE;
  lvView CLOB;

  CURSOR cur(curOwner IN VARCHAR2, curName IN VARCHAR2, curType IN VARCHAR2) IS
    SELECT line, text
      FROM dba_source
     WHERE owner = curOwner
       AND name = curName
       AND type = curType
     ORDER BY line; 

BEGIN 

  IF ora_sysevent = 'CREATE' THEN
    -- prvo trebamo sakupiti informacije o objektu i korisniku, koji ubacuje noviju verziju:
    SELECT ora_dict_obj_owner,
           ora_dict_obj_name,
           ora_dict_obj_type,
           sys_context('USERENV','IP_ADDRESS'),
           sys_context('USERENV','OS_USER'),
           SYSDATE
      INTO lvRec.owner,
           lvRec.obj_name,
           lvRec.obj_type,
           lvRec.ip,
           lvRec.who,
           lvRec.logtime
      FROM dual; 

    -- Source code objekata tipa 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION' i 'TRIGGER'
    -- uzimamo iz data dictionary view-a DBA_SOURCE:
    IF ora_dict_obj_type IN('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER') THEN
      FOR rec IN cur(ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type) LOOP
        IF rec.line = 1 THEN
          lvRec.source_code := 'CREATE OR REPLACE '|| ora_dict_obj_type ||' '|| ora_dict_obj_name || CHR(13)||CHR(10);
        ELSE
          lvRec.source_code := lvRec.source_code || rec.text;
        END IF;
      END LOOP; 

    -- Source code "pogleda" uzimamo pomocu DBMS_METADATA.get_ddl:
    ELSIF ora_dict_obj_type = 'VIEW' THEN
      lvRec.source_code := DBMS_METADATA.get_ddl('VIEW', ora_dict_obj_name, ora_dict_obj_owner); 

    END IF; -- END IF ora_dict_obj_type ... 


    -- Kada smo sakupili sve informacije, spremamo ih u tabelu:
    INSERT INTO tab_backup_source
    VALUES lvRec; 

  END IF; -- END IF ora_sysevent = 'CREATE' 

EXCEPTION
  WHEN OTHERS THEN
    NULL;
END TRG_BACKUP_SOURCE;
/