{"id":574,"date":"2018-07-02T19:57:04","date_gmt":"2018-07-02T19:57:04","guid":{"rendered":"http:\/\/dbtut.com\/?p=574"},"modified":"2021-02-05T22:38:27","modified_gmt":"2021-02-05T22:38:27","slug":"sql-server-trigger-types","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/07\/02\/sql-server-trigger-types\/","title":{"rendered":"SQL Server Trigger Types"},"content":{"rendered":"

Trigger is used to trigger another event when an event occurs in the database server.<\/p>\n

There are 3 types of trigger.<\/p>\n

1) DML(Data Manipulation Language) Trigger<\/h3>\n

2) DDL(Data Definition Language) Trigger<\/h3>\n

3) Logon Trigger<\/h3>\n

DML(Data Manipulation Language) Trigger:<\/h2>\n

Includes INSERT, UPDATE, and DELETE operations on the database.<\/p>\n

You can also do things using DML Trigger with PRIMARY KEY, FOREIGN KEY, UNIQUE constraint or CHECK CONSTRAINTS.<\/p>\n

You can find information about these concepts in my articles titled “What is Primary Key and Foreign Key<\/a>“, “Differences Between Primary Key and Unique Constraint<\/a>” and “How To Create\u00a0CHECK CONSTRAINT<\/a>“.<\/p>\n

You can use the DML Triggers when the CONSTRAINTs mentioned above do not meet the functional requirements.<\/p>\n

DML triggers have tables with the names inserted and deleted to be used in the following examples. <\/span><\/p>\n

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 <\/span>deleted <\/span>in the <\/span>deleted <\/span>table<\/span><\/p>\n

There are 2 types of DML Trigger.<\/p>\n

1) AFTER or FOR:<\/h3>\n

It is triggered after the completion of any DML operation (INSERT, UPDATE, and DELETE) on the database.<\/p>\n

In previous versions of SQL Server it was passed as FOR, so FOR support still continues.<\/p>\n

AFTER and FOR mean the same. Let’s make some examples for AFTER INSERT, AFTER UPDATE and AFTER DELETE.<\/p>\n

For each example we will use the table below with the create script.<\/p>\n

