Hello,
I'm trying to build a trigger which will catch the NEW table name being renamed to OR NEW view name being renamed to and save this NEW name into a LOG table in a given column.
CREATE OR REPLACE TRIGGER test.tr_ctas_log
AFTER CREATE OR DROP OR RENAME
ON test.SCHEMA
DECLARE
s_ora_dict_obj_type VARCHAR2(30 CHAR);
s_ora_dict_obj_name VARCHAR2(30 CHAR);
BEGIN
s_ora_dict_obj_type := UPPER(TRIM(ora_dict_obj_type));
s_ora_dict_obj_name := UPPER(TRIM(ora_dict_obj_name));
--Log only for TABLE or VIEW object types
IF (s_ora_dict_obj_type = 'TABLE' OR s_ora_dict_obj_type = 'VIEW') THEN
IF ora_sysevent = 'CREATE' THEN
--Insert a record in the table
INSERT INTO test.tb_ctas_log
(
object_type,
object_name
)
VALUES
(
s_ora_dict_obj_type,
s_ora_dict_obj_name
);
ELSIF ora_sysevent = 'DROP' THEN
--Delete the record from the table
DELETE FROM test.tb_ctas_log
WHERE 1=1
AND object_type = s_ora_dict_obj_type
AND object_name = s_ora_dict_obj_name
;
ELSIF ora_sysevent = 'RENAME' THEN
--Update the record in the table
UPDATE test.tb_ctas_log
SET object_name = ?? --New Table/View Name
WHERE 1=1
AND object_type = s_ora_dict_obj_type
AND object_name = ?? --Old Table/View Name
;
ELSE
NULL;
END IF;
END IF;
END;
Any help on what should be in ?? would be appreciated.
Thanks!