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

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Tue, 7 Dec 2010 09:46:51 +0900
Message-ID: <AANLkTi=DdfqZCf_M_deuPSbxwu2VJrB0zn0E953uR4-f_at_mail.gmail.com>

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_at_ibsplc.com>

> 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

Received on Mon Dec 06 2010 - 18:46:51 CST