Understand the Role of Triggers in SQL Server

Introduction

SQL Server is a database server deployed by Microsoft. It is one of the database management systems, which uses T-SQL and ANSI SQL as the primary query languages. Mostly, all might have come up with the triggers in SQL server. The triggers are the helping hand for the database designers. Let us go through triggers and see the different types of triggers and what important role do they play in SQL Server.

What Is Actually A Trigger?

A trigger is a store procedure, which performs certain actions in the database. It is an object of database, which operates automatically. Triggers helps in maintaining the audit file, make changes to data, etc. Triggers are run before or after the commands such as; INSERT, DELETE and UPDATE. Remember the triggers are applied to the tables and one trigger for the table.

The triggers evaluate all the data that are been build using both DML and DDL commands. It is useful in auditing a server, to conserve the integrity of data, to control all the server operations, etc. There are many other uses for the database triggers.

Uses of Triggers in SQL Server:

Some of the well-known uses of triggers are:

1. Derives Column Values:

To get the total sales of the customer, triggers are used. It helps to get the column values and for total value AFTER trigger is used.

2. Implement The Business Rule:

The INSTEAD OF trigger is used to wiretap the left out DML operations. This trigger informs the stage of each customer; tells the modification made, if any.

3. Auditing

The AFTER trigger improves the auditing. It is found helpful to track the changes made in the table of the database.

There are different triggers used in each of the purposes. In coming segments, you will come across each of the mentioned triggers.

Forms of Triggers:

Triggers are mainly of two forms:

1. After triggers
2. Instead of triggers

Let us see one each.

After trigger:

As the name suggest ‘After’, these run after the DML commands. Since there are three commands, there are three ‘After’ triggers.

• AFTER INSERT trigger
• AFTER DELETE trigger
• AFTER UPDATE trigger

Let’s see one of the ‘After’ trigger.

‘AFTER INSERT’ trigger;

A new record is added to the Employee_Demo_Audit table.

‘AFTER DELETE’ trigger is given when there is a delete on tables.

Let us see the working of ‘AFTER DELETE’ trigger.

A table is created and an audit;

Trigger is created;

In this, the data of the deleted record is taken from the logical deleted table and is inserted to the audit table. There is a record created in the audit table as shown:

Instead of Trigger:

Instead of trigger is used as an intercept. There are three types instead of triggers.

• INSTEAD OF UPDATE trigger
• INSTEAD OF INSERT trigger
• INSTEAD OF DELETE trigger

One of the ‘Instead of’ trigger- INSTEAD OF DELETE trigger;

The record from the table whose Emp_Sal>1200 is not been deleted. If the deletion is done then, the trigger will push back the transaction.

To delete;

There occurs an error as;

Actually, the record will not be deleted.

Types Of Triggers :

There are four types of triggers in SQL Server. Let us see each one of them.

DDL Trigger

Triggers are executed on the DDL commands such as; CREATE, ALTER and DROP.

NOTE: The main point to be noted is, only FOR/AFTER clauses can be used in DDL triggers, not INSTEAD OF clause.

These triggers are used in controlling and for observing the actions done in the server, for auditing the operations, etc.

DML Triggers

In this, the triggers are applied to the DML commands like; INSERT, UPDATE and DELETE. These are of two types: ‘After” and ‘Instead of’ triggers which we have seen before.

CLR Trigger

These are special kinds of triggers and depend on Common Language Runtime (CLR). It is done in .net framework, language like; C#, Visual Basic, etc.

Logon Trigger

Logon trigger are fired when the LOGON event in SQL Server is up. The LOGON event occurs as the reason of user session being established along with SQL Server, usually made after authentication is finished. But, if the authentication is failed, these triggers are not carried out.

Syntax

CREATE TRIGGER trigger_name
ON ALL SERVER
[WITH ENCRYPTION]
{FOR|AFTER} LOGON
AS
sql_statement [1...n]

Risks in Triggers:

Triggers are risky as we approach them closely. They can cause many problems and some of the reasons why it is called risky are:

• It causes other triggers to be fired along.
• Triggers are very hard to troubleshoot.

Using relationship instead of triggers can help in great way.

Covering Up:

Triggers are store procedure, which helps the database designers. Although it is helpful, we have also come across the risks in triggers. Hope a detailed view of trigger has been opened in front.