SQL Server Triggers are used to execute after or before an INSERT, DELETE, or an UPDATE operation on a table. You can use these triggers on Views or Tables to perform any of the above-specified activities. Remember, you can associate a trigger to a single table only.
We can call the triggers a special type of stored procedure that runs automatically when the specified event occurs on the database object. That can be DDL or DML operations.
Types of Triggers in SQL Server
There are two types of Triggers in SQL Server, and they are AFTER and INSTEAD OF.
The after/for triggers run on a table after an INSERT, DELETE, or an UPDATE. It means, Before this stored procedure starts running, all the operations should execute, and the statement has to succeed in the constraint check as well.
After triggers are not supported on Views, so use them on tables only. It can be further divided into
- AFTER INSERT: It will fire after the completion of the Insert data operation on the Employee table. Once it completes inserting into the Employee table, it will start inserting into the audit table. Say, if it fails to insert into the Employee, then it won’t insert into the Audit table.
- AFTER UPDATE: This update trigger will fire after the Update operation is completed on the Employees table. Once it ends Updating the Employee, it will start inserting/updating into the audit table. For instance, if it fails to update the Employees table, then it won’t insert it into the Audit.
- AFTER DELETE: The After delete will fire after completing the Delete statement operation on the Employee table. Once it completes deleting the records from the Employees, it will start inserting/deleting from the audit table. Say, if it fails to remove from the Employee, then it won’t insert into the Audit table.
INSTEAD OF TRIGGERS
The Instead of Triggers fired before the execution of an INSERT, DELETE, or an UPDATE on a table start.
It means, Before the trigger starts running, it does not need any condition constraint check. So, this stored procedure will execute even if the constraint check fails. It can be further divided into
- INSTEAD OF INSERT: Instead of insert trigger will fire before the Insert operation on the Employee table starts. Once it completes inserting into the Employee Audit table, it will begin to insert into the Employee. And if it fails for some reason, it won’t insert into the Employee table.
- INSTEAD OF UPDATE: It will fire before updating the records in the Employee table. Once it completes the execution, it will start updating the records in the Employee. And if it fails, it won’t update the table.
- INSTEAD OF DELETE: It will fire before deleting records from the Employee table starts. Once it executes successfully, it will begin deleting records from the Employees table. And if it fails, it won’t remove any record from the Employee.
Data Definition Language Triggers
The Data definition language triggers fires in response to the DDL events, including CREATE, ALTER, DROP, etc. We can use them to prevent schema changes and record the events that occurred in the database schema. An example of this DDL trigger will be doing something or firing when dropping a database or table.
Data Manipulation Language Triggers
The DML triggers fires in response to data manipulation language events, including INSERT, DELETE, and UPDATE statements. These are the special type of stored procedures that are the first step to data integrity. If you further divide the data manipulation language or DML triggers, they are Instead of and after.
As the name suggests, Logon triggers will fire when a user tries to LOGON or establish a connection to the server.
Create Trigger Statement in SQL Server
The syntax of After Triggers in SQL Server is
CREATE [OR ALTER] TRIGGER [Schema_Name].Trig_Name ON Table AFTER INSERT | UPDATE | DELETE AS BEGIN -- Statements -- Insert, Update, Or Delete Statements END
- Schema_name: Please specify the schema name. For example, dbo, or Human Resource, etc.
- Trig_Name: You can specify any name you wish to give other than the system reserved keywords. Please try to use meaningful names so that you can identify them easily.
The basic syntax of creating Instead Of Triggers is as shown below:
CREATE [OR ALTER] TRIGGER [Schema_Name].Trig_Name ON Table | View INSTEAD OF INSERT | UPDATE | DELETE AS BEGIN -- Statements -- Insert, Update, Or Delete Statements END
From the above statements, it is the same create statement that we used before, but we added INSTEAD OF INSERT | UPDATE | DELETE.
Let us see how to Create triggers, Modify the existing ones, and delete them with an example.
We are working with the Employee and audit table for the demonstration. As you can see, both the emp and the audit tables are Empty.
Create Triggers in SQL Server Example
We can create them in two ways. In this example, we show you the steps involved in creating it using the Management Studio (SSMS). Before we start generating any, let us see whether or not our database has any existing ones.
To view the existing, Please select the Database object -> Expand the specified Table Name (on which it exists) -> Go to Triggers Folder and expand it. From the below screenshot, you can observe that our Employee table under the database has none.
Right-click on the folder will open the context menu. Please select the New.. option from it, as we have shown below.
Once you click on that option, it will open the new Query window with the default Template, as we have shown below.
Here, you can add the Schema name, Trig_name, TableName, Data modification statements, and the statements that you want to apply.
Create using transact query
For this demonstration purpose, we are creating an After Insert Trigger in SQL Server to Insert the records into Employee Table Audit, along with the audit information.
CREATE TRIGGER ExampleTR ON EmployeeTR AFTER INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements here INSERT INTO [Audit]( [ID] ,[Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[ServerName] ,[ServerInstanceName] ,[Insert Time]) SELECT ID, Name, Education, Occupation, YearlyIncome, Sales, CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)), CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50)), GETDATE() FROM INSERTED; PRINT 'We Successfully Fired!.' END GO
We already explained the statements inside it in our previous article. I suggest you refer AFTER INSERT article to understand this code.
Run the above query
Messages -------- Command(s) completed successfully.
Let me show you the newly created one by opening the Object Explorer -> Go to the Database -> Go and expand the Employee -> and then expand the Folder.
Let me insert 5 random records into the Employee for the demonstration purpose. It will help us to check whether the After insert is fired or not.
INSERT INTO [EmployeeTR] ( [Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ) VALUES ('Tutorial Gateway', 'Masters Degree', 'Admin', 250000, 1900) ,('Aamir Khan', 'Bachelors', 'Skilled Professional', 69000, 100) ,('Lara Diaz', 'Bachelors', 'Management', 85000, 60) ,('Mahesh kumar', 'Degree', 'Professional', 45000, 630) ,('Ruiz John', 'Post Graduate', 'Clerical', 40000, 220)
From the above screenshot, you can see that our trigger is fired. And also, it prints the message that we placed in it. Please use the following query to check the inserted records in the Employee.
SELECT [ID] ,[Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [EmployeeTable]
Next, check the records in the Audit table using the following query.
SELECT [ID] ,[Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[ServerName] ,[ServerInstanceName] ,[Insert Time] FROM [EmployeeTableAudit]
Alter Triggers in SQL Server to Modify Example
The following examples will help you understand the steps involved in modifying the existing one using the Management Studio and query.
Before we get into the modification, let me show you the available options that the Management Studio provides. As we show below, you can drop, drop, recreate, alter, and create it.
To modify it using the Management Studio, Please navigate to the table that holds them. Next, select the one that you want to modify (ExampleTR), and right-click on it will open the context menu. Here, select the Modify option.
Once you choose the Modify option, a new query window will open with an auto-generated code. You can modify it as per your requirement.
Use Alter Trigger to Modify
The steps involved in modifying the existing one using the ALTER statement. To do so, Click the New query and return the following query.
ALTER TRIGGER [dbo].[ExampleTR] ON [dbo].[EmployeeTR] AFTER INSERT AS BEGIN -- Modify as per Your requirement END
Delete Triggers in SQL Server Example
The following examples will show you, How to drop them using a query and Management Studio (SSMS). Here, we show you how to delete them using the SSMS. To delete using the SSMS, right-click on the name you want to delete to open the context menu. You can click on the Delete option.
Here, we will show you how to delete it using the Drop Trigger statement.
DROP TRIGGER [dbo].[ExampleTR]