Sub ms sql. It does not allow to hang more than one trigger of the same type (for example, Instead of insert) on a table. Can this be fixed? I do not want to push a bunch of checks in one trigger.

  • write a development letter =) - Serge Esmanovich
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

In SQL Server, there are two parameters that determine the behavior of triggers:

AFTER .

The trigger is executed after the successful execution of the commands that caused it. If the commands for some reason cannot be successfully completed, the trigger is not executed. It should be noted that data changes as a result of the user’s request and the trigger are executed in the body of a single transaction: if a trigger rolls back, user changes will be rejected. You can define several AFTER triggers for each operation (INSERT, UPDATE, DELETE) . If the table provides for the execution of several AFTER triggers, then using the sp_settriggerorder system stored procedure, you can specify which of them will be executed first and which last. By default, in SQL Server all triggers are AFTER-triggers.

INSTEAD OF .

The trigger is invoked instead of executing commands. In contrast to the AFTER-trigger INSTEAD OF-trigger can be defined both for the table and for viewing. For each operation INSERT, UPDATE, DELETE, you can define only one INSTEAD OF trigger.

Here is a link to the source.

In order not to overload the "INSTED OF" trigger, I would spread the logic of the stored procedures and call them in the trigger itself.