CREATE TABLE [dbo].[TriggerOrnegi](\r\n[ID] [int] NULL,\r\n[AdSoyad] [varchar](100) NULL\r\n) ON [PRIMARY]<\/pre>\n
<\/h3>\n
AFTER INSERT:<\/h3>\n
Let’s write the AFTER INSERT Trigger which transfers the insert operations made to the above TriggerOrganization table to another table.<\/p>\n
CREATE TABLE [dbo].[TriggerOrnegiBackup](\r\n[ID] [int] NULL,\r\n[AdSoyad] [varchar](100) NULL\r\n) ON [PRIMARY]<\/pre>\n
Create the trigger with the help of the below script.<\/p>\n
CREATE TRIGGER Trg_After_Insert\r\nON [dbo].[TriggerOrnegi]\r\nAFTER INSERT AS\r\nBEGIN\r\nINSERT INTO [dbo].[TriggerOrnegiBackup]\r\nSELECT * FROM INSERTED\r\nEND<\/pre>\n
Now let’s do an insert with the following script on our table named TriggerOrnegi.<\/p>\n
Next, we’ll check whether our trigger is working by select on the TriggerOrNegi and TriggerOrnegiBackup tables.<\/p>\n
INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])VALUES(1,'Nurullah \u00c7AKIR')\r\nGO\r\nSelect * FROM [dbo].[TriggerOrnegi]\r\nGO\r\nSelect * FROM [dbo].[TriggerOrnegiBackup]<\/pre>\n
As you can see from the following screenshot, the trigger worked successfully.<\/p>\n
\"\"<\/p>\n
<\/h3>\n
AFTER UPDATE:<\/h3>\n
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.<\/p>\n
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”.<\/p>\n
CREATE TRIGGER Trg_After_Update\r\nON [dbo].[TriggerOrnegi]\r\nAFTER UPDATE AS\r\nBEGIN\r\nUPDATE [dbo].[TriggerOrnegiBackup]\r\nSET\u00a0\u00a0\u00a0 [dbo].[TriggerOrnegiBackup].AdSoyad = inserted.AdSoyad\r\nFROM\u00a0\u00a0 [dbo].[TriggerOrnegiBackup]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN inserted\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON [dbo].[TriggerOrnegiBackup].ID = inserted.ID\u00a0\r\nEND<\/pre>\n
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.<\/p>\n
UPDATE [dbo].[TriggerOrnegi]\u00a0\u00a0 SET [AdSoyad] = 'Nurullah \u00c7AKIR Kim?' WHERE AdSoyad='Nurullah \u00c7AKIR'\r\nGO\r\nSelect * FROM [dbo].[TriggerOrnegi]\r\nGO\r\nSelect * FROM [dbo].[TriggerOrnegiBackup]<\/pre>\n
As you can see, the trigger worked correctly.<\/p>\n
<\/p>\n
AFTER DELETE:<\/h3>\n
Similarly, when there is a deletion in the first table, you should perform the same deletion in the second table.<\/p>\n
Create the AFTER DELETE trigger as follows.<\/p>\n
CREATE TRIGGER Trg_After_Delete\r\nON [dbo].[TriggerOrnegi]\r\nAFTER DELETE AS\r\nBEGIN\r\nDELETE FROM [dbo].[TriggerOrnegiBackup] \r\nWHERE [dbo].[TriggerOrnegiBackup].ID IN (SELECT ID FROM DELETED)\r\nEND<\/pre>\n
Let’s check whether the trigger works by deleting a record from the “TriggerOrnegi” table with the help of the following script.<\/p>\n
DELETE FROM [dbo].[TriggerOrnegi]\u00a0 WHERE [AdSoyad] = 'Nurullah \u00c7AKIR Kim?'\r\nGO\r\nSelect * FROM [dbo].[TriggerOrnegi]\r\nGO\r\nSelect * FROM [dbo].[TriggerOrnegiBackup]<\/pre>\n
As you can see, when we delete a record from the TriggerOrnegi table, the corresponding record is also deleted from the TriggerOrnegiBackup table.<\/p>\n
<\/p>\n
<\/h3>\n
2) INSTEAD OF:<\/h3>\n
In the AFTER trigger, the trigger was running after the DML operations were successful.<\/p>\n
In the INSTEAD OF trigger, this trigger is triggered before the DML operation takes place.<\/p>\n
You can use this trigger for many purposes. we will make examples that adopt a single purpose in this article.<\/p>\n
Let’s look at how the INSTEAD OF INSERT, INSTEAD OF UPDATE, and INSTEAD OF DELETE trigger types work.<\/p>\n
<\/h3>\n
INSTEAD OF INSERT:<\/h3>\n
With INSTEAD OF INSERT, we can say that instead of this insert, insert the following operation before the first insert occurs.<\/p>\n
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.<\/p>\n
We create the trigger with the following script.<\/p>\n
CREATE TRIGGER Trg_InsteadOf_Insert ON [dbo].[TriggerOrnegi]\r\nINSTEAD OF INSERT\r\nAS BEGIN\r\nINSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])Select ID+1,AdSoyad FROM inserted\r\nINSERT INTO [dbo].[TriggerOrnegiBackup]([ID],[AdSoyad])Select ID+1,AdSoyad FROM inserted\r\nEND<\/pre>\n
Let’s do the insert operation into the table named TriggerOrder with the following script.<\/p>\n
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.)<\/p>\n
INSERT INTO [dbo].[TriggerOrnegi]([ID],[AdSoyad])VALUES(1,'Nurullah \u00c7AKIR')\r\nGO\r\nSelect * FROM [dbo].[TriggerOrnegi]\r\nGO\r\nSelect * FROM [dbo].[TriggerOrnegiBackup]<\/pre>\n
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.<\/p>\n
<\/p>\n
<\/h3>\n
INSTEAD OF UPDATE:<\/h3>\n
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.<\/p>\n
CREATE TABLE [dbo].[TriggerLog](\r\n[IDMevcutHali] [int] NULL,\r\n[IDUpdateEdilmekIstenenHali] [int] null,\r\n[Aciklama] [varchar](500) NULL\r\n) ON [PRIMARY]<\/pre>\n
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.<\/p>\n
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.<\/p>\n
CREATE TRIGGER Trg_InsteadOf_Update\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON [dbo].[TriggerOrnegi]\r\n\r\nINSTEAD OF UPDATE\r\nAS\r\nBEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DECLARE @ID INT, @AdSoyad VARCHAR(100)\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @ID= INSERTED.ID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @AdSoyad = INSERTED.AdSoyad\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM INSERTED\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF UPDATE(ID)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RAISERROR('ID De\u011feri Update Edilemez.', 16 ,1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROLLBACK\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT INTO [dbo].[TriggerLog]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUES(@ID, 'ID de\u011feri update edilemez.')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UPDATE [dbo].[TriggerOrnegi]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET AdSoyad = @AdSoyad\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE ID = @ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT INTO [dbo].[TriggerLog]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUES(@ID, 'AdSoyad kolonu update edildi.')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\nEND<\/pre>\n
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.<\/p>\n
UPDATE [dbo].[TriggerOrnegi] SET ID = 3 Where ID=2\r\nGO\r\nSelect * FROM [dbo].[TriggerOrnegi]\r\nGO\r\nSelect * FROM [dbo].[TriggerLog]<\/pre>\n
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.<\/p>\n
<\/p>\n
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.<\/p>\n
<\/p>\n
Now let’s try to update AdSoyad column with the following script.<\/p>\n
UPDATE [dbo].[TriggerOrnegi] SET AdSoyad = 'Nurullah \u00c7AKIR Kim?' Where ID=2\r\nGO\r\nSelect * FROM [dbo].[TriggerOrnegi]\r\nGO\r\nSelect * FROM [dbo].[TriggerLog]<\/pre>\n
As you see below, the update operation is performed and then the corresponding Log recording is saved in the TriggerLog table.<\/p>\n
<\/p>\n
INSTEAD OF DELETE:<\/h3>\n
When the Delete statement is executed, it executes the statements that will be executed instead of delete statement.<\/p>\n
We know that the record with ID value 2 belongs to Nurullah \u00c7AKIR in the table.<\/p>\n
Let’s create a trigger. If the ID value is 2, do not allow deletion. And give a warning that “Nurullah \u00c7AKIR” can not be deleted.<\/p>\n
At the same time, add a record to the TriggerLog table indicating that you want to delete the record for Nurullah \u00c7AKIR.<\/p>\n
If the ID is not set to 2, perform the delete operation.<\/p>\n
CREATE TRIGGER [dbo].[Trg_InsteadOf_Delete]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON [dbo].[TriggerOrnegi]\r\nINSTEAD OF DELETE\r\nAS\r\nBEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DECLARE @ID INT\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @ID = DELETED.ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM DELETED\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @ID = 2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RAISERROR('The record of Nurullah \u00c7AKIR can not be deleted.',16 ,1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROLLBACK\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT INTO [dbo].[TriggerLog]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUES(@ID,NULL, 'The record of Nurullah \u00c7AKIR wanted to be deleted.')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DELETE FROM [dbo].[TriggerOrnegi]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE ID = @ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT INTO [dbo].[TriggerLog]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUES(@ID,NULL, 'Instead Of Delete')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\nEND<\/pre>\n
Let’s try to delete the record with ID number 2 belonging to Nurullah \u00c7AKIR named person. Do not forget to delete other triggers first.<\/p>\n
DELETE FROM [dbo].[TriggerOrnegi] Where ID=2\r\nGO\r\nSelect * FROM [dbo].[TriggerOrnegi]\r\nGO\r\nSelect * FROM [dbo].[TriggerLog]<\/pre>\n
When we ran the script, it gave us a message like the following.<\/p>\n
<\/p>\n
When we go to the Results tab, we can see that the record has not been deleted and the log record has been saved.<\/p>\n
If an attempt was made to delete a record outside ID 2, the deletion would take place.<\/p>\n
<\/p>\n
DDL(Data Definition Language) Trigger:<\/h2>\n
It can be triggered when the modification operations on the database are performed.<\/p>\n
When creating the trigger, we specify when to trigger it. I gave the database modification procedures a few examples below.<\/p>\n