GilaMonster (2/2/2009)

---

Nested triggers means that an update to a table in a trigger can fire triggers on that table, providing it's not the same table. So, say you have Table1 that has an update trigger that inserts into table2. Table2 has an insert trigger that nserts into table3. If nested triggers is on and you update table1, then the trigger on table1 will insert into table2, will fire that trigger and will insert into tbl3. If it's off, the insert trigger on table2 won't fire.

Recursive trigger controls if a trigger may fire itself due to data changes. If it's off, your trigger will not be called due to the updates that it does.

TheSQLGuru (2/2/2009)

---

2) is the join column a primary key or unique index value? Looks like cartesian product if not.

TheSQLGuru (2/2/2009)

---

Other than the ol' trigger-firing-itself-due-to-update-inside-trigger-ploy, 2 other questions:

1) any OTHER triggers on this table?

2) is the join column a primary key or unique index value? Looks like cartesian product if not.

Marios Philippopoulos (1/31/2009)

---

I have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads!

There are two puzzling aspects about this:

(1) Only about 30,000 rows are being updated in the base table (as can be seen in the exec plan)

(2) The dominant operator (86% of total workload) is an index seek on an index that contains a seemingly unrelated column (a column that does not appear anywhere in the trigger body).

I have captured the number of logical reads and the execution plan by polling the sys.dm_db_exec_requests DMV once a minute.

How can such a huge number of logical reads of the trigger be reconciled to the relatively modest number of rows updated in the underlying table?

TheSQLGuru (2/3/2009)

---

Given the ACTUAL plan, I can't see what your issues are with the number of reads:

1) The clustered index update is 1.46M rows, NC index update 2.93M rows.

2) You are doing a table scan of 1.46M row inserted and 39.9M row updtable

3) You are hash matching the above

4) then doing a sort of the hash result

No idea where your statement of only updating 30K rows is coming from, but it is irrelevant. THAT query you gave the plan for is a beast and that is all there is to it. :)

They will embed the update code directly in the sprocs where the base table is itself updated.