在过程中动态添加的表上创建触发器

Question

我从java创建动态表。我想要的是，我想创建一个触发器添加每个新表，将只使用序列填充主键（serial_no），这是在每个表中常见的。如何实现这？

编辑： 我曾尝试这个代码，但我'越来越

create or replace procedure "TRIGGER_CALL" (trigger_name IN VARCHAR2, table_name IN VARCHAR2, sno IN NUMBER) as begin CREATE SEQUENCE abc MINVALUE 1 MAXVALUE 10000 INCREMENT BY 1 START WITH 141 CACHE 20 NOORDER NOCYCLE; CREATE OR REPLACE TRIGGER trigger_name before insert on table_name for each row begin select s_no.nextval into :new.sno from dual; end; end; 

EDIT2消息 “与编译错误创建程序”： 我的代码

CREATE OR REPLACE 
PROCEDURE "TRIGGER_CALL" (p_table_name IN VARCHAR2) 
AUTHID CURRENT_USER       
AS 
    l_sql VARCHAR2(4000); 
    l_dummy NUMBER; 
    l_trigger_name VARCHAR2(30); 
    l_seq_name VARCHAR2(30); 
BEGIN 

    --SELECT '1' 
    --INTO l_dummy 
    --FROM all_tables 
    -- WHERE table_name = UPPER(p_table_name); 

    l_trigger_name := p_table_name || '_trg'; 
    l_seq_name := p_table_name || 's_no'; 
EXECUTE IMMEDIATE 'CREATE SEQUENCE l_seq_name start with 1 increment by 1 '; 
    l_sql := 
    'CREATE OR replace TRIGGER ' || l_trigger_name || 
    ' BEFORE INSERT ON ' || p_table_name || 
    ' FOR EACH ROW 
    BEGIN 
     SELECT l_seq_name.NEXTVAL 
     INTO :new.sno 
     FROM dual; 
    END;'; 

    EXECUTE IMMEDIATE l_sql; 
END; 
/

Answer 1

请检查下面的代码：

CREATE SEQUENCE my_sequence; 
/

CREATE OR REPLACE 
PROCEDURE "TRIGGER_CALL" (p_table_name IN VARCHAR2) 
AUTHID CURRENT_USER       
AS 
    l_sql VARCHAR2(4000); 
    l_dummy NUMBER; 
    l_trigger_name VARCHAR2(30); 
BEGIN 
    -- Validate if a p_table_name is a valid object name 
    -- If you have access you can also use DBMS_ASSERT.SQL_OBJECT_NAME procedure 
    SELECT '1' 
    INTO l_dummy 
    FROM all_tables 
    WHERE table_name = UPPER(p_table_name); 

    l_trigger_name := p_table_name || '_trg'; 

    l_sql := 
    'CREATE OR replace TRIGGER ' || l_trigger_name || 
    ' BEFORE INSERT ON ' || p_table_name || 
    ' FOR EACH ROW 
    BEGIN 
     SELECT my_sequence.NEXTVAL 
     INTO :new.sno 
     FROM dual; 
    END;'; 

    EXECUTE IMMEDIATE l_sql; 
END; 
/

CREATE TABLE my_test(sno NUMBER); 
/

BEGIN 
    trigger_call('my_test'); 
END; 
/

重要注意事项：

1. AUTHID CURRENT_USER的使用消除了“权限不足”问题。仅供参考，请参阅：Execute Immediate within a stored procedure keeps giving insufficient priviliges error

2. 因为动态SQL只是简单地连接输入参数，所以需要对其进行验证以防止SQL注入。另见DBMS_ASSERT。

3. 因为第2点。我使用表名来构建触发器名称。