I'm trying to create a trigger that creates a backup of a table before it is truncated. However, I'm getting a permission error when attempting to create a new table. Why?
create or replace trigger my_trigger
before truncate on schema
begin
if (ora_dict_obj_type = 'TABLE' and ora_dict_obj_name = 'TABLE1') then
execute immediate 'create table TABLE1_BACKUP as select * from TABLE1';
end if;
end;
/
I get this error:
ORA-04088: error during execution of trigger 'MY_SCHEMA.MY_TRIGGER'
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 3
04088. 00000 - "error during execution of trigger '%s.%s'"
*Cause: A runtime error occurred during execution of a trigger.
*Action: Check the triggers which were involved in the operation.
Running this PL/SQL outside of the trigger works just fine:
begin
execute immediate 'create table TABLE1_BACKUP as select * from TABLE1';
end;
/