creating a trigger on a dynamically added table inside a procedure

Question

I'am creating dynamic tables from java. What I want is that I want to create a trigger on every new table added which will just use the sequence to populate the primary key(serial_no) which is common in every table.How to achieve this ???

EDIT: I have tried this code but I'am getting a message "procedure created with compilation errors"

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: My code

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

You'll have to do it the long way, then - dynamically define a sequence and a trigger for each table you create. — William Robertson, Apr 23 '17 at 22:18

score 1 · Accepted Answer · edited May 23 '17 at 12:34

1

Please check following code:

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

Important notes:

Usage of AUTHID CURRENT_USER eliminates the "insufficient privileges" problem. For reference see: Execute Immediate within a stored procedure keeps giving insufficient priviliges error
Because the dynamic sql simply concatenates the input parameter it needs to be validated to protect against SQL Injection. See also DBMS_ASSERT.
Because of point 2. I used table name to build trigger name.

edited May 23 '17 at 12:34

Community

1
1

answered Apr 24 '17 at 13:21

kpater87

1,190
12
31

I am getting this error message "oracle pls-00103 encountered the symbol create when expecting one of the following" – Aman Deep Apr 24 '17 at 19:17
To use `l_seq_name` in a dynamic sql you have to concatenate it with the string, you cannot use it like you posted (check how I am using `l_trigger_name` variable). Except that the code is running properly. I am not getting PLS-00103 error. How do you call this procedure? – kpater87 Apr 24 '17 at 20:00
Iam running the command "show error procedure trigger_call" in sql command line – Aman Deep Apr 24 '17 at 20:28
I've created the procedure using SQLDeveloper and it is working fine (keeping in mind my previous comment). You have to provide some more details, otherwise I am not able to guess what is your problem. Maybe you can show your full sql command line containing procedure creation and error details. – kpater87 Apr 24 '17 at 20:37
Did you manage to solve your issue? If so please post your answer or accept this one for further references. – kpater87 Apr 25 '17 at 11:18

creating a trigger on a dynamically added table inside a procedure

1 Answers1