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

  • From: Sreejith S Nair <Sreejith.Sreekantan@xxxxxxxxxx>
  • To: Harel Safra <harel.safra@xxxxxxxxx>
  • Date: Mon, 6 Dec 2010 17:47:06 +0530

Hi  - Yes, 

Have looked at FGA and some other options as well.

Thank You,

Kind Regards,
Sreejith Nair




From:   Harel Safra <harel.safra@xxxxxxxxx>
To:     Sreejith.Sreekantan@xxxxxxxxxx
Cc:     oracle-l@xxxxxxxxxxxxx
Date:   12/06/2010 05:44 PM
Subject:        Re: Trick to generate trigger codes for some of the tables



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."
> 
> 
> 
> 






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."




Other related posts: