On 11 May 1999 EVL.MISMDS@RANE.sprintrpg.ems.vsnl.net.in wrote:

> Dear sir,
>          How are you?It is after a long time we are mailing to you.
>          One reason is that we did not want to disturb you frequently.
>          But to clear our doubts in Oracle,we have no other substitute
>          for you.( I include even the Oracle support we have ).This is a
>          request from us.

Sorry to hear that support is not as helpful as you would like them to be.
Be persistent with them - they should work all of your problems through to
your satisfactory resolution. I will answer your questions below:

>          The queries for which we are not able to find
a solution
>          1) As of oracle7, we cannot create more than one trigger
>             based on an event for a table.But we were able to create
>             n number of triggers for a single table for a single event
>             (for example Before insert event) and when the event occurs,
>             all of the triggers created, fires.
>             What is the basic reason for Oracle supporting this concept?

I assume that you are asking why Oracle supports multiple triggers for an
event on a table. The main reason is that it makes it easier to
selectively turn individual triggers on and off to get appropriate actions
to happen or not. If you were constrained to one trigger per event (before
Oracle8) then you would have to code ALL conditions into one trigger and
it would be more difficult to modify and maintain the code.

>          2) How is the performance of Database is reduced when we create
>             more back end triggers?

It depends on what the trigger does. Basically, for each record that you
insert/update/delete (depending on the trigger), the database performance
will degrade by how long it takes for the trigger code to be processed.
For example, if you COMPUTE statistics after every record entered in a
million-record table, your performance will be horrendous. On the other
hand, if you ensure that one field is converted to uppercase before being
inserted, your performance will be mostly unaffected.

>          3) Is there any term like TEMPORARY TABLES in Oracle? I was told
>             that we can create temporary tables in oracle which will be
>             existing for the current instance. If Database is shutdown and
>             started, these tables will disappear. If this statement is true,
>             please give us the syntax of creating such tables.

In Oracle 8.0 and earlier, I am not aware of any such tables. There are
CURSORS in PL/SQL (Oracle 8,7,6) that act as temporary tables. You can
define them, open them, fetch records from them, and so on. They are
removed when you close the cursor. In Oracle 8i, there are temporary
tables, but I do not have the syntax to create them for you.

>          We are using Oracle for our day to day transactions and we are
>          attracted by this excellent RDBMS and we want to learn more and
>          more and always keep learning Oracle.If this is not disturbing,
>          we will be frequently mailing to you.
>          Thanking you.
>          From
>             ( P.B.Neelagandan And N.Hariharan )
You are welcome to email me as many questions as you like. I get many a
day and cannot guarantee that I can answer them for you if I become busy.
But feel free to try!

Best of luck,

-Ari Kaplan
Independent Oracle DBA Consultant

<-> For 295+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->

Back to Ari Kaplan's Home Page 16" height="18"> Back to Ari Kaplan's Home Page