具有动态SQL的PL/SQL触发器

Question

我想创建ddl触发器（在创建），这将创建一个dml触发器，但我有错误：

ORA-06512：8 00604. 00000-"错误发生在递归SQL级别%s"*原因：错误发生在处理递归SQL语句（适用于内部字典表的语句）。*操作：如果可以纠正堆栈上下一个错误中描述的情况，请这样做；否则请联系Oracle支持。

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);

Answer 1

在END后面缺少一个分号。

替换这个

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

有了这个

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

完整的错误消息包括：

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

重要部分是：

ORA-24344: success with compilation error

源代码应为：

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);

只是作为一个解释：我已经放了一些日志消息，看看它到底失败在哪里。然后，您可以看到触发器是递归调用CREATE OR REPLACE TRIGGER，但使用ora_dict_obj_type=TRIGGER

Answer 2

至少，我希望您希望new.time.成为：new.time.