SA Developer .NET

Can an AFTER TRIGGER be created on a View?

---

A Program is only Complete when the Programmer Dies

Sorry, correction. Only instead of triggers on a view. 

You can put the after trigger on the base table that the view references.

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

The scenario is that the base view of the table has 2 INSTEAD of TRIGGERS; one for INSERT and the OTHER for UPDATE.

The AFTER TRIGGER resides on the base table but when data is INSERTED, it encounters a SQL Error (I'll post the exact error shortly)

I've just specified that the AFTER TRIGGER fires LAST (SP_SETTRIGGERORDER) and I'm currently testing it.

I'll let you know if that works.

 

---

A Program is only Complete when the Programmer Dies

Hmm....not my favorite kind of problem.

I can't get it to error in the Dev Environment, this only seems to be happening in production.

I need to test live on Monday to check if the Trigger Order helped.

---

A Program is only Complete when the Programmer Dies

Trigger order's irrelevance in this case. The instead of trigger fires before the insert/update happens and may override the insert/update completely (the whole point of instead of triggers). The after trigger fires after the insert/update has completed, but before the transaction is committed.

Trigger order only matters if you have more than one after trigger on the table. You can set which of the after triggers fires first and which fires last.

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!