--Create a new sequence and supporting trigger
--The trigger will override any inserts with hard coded id into the table, forcing use of sequence.
DECLARE
seq_exists NUMBER :=0;
BEGIN
--Drop sequence if it exists
SELECT COUNT(*) INTO seq_exists FROM USER_SEQUENCES WHERE SEQUENCE_NAME = '<TABLE_NAME>_SEQ';
IF seq_exists > 0 THEN
EXECUTE IMMEDIATE('DROP SEQUENCE <SCHEMA_NAME>.<TABLE_NAME>_SEQ');
END IF;
--Create sequence
EXECUTE IMMEDIATE('CREATE SEQUENCE <SCHEMA_NAME>.<TABLE_NAME>_SEQ MINVALUE 0 START WITH 10000 INCREMENT BY 1');
END;
/
--Create trigger
CREATE OR REPLACE TRIGGER TRG_<TABLE_NAME>_SEQ
BEFORE INSERT ON <TABLE_NAME>
FOR EACH ROW
BEGIN
:NEW.<KEY_COLUMN> := <TABLE_NAME>_SEQ.NEXTVAL;
END TRG_<TABLE_NAME>_SEQ;
/
Related