SQL Server Trigger

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