Re: Trick to generate trigger codes for some of the tables

Harel Safra
Sent from my phone
On Dec 6, 2010 2:13 PM, "Sreejith S Nair" <Sreejith.Sreekantan_at_ibsplc.com> 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."
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l