Advanced Search

Return to Snippet

Revision: 4848
at January 23, 2008 14:17 by thecrumb
Initial Code 
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
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
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.

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Generate the SQL necessary to create triggers (Oracle)

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
sql, Oracle

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL