Re: Doubts on triggers

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0205220303.a9da2f1_at_posting.google.com>...
> ramsunders_at_yahoo.com (R197509) wrote in message news:<1e562f83.0205212255.6c066ceb_at_posting.google.com>...
> > Hi,
> > We are facing a situation where we need to use triggers to perform
> > a set of complex operations, the details of which are as follows.
> > We have a table on which the triggering events would be Insert and
> > Update. The event action would insert a few records in three other
> > tables. We need the trigger event to be fired for every row affected.
> > The following are the doubts that we have:
> > 1. What is the type of trigger that we should use, viz. Before or
> > After. We are not very clear on what the difference between the two
> > is.
> > 2. If there are bulk inserts or updates on the table, what are the
> > likely constraints that we may face with respect to performance. If we
> > are do a bulk update on the table, do trigger events for each of the
> > rows run in parallel?
> > 3. Is there some mechanism using which we could commit he
> > transaction from the trigger. This is because we might run into the
> > Rollback Segment going full in case the number of rows updated is very
> > large in number.
> > 4. As we understand, the size of the trigger action body can be
> > upto 32K. If we call a stored procedure from the trigger event, would
> > the size of the stored procedure also come into account.
> > 5. Is there a method by which we can cause the insert to happen
> > even if the trigger action fails. I guess one way would be is to
> > handle all exceptions in the trigger action body. Are there any other
> > means to achieve this?
> > Would really appreciate it if you could point out any other
> > short-comings that you might have experienced from using triggers too.
> >
> > Regards,
> > Ram.
>
> 1 if you don't need to compare :new and :old after can be used
> 2 The trigger will fire for every row affected. This means NO
> parallelization
> 3 There is no such mechanism. Rather you should size your rollback
> segments appriopately
> 5 Convert your updates into an autonomous transaction
>
> I don't think there are much shortcomings to triggers.
> From the nature of your questions, however, it looks like you prefer
> symptom-fighting instead of designing robust applications.
> One shouldn't cut down on rollback segment size. Disk is cheap.

Sybrand,

   Thanks for your replies.
   We were just trying to fill the gaps in our understaning of triggers. From what we read, we weren't very sure if it might be advisable to perform a lot of operations as part of a trigger action. That's why we started thinking on the lines of what problems we could encounter in case we design based on triggers. I didn't quite get what you really meant by "prefer symptom-fighting instead of designing robust applications".

Regards,
Ram. Received on Thu May 23 2002 - 12:25:08 CEST