SQL Server Trigger Types

Trigger is used to trigger another event when an event occurs in the database server.

There are 3 types of trigger.

1) DML(Data Manipulation Language) Trigger

2) DDL(Data Definition Language) Trigger

3) Logon Trigger

DML(Data Manipulation Language) Trigger:

Includes INSERT, UPDATE, and DELETE operations on the database.

You can also do things using DML Trigger with PRIMARY KEY, FOREIGN KEY, UNIQUE constraint or CHECK CONSTRAINTS.

You can find information about these concepts in my articles titled “What is Primary Key and Foreign Key“, “Differences Between Primary Key and Unique Constraint” and “How To Create CHECK CONSTRAINT“.

You can use the DML Triggers when the CONSTRAINTs mentioned above do not meet the functional requirements.

DML triggers have tables with the names inserted and deleted to be used in the following examples.

For example, when an insert operation comes in, you can find the records inserted in the inserted table, and when an update or delete operation occurs, you can find the records deleted in the deleted table

There are 2 types of DML Trigger.

1) AFTER or FOR:

It is triggered after the completion of any DML operation (INSERT, UPDATE, and DELETE) on the database.

In previous versions of SQL Server it was passed as FOR, so FOR support still continues.

AFTER and FOR mean the same. Let’s make some examples for AFTER INSERT, AFTER UPDATE and AFTER DELETE.

For each example we will use the table below with the create script.

AFTER INSERT:

Let’s write the AFTER INSERT Trigger which transfers the insert operations made to the above TriggerOrganization table to another table.

Create the trigger with the help of the below script.

Now let’s do an insert with the following script on our table named TriggerOrnegi.

Next, we’ll check whether our trigger is working by select on the TriggerOrNegi and TriggerOrnegiBackup tables.

As you can see from the following screenshot, the trigger worked successfully.

AFTER UPDATE:

When there is an update on the table named “TriggerOrnegi”, we use the after update trigger for the reflection of this update on the table named TriggerOrnegiBackup.

The following trigger updates the table named “TriggerOrnegiBackup” in the AdSoyad column when an update operation is performed on the AdSoyad column of the table named “TriggerOrnegi”.

To check that the trigger works correctly, run the following query that updates the AdSoyad column in the “TriggerOrnegi” table and then selects both tables.

As you can see, the trigger worked correctly.

AFTER DELETE:

Similarly, when there is a deletion in the first table, you should perform the same deletion in the second table.

Create the AFTER DELETE trigger as follows.

Let’s check whether the trigger works by deleting a record from the “TriggerOrnegi” table with the help of the following script.

As you can see, when we delete a record from the TriggerOrnegi table, the corresponding record is also deleted from the TriggerOrnegiBackup table.

2) INSTEAD OF:

In the AFTER trigger, the trigger was running after the DML operations were successful.

In the INSTEAD OF trigger, this trigger is triggered before the DML operation takes place.

You can use this trigger for many purposes. we will make examples that adopt a single purpose in this article.

Let’s look at how the INSTEAD OF INSERT, INSTEAD OF UPDATE, and INSTEAD OF DELETE trigger types work.

INSTEAD OF INSERT:

With INSTEAD OF INSERT, we can say that instead of this insert, insert the following operation before the first insert occurs.

When an insert occurs in the “TriggerOrNegi” table, we also insert the same records to the TriggerOrnegiBackup table by incrementing the ID value by 1.

We create the trigger with the following script.

Let’s do the insert operation into the table named TriggerOrder with the following script.

Then you can see the result by select on two tables. (Before performing this operation, remember to delete all the triggers and records we created in the AFTER example.)

As you can see, even though the ID has a value of 1 in the script, we increased the value by 2 and made the insert operation in both tables.

INSTEAD OF UPDATE:

When the Update statement is run, it executes the statements that will work instead of update. To implement our example, first create a log table with the following script.

Trigger will rollback the update if the ID column is updated in our table named “TriggerOrnegi” and in the TriggerLog table we created above, we will be entering the ID value that is being tried to change and its description.

Even if the “AdSoyad” column is updated, it will apply this update and insert it into the TriggerLog table along with an explanation of which record was updated. You will create the trigger as follows.

Now, in our first script, we try to update the ID column in the TriggerOrNegi table, and check if the trigger is working by select on the TriggerOrnegi and TriggerLog tables. Remember to delete the previous triggers.

When we run the above script, we get a message stating that the ID value can not be updated and rollback of the update is performed as below.

When we move to the Result tab on the left side of the Messages section on the left, we can see in the TriggerLog table that the record is not updated and what value is requested to be updated.

Now let’s try to update AdSoyad column with the following script.

As you see below, the update operation is performed and then the corresponding Log recording is saved in the TriggerLog table.

INSTEAD OF DELETE:

When the Delete statement is executed, it executes the statements that will be executed instead of delete statement.

We know that the record with ID value 2 belongs to Nurullah ÇAKIR in the table.

Let’s create a trigger. If the ID value is 2, do not allow deletion. And give a warning that “Nurullah ÇAKIR” can not be deleted.

At the same time, add a record to the TriggerLog table indicating that you want to delete the record for Nurullah ÇAKIR.

If the ID is not set to 2, perform the delete operation.

Let’s try to delete the record with ID number 2 belonging to Nurullah ÇAKIR named person. Do not forget to delete other triggers first.

When we ran the script, it gave us a message like the following.

When we go to the Results tab, we can see that the record has not been deleted and the log record has been saved.

If an attempt was made to delete a record outside ID 2, the deletion would take place.

DDL(Data Definition Language) Trigger:

It can be triggered when the modification operations on the database are performed.

When creating the trigger, we specify when to trigger it. I gave the database modification procedures a few examples below.

  • CREATE DATABASE, DROP DATABASE
  • Create Table, Alter Table, Drop Table
  • Create Function,Alter Function,Drop Function
  • Create View,Alter View,Drop View
  • Create Stored Procedure,Alter Stored Procedure, Drop Stored Procedure

To make an example of modifications in the database I create a stored procedure, a view, a function with the following script in our test database.

After creating our sample objects, create a Database Trigger as follows.

We will define that the trigger will prevent the creation of TABLE, FUNCTION, VIEW or STORED PROCEDURE in this database, and deletion and modification in existing ones.

After creating the Trigger, you can see the result of deleting, modifying, and creating a new object.

As an example, let’s try to delete the view we created.

To find out what you can do with the DDL Trigger, you should run the script below.

Logon Trigger:

When the user logs in to become a login, the login and password information is checked and then if the connection information is correct, the connection is triggered before it happens.

You can not use this for failed connections. Instead, you should use SQL Server Alerts.

I mentioned this in the article “How To Send Email For Failed Logons“.

You can find more detailed information about the alerts in the “Always On Availability Group Alert System” and “SQL Server Best Practices and Configurations that need to be made After Installation“.

It can be used to log successful entries or limit the privileges of some of the logins. Let’s make 2 examples about these two topics.

Example1:

We use the following script to create a table to hold successful logins in the master database and log successful entries with the Logon Trigger.

You can delete the Logon Trigger with the following script.

Example2:

We create a login named TestLogin with the following script, and when the session number of this login is 3, we trigger the trigger to prevent it from opening more than 3 connections.

After login with TestLogin, try to open several query screens by clicking new query.

When you try to open the third query screen, it will fail as below.

You can delete the Logon Trigger with the following script.

You can see the Triggers at the table level under Triggers as below.

Database-level triggers can be viewed under Programmability-> Database Triggers as follows.

You can see the Triggers at Server Level under Server Objects as below.