SQL Server Trigger
January 25, 2023
A trigger is a special type of stored procedure that automatically executes in response to certain events on a table or view in a SQL Server database. Here is an example of a trigger that updates the “testEmployee” table whenever a new employee is inserted:
In this example, the trigger is named “UpdateEmployees” and is set to execute on the “testEmployee” table. The “AFTER INSERT” clause specifies that the trigger should fire after a new row has been inserted into the table.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE dbo.testEmployee (EmployeeId Int identity (1,1), FirstName NVARCHAR(100), LastName NVARCHAR(100), Salary Decimal(12,2), InsertDate DATETIME CONSTRAINT DF_TS_UTC1 DEFAULT GETUTCDATE(), UpdateDate DATETIME CONSTRAINT DF_TS_UTC2 DEFAULT GETUTCDATE() ) GO CREATE TRIGGER [dbo].[UpdateEmployees] ON [dbo].[testEmployee] AFTER INSERT AS BEGIN UPDATE testEmployee SET Salary = Salary * 1.1 WHERE EmployeeID IN (SELECT EmployeeID FROM inserted) END GO
The code inside the trigger updates the salary of any employee that was just inserted by multiplying their current salary by 1.1. The “inserted” virtual table is used to reference the newly inserted rows and the “WHERE” clause filters the update to only apply to rows whose EmployeeID is present in the inserted table.
This trigger will run automatically every time a new employee is inserted into the testEmployee table and update their salary by 10%.