PL/SQL Triggers with dynamic sql

1
votes

I want to create ddl trigger(on create) which will create a dml trigger But i have error:

ORA-06512: на line 8 00604. 00000 - "error occurred at recursive SQL level %s" *Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables). *Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.

CREATE OR REPLACE TRIGGER test_ddl
after CREATE ON SCHEMA
DECLARE 
    user_col VARCHAR(5) := 'user_';
    time_col VARCHAR(5) := 'time_';
BEGIN
IF ora_dict_obj_type = 'TABLE'
THEN 
    EXECUTE IMMEDIATE 'alter table ' || ora_dict_obj_name || ' add(' || user_col || ' varchar(20), '|| time_col ||' timestamp)'||'';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER add_user_time BEFORE INSERT OR UPDATE ON test_tab FOR EACH ROW BEGIN ' || ':' || 'new.time_ := sysdate; END';
END IF;
END;                               
/
DROP TABLE test_tab PURGE;
/
CREATE TABLE test_tab(ID NUMBER);
2
oracleplsqltriggers
You might want to do some logging, at least of the DDL statements being generated and executed. Your trigger creation statement seems to be hardcoded with a single name for the trigger and table - this may or may not be the cause. It might help to add an exception handler that logs the error (before re-raising it). Note that this trigger will fire in a recursive fashion when the trigger is created (of course, your code stops the recursive trigger actually doing anything because of the condition on ora_dict_obj_type). - Jeffrey Kemp

2 Answers

0
votes

At the very least, I expect you want new.time_ to be :new.time_

0
votes

You are missing a semicolon after the END.

Replace this 

EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER add_user_time BEFORE INSERT OR UPDATE ON test_tab FOR EACH ROW BEGIN ' || ':' || 'new.time_ := sysdate; END';

with that

EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER add_user_time BEFORE INSERT OR UPDATE ON test_tab FOR EACH ROW BEGIN ' || ':' || 'new.time_ := sysdate; END;';

Full error messages are:

ORA-00604: error occurred at recursive SQL level 1
ORA-24344: success with compilation error
ORA-06512: at line 8

Important part was:

ORA-24344: success with compilation error

The source code should read:

CREATE OR REPLACE TRIGGER test_ddl
after CREATE ON SCHEMA
DECLARE 
    user_col VARCHAR(5) := 'user_';
    time_col VARCHAR(5) := 'time_';
BEGIN
IF ora_dict_obj_type = 'TABLE'
THEN 
    EXECUTE IMMEDIATE 'alter table ' || ora_dict_obj_name || ' add(' || user_col || ' varchar(20), '|| time_col ||' timestamp)'||'';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER add_user_time BEFORE INSERT OR UPDATE ON test_tab FOR EACH ROW BEGIN ' || ':' || 'new.time_ := sysdate; END;';
END IF;
END;                               
/
DROP TABLE test_tab PURGE;
/
CREATE TABLE test_tab(ID NUMBER);

Just as an explanation: I have put some log messages to see where it exactly fails. Then you can see that the trigger is recursively called upon the CREATE OR REPLACE TRIGGER but with an ora_dict_obj_type = TRIGGER