INSTEAD OF Triggers and CASCADE paths

Assuming you must use INSTEAD OF triggers, and AFTER triggers are not an option, the best approach is to a) tightly control the schema so that you can b) script the INSTEAD OF triggers out in a regular fashion to implement the CASCADE DELETE and whatever other operations you need.

Create the FK constraints as before, but w/out any cascade behavior. In the FK name, use some convention to indicate what kind of cascade behavior and custom behavior should occur, eg:

• FK_UC_DC_Table1_Table2 -- update cascade, delete cascade
• FK_UC_DN_Table1_Table3 -- update cascade, delete set null

Use whatever makes sense, but do create the FKs, they are useful metadata for code generation, and you can use the FK names to record directives for the code-generator.

I'd then take it a step further and isolate these tables in their own schema. They won't behave the same way as other tables, and they will be more buggy at first as you test and fine-tune the code generation. Best to keep all this quarantined, and easily identifiable by a common container.

A dedicated schema will also inform anyone modifying the data that different rules and behavior apply.

The standard best-practice is to define INSTEAD OF triggers on views, not on tables.

If you have to use a trigger on a FK update/delete you are best to use AFTER, since it will always execute.

If you want to cancel the cascading actions but retain the FKs, just set the FK action to NO ACTION.