Skip to main content
Using Triggers In SQL

Using Triggers In SQL

Often times you want some event to be triggered automatically when a certain action is performed on a SQL database.

For instance, you might want to create logs for every INSERT operation performed on a database table or you might want to add a tax percentage to an amount entered by a user before storing it in the database.

In such scenarios you have two options:

1) You can first insert data into the table and then execute another query that adds data into the log table; or

2) You can make user of triggers in SQL.

What are Triggers in SQL?

Put simply, a trigger is an action that is automatically performed when a specific type of event occurs on a database.

In MS SQL Server, triggers are saved in the form of stored procedures. These stored procedures are fired whenever either a DML (Data Manipulation Language) event or a DDL (Data Definition Language) event occurs.

DML events include INSERT, UPDATE and DELETE while DDL events include CREATE, DROP and ALTER statements etc.

Triggers can also be implemented on server events such as SHUTDOWN, STARTUP, LOGOFF, LOGON and SERVERERROR.

How Do Triggers Work?

Triggers work in the three simple steps:

  1. Create a trigger on an event.
  2. Execute the event.
  3. Trigger fires

The first step is to create the trigger on some event. There is special syntax for that which we will look at later. Next the trigger event occurs and then finally the associated triggered event automatically fires.

AFTER vs. INSTEAD OF Triggers

There are two types of triggers in MS SQL Server: AFTER and INSTEAD OF.

The AFTER trigger is a type of trigger that fires after an event e.g. INSERT, DELETE or UPDATE has occurred. The other type of trigger, an INSTEAD OF trigger,  executes in place of the event on which the trigger is actually created.

For example, you if you create an INSTEAD OF trigger on a DELETE event, then instead of deleting the actual record, the INSTEAD OF trigger is executed and a record is not actually deleted. Unless of course the deletion of that record is also specified by the trigger. We will see examples of both of these trigger types in the next sections.

NB There is a third type of trigger called a FOR trigger that is far less used that AFTER and INSTEAD OF triggers. In this article, we have focused on AFTER and INSTEAD OF but FOR triggers work in exactly the same way.

Trigger Example

Before demonstrating how triggers are implemented, let us create our database and some tables within the database so that we can actually create triggers on those tables.

Let’s create a database named “studentdb”. Run the following command in your query window in MS SQL Management Studio (”SSMS”):

CREATE DATABASE schooldb;


The above command will create a database named “schooldb” on your database server.

Next, we need to create a “student” table within the “studentdb” database. The student table will have five columns: id, name, age, gender, and total_score. This is not a perfectly normalized data table but more than sufficient for our example. At the moment we just want to see sub-queries in action. To create a “student” table, execute the following:


Now, let’s create a log table. This table will contain log entries created by the trigger. Whenever the student table is modified via an INSERT, UPDATE or DELETE query, a corresponding entry will be entered into the log table.The above query will create a “student” table with five fields.

Execute the following query in your SSMS window to create a log table.

USE schooldb;

CREATE TABLE log

(

log_id INT IDENTITY(1,1) PRIMARY KEY,

student_id INT NOT NULL,

student_name VARCHAR(50) NOT NULL,

action_performed VARCHAR(50) NOT NULL

)


Trigger Syntax in SQL Server

Before, creating a trigger, let us first take a look at the basic syntax of a trigger in MS SQL Server. and go through it line-by-line:

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name

ON { table | view }

 

{ AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

AS

{

BEGIN

TRIGGER Statements here —-

END

}


The first line contains the keyword CREATE, which is followed by schema_name and the name of the trigger. Triggers can be given any name.  You can also use ALTER if you are altering a trigger you’ve already set up.

In the second line, the name of the Table or View on which the trigger is being created is defined.

In the third line, the type of trigger is defined. It can be either “AFTER” or “INSTEAD OF” trigger.

In the fourth line, the event on which the trigger should fire is defined. This can be DML event or DDL event.

Finally, in the sixth line inside the AS statement, the trigger functionality is defined between BEGIN and END keyword.

INSERT TRIGGER (AFTER)

Let us create an INSERT trigger which fires AFTER a record is inserted into the student table of the schooldb database that we created in the earlier section.

After the record has been inserted in the student table, this trigger will fire and will insert an entry into the log table. Trigger will enter student_id, student_name and the “action_perform” data into the log table. Let’s take a look at our trigger.

CREATE TRIGGER [dbo].[Student_INSERT]

ON [dbo].[student]

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @student_id INT, @student_name VARCHAR(50)

SELECT @student_id = INSERTED.id,  @student_name = INSERTED.name

FROM INSERTED

 

INSERT INTO log

VALUES(@student_id, @student_name, ‘record inserted’)

END

 

Let’s see what is happening in the above query.

In the first two lines, we have created a trigger named “Student_INSERT” on the student table.

The third line defines that the type of the trigger is AFTER and it is being triggered by an INSERT event. It means that whenever a new record is inserted in the student table, this trigger will execute.

Finally, let’s look at the statements between BEGIN and END. This is what the trigger will actually do when it executes.

Here we set NOCOUNT ON which returns the number of rows affected. Next, we declared two variables @student_id and @student_name of type INT and VARCHAR respectively. These variables will store the values of the id and name columns of the inserted record.

Next, we use a SELECT query (see here for more on SELECT queries) to the select id and name from the INSERTED record and store it in the@student_id and @student_name variables that we just created. Finally, we insert these values and a comment “record inserted” for “action_performed” column into log table using INSERT INTO statement.

Now to see if this trigger is working, insert a record inside the student table.

Execute the following query in your SSMS:

USE schooldb;

INSERT INTO student

VALUES (‘Jolly’, ‘Female’, 20, 500)


Now if you view the records in the log table, you should see an entry which looks like this:

Screen grab of log table output

Notice, we did not insert anything into log table. However, since we created a trigger on the student table which performs insertion into log table, with every insertion into student table we will also create a record in the log table as well.

DELETE TRIGGER (INSTEAD OF)

Let’s create another trigger on a DELETE event in the student table. However, this time we’ll use an INSTEAD OF trigger.

When the trigger fires, it will first check if the name column of the deleted student record has a value of “Alan”. If yes then an error will be thrown stating that “Alan’s” record cannot be deleted. An entry will also be inserted in the log table as well stating record cannot be deleted.

However, if the name column has value other than “Alan”, the record will be deleted and an entry will be inserted into log table that record has been deleted.

First lets enter some records into the student table, including Alan’s record.

To do this use the following SQL query.

USE schooldb;

 

INSERT INTO student

 

VALUES

( ‘Jon’, ‘Male’, 22, 545),

(‘Sara’, ‘Female’, 25, 600),

(‘Laura’, ‘Female’, 18, 400),

(‘Alan’, ‘Male’, 20, 500),

(‘Kate’, ‘Female’, 22, 500)


You can see now we have a student in our student table with name “Alan”.

Now, let’s create our INSTEAD OF trigger on DELETE event of student table.

CREATE TRIGGER [dbo].[Student_DELETE]

ON [dbo].[student]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON;

DECLARE @student_id INT, @student_name VARCHAR(50)

SELECT @student_id = DELETED.id,  @student_name = DELETED.name

FROM DELETED

 

IF @student_name = ‘Alan’

BEGIN

RAISERROR(‘Alan”s record cannot be deleted.’,16 ,1)

ROLLBACK

INSERT INTO log

VALUES(@student_id, @student_name, ‘record cant be deleted’);

END

ELSE

BEGIN

DELETE FROM student

WHERE id = @student_id

INSERT INTO log

VALUES(@student_id, @student_name, ‘record deleted’);

END

END

 


Remember, the delete operation will not be performed on student table when you delete a record, rather the above trigger will execute and it will delete the student record except in the case of the record being “Alan’s”.

Now, lets try to delete Alan’s record with following query.

USE schooldb;

DELETE from student

where name=’Alan’;


The record will not be deleted. Instead, an error message appears which we defined using the RAISERROR method in our trigger.

In SQL Server Management Studio the error looks like this:

Image Of Example Error Message

If you open the log table, the record of Alan will be entered. However, the action_performed column will contain “record can’t be deleted”. Now try to delete any other student record. The record shall be deleted and logged in the log table. The log table will contain an entry of the deleted record and looks like this:

Thanks for reading the article. Happy Coding.

This is a guest post by AcuityTraining. If you would like to write for DotnetCrunch, check our guest posting guidelines.

Was this article worth reading? Share it with fellow developers too.

AcuityTraining

Acuity Training is a UK-based IT training business. It offers a variety of training courses including Microsoft applications, Adobe applications and SQL.