Re: Doubts on triggers

ramsunders_at_yahoo.com (R197509) wrote in message news:<1e562f83.0205230225.17b72551_at_posting.google.com>...
> 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.

If a *logical* transaction is causing the rollback segment to increase, than the rollback segment has to increase. You shouldn't commit individidual records, thus splitting up the logical transaction into multiple transactions, to prevent the rollback segment to increase. This measure will increase redolog overhead (as every transaction has overhead) and also you can run into a situation where you are left with an incomplete logical transaction when one of your mini-transactions fail. This is why I call the considered measure symptom-fighting: you start at the wrong end.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Fri May 24 2002 - 11:20:56 CEST