terça-feira, 19 de agosto de 2014

About triggers, after and instead of

The following picture shows an overview of data flow when using triggers in SQL Server. More details and more arrows flows were not shown to be a simple diagram. Some anottations about:

  • Truncate table statement does not fire triggers
  • WriteText does not fire triggers, with or without logging
  • Old versions of SQL need set the DISALLOW RESULTS FROM TRIGGERS to 1, due to avoiding returning results when running a trigger
  • Use sp_settriggerorder to order the first and last AFTER trigger when using many of them in the same object. Other triggers between the last and first will run randomly
  • SERVER scoped DDL triggers are in \Server Objects\Triggers folder
  • DATABASE scoped DDL triggers are in \Programmability\Database Triggers folder
  • LOGON trigger run only when authentication is successfull
  • Inside an Instead Of trigger is not necessary to rollback a command, because its underlying commando (the original one) is ignored. If needed execute the original statement inside the trigger. The statement won't call the instead off trigger again (recursively), but the After triggers will run, whether exists.
  • Code triggers carefully because they can call each other recursively when changing other tables than the underlying table (where the trigger is attached).

Nenhum comentário:

Postar um comentário