Re: statement level trigger vs. row trigger

dario wrote:

> Statement level trigger fires once per statment, not transaction.
>
> "Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3CC6C9A7.5EDF4FC4_at_exesolutions.com...
> > Katherine wrote:
> >
> > > actually, your question is quite appropriate since you have to hunt for
> the
> > > answer. check out this link:
> > > http://technet.oracle.com/doc/server.815/a68003/01_13dbt.htm#7445
> > >
> > > The difference between a rowlevel and a statement trigger is that the
> > > statement trigger allows one event to take place, such as writing to a
> log
> > > table with just one entry for the trigger event. The statement level
> > > triggers are useful for performing validation-type actions where you
> want to
> > > track that some event took place. in the example on the link, the
> salary
> > > of 5 employees in dept 20 was increased with a row level trigger. also,
> a
> > > validation record was inserted into a log table of when this trigger was
> > > fired and the data changed.
> > >
> > > The row level triggers fire for each row effected by the trigger. For
> > > example, if there are five rows are affected by the trigger DML, then
> the
> > > trigger fires five times when an UPDATE (for example) is entered.
> > >
> > > The "FOR EACH ROW" option determines whether the trigger is a row
> trigger or
> > > a statement trigger.
> > >
> > > i think you'll find an adequate explanation of mutating tables on the
> link
> > > above.
> > >
> > > hope this helps
> > >
> > > "tshen" <tshen01_at_hotmail.com> wrote in message
> > > news:gplx8.47730$7e5.14622489_at_news02.optonline.net...
> > > > hi, guys:
> > > >
> > > > Can anybody tell me the diff between statement level trigger and row
> > > > trigger? and
> > > > why cann't do the "select" on mutating tables, thanks.
> > > >
> > > > tshen
> > > >
> > > >
> > > >
> > > >
> >
> > I would disagree with your explanation of statement versus row level
> triggers. A
> > statement level trigger fires one per transaction. You can do 1,000
> inserts and
> > the trigger will fire once for all 1,000 records. The row level trigger
> will
> > fire 1,000 times.
> >
> > Daniel Morgan
> >

Thank you for correcting me. I did use the wrong word. I'll restate.

A SQL Statement that inserts 1000 records, for example:

INSERT INTO mytable
SELECT *
FROM yourtable
WHERE ROWNUM < 1001;

Will fire the trigger once. A row level trigger will fire 1000 times.

Daniel Morgan Received on Thu Apr 25 2002 - 14:41:06 CDT