RE: Trick to generate trigger codes for some of the tables
- From: <Dominic.Brooks@xxxxxxxxxxxxxxxxxxx>
- To: <Sreejith.Sreekantan@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 6 Dec 2010 12:30:15 +0000
4000 is limit for VARCHAR2 column.
32767 for VARCHAR2 variable.
Should be able to use execute immediate against a 32k string.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sreejith S Nair
Sent: 06 December 2010 12:10
To: oracle-l@xxxxxxxxxxxxx
Subject: Trick to generate trigger codes for some of the tables
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."
_______________________________________________
This e-mail may contain information that is confidential, privileged or
otherwise protected from disclosure. If you are not an intended recipient of
this e-mail, do not duplicate or redistribute it by any means. Please delete it
and any attachments and notify the sender that you have received it in error.
Unless specifically indicated, this e-mail is not an offer to buy or sell or a
solicitation to buy or sell any securities, investment products or other
financial product or service, an official confirmation of any transaction, or
an official statement of Barclays. Any views or opinions presented are solely
those of the author and do not necessarily represent those of Barclays. This
e-mail is subject to terms available at the following link:
www.barcap.com/emaildisclaimer. By messaging with Barclays you consent to the
foregoing. Barclays Capital is the investment banking division of Barclays
Bank PLC, a company registered in England (number 1026167) with its registered
office at 1 Churchill Place, London, E14 5HP. This email may relate to or be
sent from other members of the Barclays Group.
_______________________________________________
Other related posts:
