Re: Performance Tip

On Fri, 04 Feb 2000 06:12:35 GMT, you wrote:

>Hi All,
>
>I have recently discovered that before row triggers are up to twice as
>slow as after row triggers, and so after row triggers should be used in
>preference to before row triggers where the choice is otherwise
>indifferent. See the Q&A page on my web site (1st URL below) for more
>info.
>

I'd just like to add

o use BEFORE row triggers ONLY when you need to modify the row values before its inserted (eg: to supply a primary key value from a sequence)

o don't do data validation in a BEFORE row trigger

o do all data validation in an AFTER row trigger.

The reason is that the BEFORE trigger might not be seeing the row as it will ultimately be inserted. Since you can have many BEFORE triggers -- each capable of modifying the column values for that row -- and you cannot control the firing order of these triggers, you should always postpone all validation until AFTER the row has 'stabalized'.

>Regards,
>Steve Adams
>http://www.ixora.com.au/
>http://www.oreilly.com/catalog/orinternals/
>http://www.christianity.net.au/

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Feb 04 2000 - 07:33:03 CST