Re: Are Triggers Really that Bad or ....???

Hi Charles,

A trigger is just a piece of code that performs an operation, nothing more. It's main advantage is that this code can be fired off automatically upon an event occurring. It has heaps of uses such as complex validation checks, customised auditing, customised replication etc ,etc.

However if the code that is executing is not essential, or if it duplicates something Oracle already provides, or if it's grossly inefficient in what it does, then yes we have an issue. In your example (assuming a row level trigger), by inserting all this information into another table, you are basically doubling the work that the original DML was performing. If the trigger causes double the work, you can expect that overall it would therefore take twice as long.

Note in the original's posters example, he basically wanted a trigger to automate what the application was doing anyway, therefore no additional overhead as such.

The issue with triggers is not so much that they're an issue per se, but if they are inefficient or unnecessary, then because of the frequency that they get executed, can cause awful performance problems (and that the cause of these performance problems may not always be that obvious to detect).

Cheers

Richard
<Charles> wrote in message
news:23uvju0utuoikvp9n1ncr9b5l9ta6pbmpk_at_4ax.com...
> On Thu, 25 Jul 2002 09:42:37 +0100, Norman Dunbar
> <Norman.Dunbar_at_lfs.co.uk> wrote:
>
> >Hi Ronnie,
> >
> >Oracle uses triggers in the database itself - so there really isn't a
> >problem.
> >I've never come across any problems with triggers - except 'mutating
> >table' errors - and that was because I wrote a bad trigger :o)
> >
>
> Does the size or amount of the data being updated by the trigger
> matter? The reason I wonder is that we use open interface tables
> between our various ERP systems. The trigger in question (I don't have
> access to that groups code) had to be removed because it doubled the
> time for their dataload. Basically they were using it to copy the
> entire transaction to the OI table on any insert or update. Each row
> consisted of 23 columns of data, about half of the columns populated
> with part numbers, price, etc. It is an 8i database on HP/UX if that
> matters.
Received on Thu Jul 25 2002 - 08:54:08 CDT