Wouldn't it be more simple just to enable/disable predefined triggers dynamically? ================================ Dion Cho - Oracle Performance Storyteller http://dioncho.wordpress.com (english) http://ukja.tistory.com (korean) http://sites.google.com/site/otpack (tpack) ================================ 2010/12/6 Sreejith S Nair <Sreejith.Sreekantan@xxxxxxxxxx> > Hi List, > > We have a requirement in generating triggers for a set of configured tables > in our database ( Oracle 10g) . I am thinking to write a procedure which > reads the table names configured and create trigger for each table with a > defined business ( Its just for audit , so the trigger code is same for > table A and B but the name and columns might differ , the basic logic is > same.) > > The problem I am facing is that , in my generic procedure I am not able > to generate the trigger string and execute it using 'EXECUTE IMMEDIATE' as > for some of our tables when all columns and trigger code is appended the > string goes beyond 4000( defined as VARCHAR2) . > > The same will happen even if I use COLLECTIONS,TYPES or any object ? > > Is there any other tricks to accomplish this, other than > > 1. Writing/ Spooling the results to a file and executing this file to > create a trigger ( I don't want to create the trigger source codes and > maintain the file) > 2.Writing individual trigger codes. > > Any tweaks and tricks is appreciated. > > The basic template would be > > CREATE TRIGGER BEFORE EACH ROW > BEGIN > IF INSERTING > INSERT TO SOME TABLE > IF UPDATING > INSERT TO SOME TABLE > IF DELETING > INSERT TO SOME TABLE > END; > / > > > Thank You, > > Kind Regards, > Sreejith Nair > > > > > > > > DISCLAIMER: > > "The information in this e-mail and any attachment is intended only for the > person to whom it is addressed and may contain confidential and/or > privileged material. If you have received this e-mail in error, kindly > contact the sender and destroy all copies of the original communication. IBS > makes no warranty, express or implied, nor guarantees the accuracy, adequacy > or completeness of the information contained in this email or any attachment > and is not liable for any errors, defects, omissions, viruses or for > resultant loss or damage, if any, direct or indirect." > > > > >