SQL Server Coding Standards: Working with Triggers

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Prepared by Bihag Thaker

Prefix the User Defined Function Name with ‘trg_’.

Try to include only letters in identifiers by using Pascal Casing and avoid the use of special characters in identifiers. Underscore (_) character may be used for element separation in an identifier.

Here element means Application Prefix, type of Operation, Table Name or Entity Name etc. However, word separation in single element should be achieved with PascalCasing only.

Use proper, meaningful and self-explanatory identifiers for user-defined functions. Following naming convention should be used for trigger:

‘trg_’ + + ‘_’ + + ‘_’ +

Here, is the name of the table on which the trigger is defined. is the type of trigger. It can be either ‘AFTER’ or ‘INSTEADOF’. is the underscore (_) separated list of operations for which trigger is defined. It can be INSERT, DELETE or UPDATE.

Some of the examples are:

1 2	trg_tblOrders_After_DELETE trg_tblOrders_After_INSERT_UPDATE

If there is some alternative solution to using triggers then avoid creating frequent triggers on the table as they degrade the performance of the DML statements.

Check if the same functionality can be achieved using the constraints as constraints are faster than triggers. Only use triggers for complex business logic whose implementation is not possible by constraints.

If the trigger is specified for INSERT, UPDATE or DELETE, do not use OUTPUT clause in their respective DML statements that fire the trigger.

Sample Trigger Code Snippet: