use the same auto increment trigger in oracle for many tables

Question

I created a database in MySQL with ~10 tables, each starting with the column

SN INT NOT NULL AUTO_INCREMENT 

SN doesn't mean anything, just the primary to differentiate between possibly repeating/similar names/titles, etc

I'm moving it to Oracle now, and found this post here on stackoverflow to make the trigger to auto increment the SN field. Basically,

CREATE SEQUENCE user_seq;

CREATE OR REPLACE TRIGGER user_inc 
BEFORE INSERT ON users 
FOR EACH ROW

BEGIN
  SELECT user_seq.NEXTVAL
  INTO   :new.SN
  FROM   dual;
END;
/

Now, how can I rewrite that trigger once to apply to all the other tables? Because otherwise I have to rewrite it for many tables, just changing the trigger name and sequence name... I was picturing something like:

BEFORE INSERT ON users OR other_table OR another_one

I also found this post here, but the one answer there isn't helpful because I think it's reasonable for many tables to have the same SN field, or I'm misunderstanding the point.

Also, not Oracle 12c so no identity columns

Thanks in advance

I was going to just comment on the first post I mentioned but I can't comment without more reputation points :/

Answer 1

Creating a trigger referencing many tables in Oracle is not possible, What you can do is to generate the triggers with a PL/SQL statement.

Below is an example on how could you achieve this

drop table tab_a;
drop table tab_b;
drop table tab_c;

drop sequence seq_tab_a_id;
drop sequence seq_tab_b_id;
drop sequence seq_tab_c_id;

--create test tables 
create table tab_a (SN number, otherfield varchar2(30), date_field date);
create table tab_b (SN number, otherfield varchar2(30), date_field date);
create table tab_c (SN number, otherfield varchar2(30), date_field date);

-- this pl/sql block creates the sequences and the triggers 
declare
  my_seq_create_stmt varchar2(2000);
  my_trigger_create_stmt varchar2(2000);  
begin

  for i in (select table_name
              from user_tables
            -- remember to change this where condition to filter 
            -- the tables that are relevant for you
            where table_name in ('TAB_A', 'TAB_B', 'TAB_C') )loop  <<TableLoop>>

   my_seq_create_stmt := 'CREATE SEQUENCE '||'SEQ_'||i.table_name||'_ID ' 
                         ||CHR(13)||' START WITH 1 INCREMENT BY 1 NOCYCLE ';

   execute immediate my_seq_create_stmt;
   my_trigger_create_stmt := 'CREATE OR REPLACE TRIGGER '||'TRG_'||i.Table_name||'_ID_BI '||' BEFORE INSERT ON '||i.table_name||' FOR EACH ROW '
                    ||CHR(13)||'BEGIN '
                    ||CHR(13)||'  SELECT '||'SEQ_'||i.table_name||'_ID'||'.NEXTVAL '
                    ||CHR(13)||'  INTO   :new.SN '
                    ||CHR(13)||'  FROM   dual; '
                    ||CHR(13)||'END; ';

   execute immediate my_trigger_create_stmt;
 end loop TableLoop;
end;
/

-- test the triggers and the sequences
insert into tab_a (otherfield, date_field) values ('test 1',sysdate);
insert into tab_a (otherfield, date_field) values ('test 2',sysdate); 

commit;


Select * from tab_a;