I’m trying to create a trigger which is firing after creating a new table in a specific schema called “HK”.
Background: all tables of schema HK need to be audited for INSERT, UPDATE and DELETE actions on it. Also all tables which are created in future.
I created an audit policy before called “changes_on_hk”. For that I just take one table of that schema
create audit policy changes_on_hk actions insert, update, delete on hk.TABLE_001;
With this small procedure I put all other tables into this audit policy
l_sql_stmt varchar2(1000);
FOR t IN (SELECT owner, table_name FROM all_tables WHERE upper(owner) = 'HK')
l_sql_stmt := 'alter audit policy changes_on_hk add actions insert, update, delete on ' || t.owner || '.' || t.table_name;
EXECUTE IMMEDIATE l_sql_stmt;
<code>DECLARE
l_sql_stmt varchar2(1000);
BEGIN
FOR t IN (SELECT owner, table_name FROM all_tables WHERE upper(owner) = 'HK')
LOOP
l_sql_stmt := 'alter audit policy changes_on_hk add actions insert, update, delete on ' || t.owner || '.' || t.table_name;
EXECUTE IMMEDIATE l_sql_stmt;
END LOOP;
END;
/
</code>
DECLARE
l_sql_stmt varchar2(1000);
BEGIN
FOR t IN (SELECT owner, table_name FROM all_tables WHERE upper(owner) = 'HK')
LOOP
l_sql_stmt := 'alter audit policy changes_on_hk add actions insert, update, delete on ' || t.owner || '.' || t.table_name;
EXECUTE IMMEDIATE l_sql_stmt;
END LOOP;
END;
/
And this is my trigger for future situation when new tables are created in HK schema to have these tables direct in audit
<code>CREATE OR REPLACE TRIGGER SYSTEM.CHECK_HK_TABLES_FOR_AUDIT
l_sql_stmt varchar2(1000);
v_schema_name varchar2(100);
v_table_name varchar2(100);
v_schema_name := ora_dict_obj_owner;
v_table_name := ora_dict_obj_name;
IF v_schema_name = 'HK' THEN
l_sql_stmt := 'alter audit policy changes_on_hk add actions insert, update, delete on ' || v_schema_name || '.' || v_table_name;
EXECUTE IMMEDIATE l_sql_stmt;
ALTER TRIGGER SYSTEM.CHECK_HK_TABLES_FOR_AUDIT ENABLE;
<code>CREATE OR REPLACE TRIGGER SYSTEM.CHECK_HK_TABLES_FOR_AUDIT
AFTER CREATE ON SCHEMA
DECLARE
l_sql_stmt varchar2(1000);
v_schema_name varchar2(100);
v_table_name varchar2(100);
BEGIN
v_schema_name := ora_dict_obj_owner;
v_table_name := ora_dict_obj_name;
IF v_schema_name = 'HK' THEN
l_sql_stmt := 'alter audit policy changes_on_hk add actions insert, update, delete on ' || v_schema_name || '.' || v_table_name;
EXECUTE IMMEDIATE l_sql_stmt;
END IF;
END;
/
ALTER TRIGGER SYSTEM.CHECK_HK_TABLES_FOR_AUDIT ENABLE;
</code>
CREATE OR REPLACE TRIGGER SYSTEM.CHECK_HK_TABLES_FOR_AUDIT
AFTER CREATE ON SCHEMA
DECLARE
l_sql_stmt varchar2(1000);
v_schema_name varchar2(100);
v_table_name varchar2(100);
BEGIN
v_schema_name := ora_dict_obj_owner;
v_table_name := ora_dict_obj_name;
IF v_schema_name = 'HK' THEN
l_sql_stmt := 'alter audit policy changes_on_hk add actions insert, update, delete on ' || v_schema_name || '.' || v_table_name;
EXECUTE IMMEDIATE l_sql_stmt;
END IF;
END;
/
ALTER TRIGGER SYSTEM.CHECK_HK_TABLES_FOR_AUDIT ENABLE;
Now I try to create a test table as different user (in this case I take system) in schema HK
<code>CREATE TABLE HK.TEST_DAVID_SYSTEM
<code>CREATE TABLE HK.TEST_DAVID_SYSTEM
(
column_1 number,
column_2 varchar2(5)
);
</code>
CREATE TABLE HK.TEST_DAVID_SYSTEM
(
column_1 number,
column_2 varchar2(5)
);
and as user HK in schema HK
<code>CREATE TABLE HK.TEST_DAVID_HK
<code>CREATE TABLE HK.TEST_DAVID_HK
(
column_1 number,
column_2 varchar2(5)
);
</code>
CREATE TABLE HK.TEST_DAVID_HK
(
column_1 number,
column_2 varchar2(5)
);
After this I look with this SQL, if the new tables are in my audit policy
<code>select upper(table_name) from all_tables where upper(owner) = 'HK'
select upper(object_name) from audit_unified_policies where lower(policy_name) in ('changes_on_hk');
<code>select upper(table_name) from all_tables where upper(owner) = 'HK'
minus
select upper(object_name) from audit_unified_policies where lower(policy_name) in ('changes_on_hk');
</code>
select upper(table_name) from all_tables where upper(owner) = 'HK'
minus
select upper(object_name) from audit_unified_policies where lower(policy_name) in ('changes_on_hk');
But they’re not. Do I miss something?