SQL World: TRIGGERS

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updating of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action on the table that they are assigned to.

Types of Triggers:

1) DDL Triggers

We can create triggers on DDL statements (like CREATE, ALTER, and DROP).

We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements.

DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.

2) DML Triggers

We can create triggers on DML statements (like INSERT, UPDATE, and DELETE) DML Triggers are of two types

After Trigger (using FOR/AFTER CLAUSE)

This type of trigger fires after SQL Server finishes the execution of the action successfully that fired it.

Example: If you insert row in a table then the trigger with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the row insertion fails, SQL Server will not fire the After Trigger.

Instead of Trigger (using INSTEAD OF CLAUSE)

This type of trigger fires before SQL Server starts the execution of the action that fired it. It does differ from the AFTER trigger. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.

Example: If you insert row in a table then the trigger with the insert event on this table will fire before the row passes all the constraints. If the row insertion fails, SQL Server will fire the Instead of Trigger.

3) CLR Triggers

CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#.

4) Logon Triggers

Logon triggers are special type of trigger that fire when LOGON event of SQL Server is raised. This event is raised when a user session is being established with SQL Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger such as error messages, will be redirected to the SQL Server error log.

Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.

Advantages of Triggers:

Triggers can be used as an alternative method for implementing referential integrity constraints.
By using triggers, business rules and transactions are easy to store in database and can be used consistently even if there are future updates to the database.
It controls on which updates are allowed in a database.
When a change happens in a database a trigger can adjust the change to the entire database.
Triggers are used for calling stored procedures.
SQL triggers provide an alternative way to run scheduled tasks. By using SQL triggers, you don’t have to wait to run the scheduled tasks because the triggers are invoked automatically before or after a change is made to the data in tables.
SQL triggers are very useful to audit the changes of data in tables.

Disadvantages (Problems) of Triggers:

It is easy to view table relationships, constraints, indexes, stored procedure in database but triggers are difficult to view.
Triggers execute invisible to client-application. They are not visible or can be traced in debugging code.
It is hard to follow their logic as it they can be fired before or after the database insert/update happens.
It is easy to forget about triggers and if there is no documentation it will be difficult to figure out for new developers for their existence.
Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower.
Triggers work over tempdb, so extensive use of them needs to be taken into account when thinking about tempdb