Re: Using triggers for data validation

I think Steve is probably a little more precise in his statement. But there are definitely cases where using before-row triggers is more expensive that using after-row triggers to do the same job. The cost appears as extra current mode gets and redo log generation.

Thomas Kyte has a demonstration of this in his book "Expert One on One: Oracle"

However, you do need to consider carefully what your triggers are trying to do. For example, (extreme case for the purposes of making a point) if the trigger is supposed to cause a failure in a larger percentage of cases then it is better to do:

    before row trigger - fail
than

    update row
    after row trigger - fail
    roll back update

General principle, therefore (which may be subject to change in future versions of Oracle). All other things being equal, an after row trigger is probably cheaper than a before-row; but there are inevitably cases where it doesn't really matter, and special cases where it is not true.

Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases See http://www.jlcomp.demon.co.uk/book_rev.html

For latest news of public appearances
See http://www.jlcomp.demon.co.uk

Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research.

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 17 August 2001 23:16

|In Steve Adams sample of performance report there is a suggestion to
use
|trigger after row update instead of before row update because it
will halve
|the number of redo records created for row locks. Anybody has
diferent
|opinion?
|
|Alex Hillman
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Hillman, Alex
| INET: Alex.Hillman_at_usmint.treas.gov
|
|Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
|San Diego, California -- Public Internet access / Mailing
Lists
|--------------------------------------------------------------------
|To REMOVE yourself from this mailing list, send an E-Mail message
|to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
|the message BODY, include a line containing: UNSUB ORACLE-L
|(or the name of mailing list you want to be removed from). You may
|also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).