Re: Doubts on triggers

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.

--
Regards

Sybrand Bakker
Senior Oracle DBA