Advanced Search

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.

Expand | Embed | Plain Text

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.