Trigger to keeep track of changes made for PL/SQL Code

Always if we use Create or replace for a pl/sql code,high chances to make chages for exsiting one.So we need to track the changes made.


CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, ALL_SOURCE.*FROM ALL_SOURCE WHERE 1=2;

------------------------------------------------

create a trigger to capture the chages made

CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name

DECLARE
BEGIN
IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY',
'TYPE', 'TYPE BODY')

THEN
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, all_source.* FROM ALL_SOURCE
WHERE TYPE = ORA_DICT_OBJ_TYPE -- DICTIONARY_OBJ_TYPE IN 8i
AND NAME = ORA_DICT_OBJ_NAME; -- DICTIONARY_OBJ_NAME IN 8i
END IF;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;

/