Generate the SQL necessary to create triggers (Oracle)


/ Published in: SQL
Save to your folder(s)

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.


Copy this code and paste it in your HTML
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.