GilaMonster (2/1/2009)

---

Yes, absolutely. Providing he traces the correct event. I seem to recall there are about 6 events relating to the exec plan and I can't recall offhand which produces what. BoL should say.

Marios Philippopoulos (2/2/2009)

---

Wow, that was something I didn't know, but makes sense the way you put it.

Not that I don't believe you, but can you post a link that mentions this?

GilaMonster (2/2/2009)

---

I don't have one offhand (and I'm sitting at the airport), but it's easy to check. Open the exec plan, find any joi, seek or scan operator and look at the tooltips. If you see things like 'Actual rows', 'Actual IO cost', etc then it's an 'actual' plan with run-time information. If you don't then it's essentially an estimated plan.

Not on this exactly, but - http://sqlinthewild.co.za/index.php/2007/09/04/execution-plans-estimated-vs-actual/

Peso (2/2/2009)

---

No need to check for UPDATE, DELETE or INSERT.
Trigger is designed for UPDATE.

Also, the trigger is nesting itself by updating the table for which the trigger ís triggering on...

Use this

CREATE TRIGGER [dbo].[TG_U_UpdTable]
ON [dbo].[UpdTable]
AFTER UPDATE
AS

IF UPDATE(db_updateDate) OR UPDATE(db_updateBy)
RETURN

UPDATE d
SET d.db_updateDate = GETDATE(),
d.db_updateBy = SYSTEM_USER
FROM dbo.UpdTable AS d
INNER JOIN inserted AS i ON i.UpdTableOID = d.UpdTableOID

IF UPDATE(db_updateDate) OR UPDATE(db_updateBy)
	RETURN

Peso (2/2/2009)

---

The trigger is updating the same base table on where the trigger is resided.
So when the trigger updates the two columns, the same trigger is fired again!
And again.. And again...

Marios Philippopoulos (2/2/2009)

---

Wow, would the trigger feed on itself that way? I hadn't thought of that...

GilaMonster (2/2/2009)

---

That's only possible if recursive triggers are enabled. It's a database-level option and it's disabled by default.

EXEC sp_dboption 'myDB', 'recursive triggers'

EXEC sp_configure 'nested triggers'