Instead of Triggers - Can they coexist with regular Triggers

Question

Can Instead Of triggers co-exist with regular triggers? If so, are there any potential issues we should be aware of?

Answer 1

INSTEAD OF triggers can coexist with normal triggers. I've done this a good bit.

INSTEAD OF triggers have numerous potential issues, mainly around the fact that what they replace the normal insert/update/delete behavior with whatever you define. A developer may think nothing of UPDATE User SET Address = 'foo' WHERE UserID = 4, but if your trigger is using that as a hook to touch a dozen authentication tables and maybe talk to a server around the world, you've bought yourself a lot of potential confusion.

Keep the behavior of these triggers inline with expected behavior of IUD statements. Don't do too much.

INSTEAD OF triggers are a very powerful tool, easily misused. Use them appropriately and thoughtfully.

Answer 2

I haven't found anything to be concerned about with respect to using both INSTEAD OF and AFTER (AKA FOR) triggers at the same time. The main issues with INSTEAD OF triggers are:

• You can only have one INSTEAD OF trigger per operation, per table;
• They can mess with OUTPUT INTO clauses (i.e. you'll get identity values of 0);
• If you make any schema changes to the table, things may mysteriously break at some point in the future if you weren't careful to maintain the trigger.

None of these caveats are related to AFTER triggers, so you don't really have anything to worry about in that regard. Although I will say that it's more common to write INSTEAD OF triggers on views as opposed to tables, because there's less chance of them interfering with table operations. They were primarily designed as a tool to help you create insertable/updatable views.

Anyway, you'll be fine if you're careful, but I would still recommend against using an INSTEAD OF trigger unless you actually need to, because they make ordinarily simple operations harder to reason about.