Posted by Hugo Kornelis on 05/27/05 22:02

On 26 May 2005 17:08:00 -0700, coosa wrote:

>My question now, when i insert multiple records, isn't the trigger
>still fired after each single insert?

Hi coosa,

Only if you use seperate insert statements. If you use something like

INSERT INTO MyTable (Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM OtherTable
WHERE ....

then the trigger will fire once, regardless of whether the number of
rows inserted is zero, one, two or 3,000,000,000.

I stick to my advice: remove the derived column, and use a user-defined
function to calculate the depth when you retrieve data. Or, when you
don't want to do that, check out the suggestion posted by Erland: it
will work fone, even for multi-row inserts, as long as you never insert
both a parent and a child in the same statement (and it's not very hard
to adapt the code to cater for that possibility as well).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]