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:
- 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.
- 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?
- 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.
- 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.
- 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.
