SQL Server Trigger Types

dbtut

6 years ago

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.

CREATE TABLE [dbo].[TriggerOrnegi](
[ID] [int] NULL,
[AdSoyad] [varchar](100) NULL
) ON [PRIMARY]

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.

CREATE TABLE [dbo].[TriggerOrnegiBackup](
[ID] [int] NULL,
[AdSoyad] [varchar](100) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[TriggerOrnegiBackup](

[ID] [int] NULL,

[AdSoyad] [varchar](100) NULL

) ON [PRIMARY]

Create the trigger with the help of the below script.

CREATE TRIGGER Trg_After_Insert
ON [dbo].[TriggerOrnegi]
AFTER INSERT AS
BEGIN
INSERT INTO [dbo].[TriggerOrnegiBackup]
SELECT * FROM INSERTED
END

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.

INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])VALUES(1,'Nurullah ÇAKIR')
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerOrnegiBackup]

INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])VALUES(1,'Nurullah ÇAKIR')

Select * FROM [dbo].[TriggerOrnegi]

Select * FROM [dbo].[TriggerOrnegiBackup]

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”.

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

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.

UPDATE [dbo].[TriggerOrnegi]   SET [AdSoyad] = 'Nurullah ÇAKIR Kim?' WHERE AdSoyad='Nurullah ÇAKIR'
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerOrnegiBackup]

UPDATE [dbo].[TriggerOrnegi] SET [AdSoyad] = 'Nurullah ÇAKIR Kim?' WHERE AdSoyad='Nurullah ÇAKIR'

Select * FROM [dbo].[TriggerOrnegi]

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.

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

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.

DELETE FROM [dbo].[TriggerOrnegi]  WHERE [AdSoyad] = 'Nurullah ÇAKIR Kim?'
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerOrnegiBackup]

DELETE FROM [dbo].[TriggerOrnegi] WHERE [AdSoyad] = 'Nurullah ÇAKIR Kim?'

Select * FROM [dbo].[TriggerOrnegi]

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.

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

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.)

INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])VALUES(1,'Nurullah ÇAKIR')
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerOrnegiBackup]

INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])VALUES(1,'Nurullah ÇAKIR')

Select * FROM [dbo].[TriggerOrnegi]

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.

CREATE TABLE [dbo].[TriggerLog](
[IDMevcutHali] [int] NULL,
[IDUpdateEdilmekIstenenHali] [int] null,
[Aciklama] [varchar](500) NULL
) ON [PRIMARY]

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.

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

CREATE TRIGGER Trg_InsteadOf_Update

ON [dbo].[TriggerOrnegi]

INSTEAD OF UPDATE

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

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.

UPDATE [dbo].[TriggerOrnegi] SET ID = 3 Where ID=2
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerLog]

UPDATE [dbo].[TriggerOrnegi] SET ID = 3 Where ID=2

Select * FROM [dbo].[TriggerOrnegi]

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.

UPDATE [dbo].[TriggerOrnegi] SET AdSoyad = 'Nurullah ÇAKIR Kim?' Where ID=2
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerLog]

UPDATE [dbo].[TriggerOrnegi] SET AdSoyad = 'Nurullah ÇAKIR Kim?' Where ID=2

Select * FROM [dbo].[TriggerOrnegi]

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.

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

CREATE TRIGGER [dbo].[Trg_InsteadOf_Delete]

ON [dbo].[TriggerOrnegi]

INSTEAD OF DELETE

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

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.

DELETE FROM [dbo].[TriggerOrnegi] Where ID=2
GO
Select * FROM [dbo].[TriggerOrnegi]
GO
Select * FROM [dbo].[TriggerLog]

DELETE FROM [dbo].[TriggerOrnegi] Where ID=2

Select * FROM [dbo].[TriggerOrnegi]

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.

--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

--Creating Sample Stored Procedure.

CREATE PROCEDURE OrnekStoredProcedure

BEGIN

SET NOCOUNT ON;

END

--Creating Sample Function

CREATE FUNCTION OrnekFunction(@a int)

RETURNS TABLE

RETURN (SELECT 0 AS dönecektablo)

--Creating Sample View

CREATE VIEW [dbo].[OrnekView]

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.

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

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.

DROP VIEW [dbo].[OrnekView]
GO

1 2	DROP VIEW [dbo].[OrnekView] GO

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

select * from sys.trigger_event_types

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.

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

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

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.

DROP TRIGGER Trigger_Log ON ALL SERVER

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.

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;

USE master;

CREATE LOGIN TestLogin WITH PASSWORD = 'Test123'

GRANT VIEW SERVER STATE TO TestLogin;

CREATE TRIGGER Trigger_ConnectionLimit

ON ALL SERVER

FOR LOGON

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.

DROP TRIGGER Trigger_ConnectionLimit ON ALL SERVER

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.