ID Basado en trigger y secuencia para todas las tablas - Oracle

En ocasiones todas o la mayoría de las tablas de una base de datos requieren que su ID sea sacado de un trigger el cual llama una secuencia, por lo que tendremos que crear los trigger y las secuencias para esas tablas las cuales pueden ser muchas.

esto nos lleva a 2 situaciones, en donde la base de datos no tiene ningún dato, y en donde esta ya tiene algunos, por lo que la secuencia no puede empezar desde 1.

Base de Datos sin Datos :P

En el primer caso basta con ejecutar el siguiente bloque pl/sql el cual nos darán el script para crear las secuencias y los triggers, podriamos hacerlo todo en un solo loop, pero queremos que el script de las secuencias nos quede en un bloque y el de los triggers en otro, solo por orden.

BEGIN
  FOR r IN ( SELECT table_name
             FROM user_tables)
  LOOP
    dbms_output.put_line ('CREATE SEQUENCE S_'|| r.table_name || ' MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE'||chr(10)||'/');
  END LOOP;
  
  FOR r IN ( SELECT table_name
             FROM user_tables)
  LOOP
  dbms_output.put_line( 'CREATE OR REPLACE TRIGGER  T_'|| r.table_name ||' before insert on '|| r.table_name ||' for each row'||chr(10)||
  'begin if :NEW.ID_'|| r.table_name ||'  is null then select S_'|| r.table_name ||'.nextval into :NEW.ID_'|| r.table_name || ' from dual; end if; end;'||chr(10)||'/');
  end loop;
END;
/

Lo que hace este bloque de PL/SQL es mostrar el nombre de todas las tablas, y le concatenamos la parte para crear la secuencia y el trigger. Por mantener un estándar, lo cual es importante, todas las secuencias serán S_[nombre de la tabla], y todos los triggers serán T_[nombre de la tabla], también es importante que las tablas conserven un estándar en el nombre de su llave primaria como ID_[nombre de la tabla], en caso de que el estándar sea otro, se debe cambiar y en caso de que no se tenga ningún estándar se debe cambiar luego manualmente cada id que no coincida.

En caso de que tengan muchas tablas y les de pereza cambiar uno por uno, pueden intentar modificar el PL/SQL para que traiga el nombre del ID de la tabla. Con la siguiente consulta sabrán el nombre de la columna que es llave primaria en cada tabla.

select table_name, COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
where CONSTRAINT_TYPE = 'P'
)
ORDER BY TABLE_NAME , POSITION;

Afortunadamente para ustedes :P me toco modificar el código, entonces acá está, aunque solo la parte de los triggers, considerando que ya tienen las secuencias :) Y pues... si tienen alguna tabla con llaves compuestas... les sacara error. tienen que exluirlas de la consulta del ciclo.

declare
ID_TABLA VARCHAR(30);
begin

for r in (
  SELECT TABLE_NAME FROM USER_TABLES tabla
  )
LOOP

-- consultamos el nombre de la columna que es la llave primaria
select  COLUMN_NAME
INTO ID_TABLA
FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME  
  FROM USER_CONSTRAINTS  
  where CONSTRAINT_TYPE = 'P'  
)  AND table_name = r.TABLE_NAME;

dbms_output.put_line( 'CREATE OR REPLACE TRIGGER  T_'|| r.table_name ||' before insert on '|| r.table_name ||' for each row'||chr(10)||  
  'begin if :NEW.'|| ID_TABLA ||'  is null then select S_'|| r.table_name ||'.nextval into :NEW.'|| ID_TABLA || ' from dual; end if; end;'||chr(10)||'/');  

end loop;
END;

Base de Datos con Datos :P

Podemos realizar el mismo procedimiento, solo que antes de crear el script de la secuencia traeremos el máximo id de la tabla, aquí es importante el estándar. de momento no les muestro como quedaría el Script por que debo volver al trabajo, y el primer caso me sirve para lo que tengo que hacer.

Pronto lo pondré y si no, me acuerdan :P　Mientras tanto espero que les haya servido la primera parte :P y estén celebrando como Kashiyuka por que se ahorraron el tiempo de crear todos esos triggers, entonces... usen parte de ese tiempo para ver este vídeo :D