Have you looked into oracle's built in auditing? Harel Safra Sent from my phone On Dec 6, 2010 2:13 PM, "Sreejith S Nair" <Sreejith.Sreekantan@xxxxxxxxxx> wrote: > 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." > > > >