Posted By

thecrumb on 01/23/08


Tagged

sql Oracle


Versions (?)

Generate the SQL necessary to create triggers (Oracle)


 / Published in: SQL
 

Generate the SQL necessary to create triggers that use the sequences listed above. NOTE: This particular script assumes that the tables all start with ‘tbl’. It may need to be modified to work with different naming conventions.

  1. SELECT 'CREATE OR REPLACE TRIGGER trg' || substr (table_name,4,length(table_name)-3) || '
  2. before insert on tbl' || substr (table_name,4,length(table_name)-3) || '
  3. for each row
  4. declare
  5. begin
  6. if (:new.' || substr (table_name,4,length(table_name)-3) || 'id =0) or (:new.' || substr (table_name,4,length(table_name)-3) || 'id is null) then
  7. begin
  8. select tbl' || substr (table_name,4,length(table_name)-3) || '_seq.nextval
  9. into
  10. :new.' || substr (table_name,4,length(table_name)-3) || 'id from dual;
  11. end;
  12. end if;
  13. end;
  14. / '
  15. FROM user_tables

Report this snippet  

You need to login to post a comment.