Trick to generate trigger codes for some of the tables
- From: Sreejith S Nair <Sreejith.Sreekantan@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 6 Dec 2010 17:40:24 +0530
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."
Other related posts:
