create pl/sql trigger insert squence on all tables-CodePudding

i want to create a trigger insert sequence i created to apply on all primary key on tables ,

CREATE SEQUENCE HR.PRIMARY_KEY
  START WITH 300
  INCREMENT BY 10
  MAXVALUE 99990
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

create or replace trigger increment_pk_trigger 
before insert 
ON schema
--FOR EACH ROW

DECLARE
   CURSOR get_pk_CURSOR IS
   select a.constraint_name, b.column_name, a.table_name
    from user_constraints a, user_cons_columns b
    where a.constraint_type = 'P' and a.constraint_name = b.constraint_name;


BEGIN
    FOR V_RECORD IN get_pk_CURSOR LOOP
                    EXECUTE IMMEDIATE 'insert into '||V_RECORD.TABLE_NAME||' :new.V_RECORD.column_name := primary_key.nextva ';
        END LOOP;
END;

my problem i cannot get what will table_name will be i tried to make it on database|schema but not work

CodePudding user response：

This is not a requirement that makes sense so you realistically can't.

It would be very weird to have a single sequence as the source for the primary key on every table-- there would end up being quite a bit of contention on that single sequence. It would be much more normal to create one sequence per table or (assuming a recent version of Oracle) to simply declare the primary key as an identity column.

If you really wanted to, you could write some code that dynamically generated triggers for every table in the schema. Something like this (assuming that every table has a single column primary key and that you really want to use the same sequence to generate the primary key on every table despite the performance impact)

begin
  for pk in (select a.constraint_name, b.column_name, a.table_name
               from user_constraints a, user_cons_columns b
              where a.constraint_type = 'P' 
                and a.constraint_name = b.constraint_name)
  loop
    execute immediate 'create or replace trigger trg_pk_' || pk.table_name ||
                      '  before insert on ' || pk.table_name ||
                      '  for each row ' || 
                      'begin ' ||
                      '  :new.' || pk.column_name || ' := primary_key.nextval; ' ||
                      'end; ';
  end loop
end;