Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 | CREATE TABLE [dbo].[TriggerOrnegi]( [ID] [int] NULL, [AdSoyad] [varchar](100) NULL ) ON [PRIMARY] |
AFTER INSERT:
Let’s write the AFTER INSERT Trigger which transfers the insert operations made to the above TriggerOrganization table to another table.
1 2 3 4 | CREATE TABLE [dbo].[TriggerOrnegiBackup]( [ID] [int] NULL, [AdSoyad] [varchar](100) NULL ) ON [PRIMARY] |
Create the trigger with the help of the below script.
1 2 3 4 5 6 7 | CREATE TRIGGER Trg_After_Insert ON [dbo].[TriggerOrnegi] AFTER INSERT AS BEGIN INSERT INTO [dbo].[TriggerOrnegiBackup] SELECT * FROM INSERTED END |
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.
1 2 3 4 5 | INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])VALUES(1,'Nurullah ÇAKIR') GO Select * FROM [dbo].[TriggerOrnegi] GO Select * FROM [dbo].[TriggerOrnegiBackup] |
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”.
1 2 3 4 5 6 7 8 9 10 | CREATE TRIGGER Trg_After_Update ON [dbo].[TriggerOrnegi] AFTER UPDATE AS BEGIN UPDATE [dbo].[TriggerOrnegiBackup] SET [dbo].[TriggerOrnegiBackup].AdSoyad = inserted.AdSoyad FROM [dbo].[TriggerOrnegiBackup] JOIN inserted ON [dbo].[TriggerOrnegiBackup].ID = inserted.ID END |
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.
1 2 3 4 5 | UPDATE [dbo].[TriggerOrnegi] SET [AdSoyad] = 'Nurullah ÇAKIR Kim?' WHERE AdSoyad='Nurullah ÇAKIR' GO Select * FROM [dbo].[TriggerOrnegi] GO Select * FROM [dbo].[TriggerOrnegiBackup] |
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.
1 2 3 4 5 6 7 | CREATE TRIGGER Trg_After_Delete ON [dbo].[TriggerOrnegi] AFTER DELETE AS BEGIN DELETE FROM [dbo].[TriggerOrnegiBackup] WHERE [dbo].[TriggerOrnegiBackup].ID IN (SELECT ID FROM DELETED) END |
Let’s check whether the trigger works by deleting a record from the “TriggerOrnegi” table with the help of the following script.
1 2 3 4 5 | DELETE FROM [dbo].[TriggerOrnegi] WHERE [AdSoyad] = 'Nurullah ÇAKIR Kim?' GO Select * FROM [dbo].[TriggerOrnegi] GO Select * FROM [dbo].[TriggerOrnegiBackup] |
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.
1 2 3 4 5 6 | CREATE TRIGGER Trg_InsteadOf_Insert ON [dbo].[TriggerOrnegi] INSTEAD OF INSERT AS BEGIN INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])Select ID+1,AdSoyad FROM inserted INSERT INTO [dbo].[TriggerOrnegiBackup]([ID],[AdSoyad])Select ID+1,AdSoyad FROM inserted END |
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.)
1 2 3 4 5 | INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])VALUES(1,'Nurullah ÇAKIR') GO Select * FROM [dbo].[TriggerOrnegi] GO Select * FROM [dbo].[TriggerOrnegiBackup] |
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.
1 2 3 4 5 | CREATE TABLE [dbo].[TriggerLog]( [IDMevcutHali] [int] NULL, [IDUpdateEdilmekIstenenHali] [int] null, [Aciklama] [varchar](500) NULL ) ON [PRIMARY] |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | CREATE TRIGGER Trg_InsteadOf_Update ON [dbo].[TriggerOrnegi] INSTEAD OF UPDATE AS BEGIN DECLARE @ID INT, @AdSoyad VARCHAR(100) SELECT @ID= INSERTED.ID, @AdSoyad = INSERTED.AdSoyad FROM INSERTED IF UPDATE(ID) BEGIN RAISERROR('ID Değeri Update Edilemez.', 16 ,1) ROLLBACK INSERT INTO [dbo].[TriggerLog] VALUES(@ID, 'ID değeri update edilemez.') END ELSE BEGIN UPDATE [dbo].[TriggerOrnegi] SET AdSoyad = @AdSoyad WHERE ID = @ID INSERT INTO [dbo].[TriggerLog] VALUES(@ID, 'AdSoyad kolonu update edildi.') END END |
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.
1 2 3 4 5 | UPDATE [dbo].[TriggerOrnegi] SET ID = 3 Where ID=2 GO Select * FROM [dbo].[TriggerOrnegi] GO Select * FROM [dbo].[TriggerLog] |
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.
1 2 3 4 5 | UPDATE [dbo].[TriggerOrnegi] SET AdSoyad = 'Nurullah ÇAKIR Kim?' Where ID=2 GO Select * FROM [dbo].[TriggerOrnegi] GO Select * FROM [dbo].[TriggerLog] |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | CREATE TRIGGER [dbo].[Trg_InsteadOf_Delete] ON [dbo].[TriggerOrnegi] INSTEAD OF DELETE AS BEGIN DECLARE @ID INT SELECT @ID = DELETED.ID FROM DELETED IF @ID = 2 BEGIN RAISERROR('The record of Nurullah ÇAKIR can not be deleted.',16 ,1) ROLLBACK INSERT INTO [dbo].[TriggerLog] VALUES(@ID,NULL, 'The record of Nurullah ÇAKIR wanted to be deleted.') END ELSE BEGIN DELETE FROM [dbo].[TriggerOrnegi] WHERE ID = @ID INSERT INTO [dbo].[TriggerLog] VALUES(@ID,NULL, 'Instead Of Delete') END END |
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.
1 2 3 4 5 | DELETE FROM [dbo].[TriggerOrnegi] Where ID=2 GO Select * FROM [dbo].[TriggerOrnegi] GO Select * FROM [dbo].[TriggerLog] |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | --Creating Sample Stored Procedure. CREATE PROCEDURE OrnekStoredProcedure AS BEGIN SET NOCOUNT ON; END --Creating Sample Function CREATE FUNCTION OrnekFunction(@a int) RETURNS TABLE AS RETURN (SELECT 0 AS dönecektablo) --Creating Sample View CREATE VIEW [dbo].[OrnekView] AS SELECT dbo.TriggerOrnegi.* FROM dbo.TriggerOrnegi |
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.
1 2 3 4 5 6 7 8 | CREATE TRIGGER DDLTriggerOrnek ON DATABASE FOR CREATE_TABLE,DROP_TABLE, ALTER_TABLE, CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION, CREATE_VIEW,ALTER_VIEW,DROP_VIEW, CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE AS PRINT 'Modifications to this database can not be performed.' ROLLBACK |
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.
1 2 | DROP VIEW [dbo].[OrnekView] GO |
To find out what you can do with the DDL Trigger, you should run the script below.
1 | select * from sys.trigger_event_types |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE TABLE Login_Info ( Login_Name NVARCHAR(256), Login_Time DATETIME, Host_Name NVARCHAR(128) ) CREATE TRIGGER Trigger_Log ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN INSERT INTO master..Login_Info SELECT ORIGINAL_LOGIN(), GETDATE(), EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(128)') END |
You can delete the Logon Trigger with the following script.
1 | DROP TRIGGER Trigger_Log ON ALL SERVER |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | USE master; GO CREATE LOGIN TestLogin WITH PASSWORD = 'Test123' GO GRANT VIEW SERVER STATE TO TestLogin; GO CREATE TRIGGER Trigger_ConnectionLimit ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'TestLogin' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'TestLogin') > 3 ROLLBACK; END; |
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.
1 | DROP TRIGGER Trigger_ConnectionLimit ON ALL SERVER |
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.
