/ 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.
NOTE: This particular script assumes that the tables all start with ‘tbl’.
It may need to be modified to work with different naming conventions.
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
SELECT 'CREATE OR REPLACE TRIGGER trg' || substr (TABLE_NAME,4,LENGTH(TABLE_NAME)-3) || ' before insert on tbl' || substr (TABLE_NAME,4,LENGTH(TABLE_NAME)-3) || ' for each row declare begin 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 begin select tbl' || substr (TABLE_NAME,4,LENGTH(TABLE_NAME)-3) || '_seq.nextval into :new.' || substr (TABLE_NAME,4,LENGTH(TABLE_NAME)-3) || 'id from dual; end; end if; end; / ' FROM user_tables