آموزش Trigger در SQL Server: انواع، جدول‌های inserted و deleted و مثال

CREATE TABLE Employee (
  EmployeeId INT PRIMARY KEY,
  FullName   NVARCHAR(100),
  Salary     DECIMAL(12,2)
);

CREATE TABLE SalaryLog (
  LogId      INT IDENTITY PRIMARY KEY,
  EmployeeId INT,
  OldSalary  DECIMAL(12,2),
  NewSalary  DECIMAL(12,2),
  ChangedBy  SYSNAME DEFAULT SUSER_SNAME(),
  ChangedAt  DATETIME2 DEFAULT SYSDATETIME()
);
GO

CREATE TRIGGER trg_Employee_SalaryAudit
ON Employee
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  IF UPDATE(Salary)
    INSERT INTO SalaryLog (EmployeeId, OldSalary, NewSalary)
    SELECT d.EmployeeId, d.Salary, i.Salary
    FROM deleted d
    JOIN inserted i ON i.EmployeeId = d.EmployeeId
    WHERE ISNULL(d.Salary, -1) <> ISNULL(i.Salary, -1);
END;
GO

UPDATE Employee SET Salary = 1500 WHERE EmployeeId = 1;
SELECT * FROM SalaryLog;

CREATE TRIGGER trg_Employee_NoDeleteManager
ON Employee
AFTER DELETE
AS
BEGIN
  IF EXISTS (SELECT 1 FROM deleted WHERE FullName = N'Admin')
  BEGIN
    ROLLBACK TRANSACTION;
    THROW 50010, N'حذف کاربر Admin مجاز نیست', 1;
  END
END;

CREATE TRIGGER trg_Customer_SoftDelete
ON Customer
INSTEAD OF DELETE
AS
BEGIN
  SET NOCOUNT ON;
  UPDATE c SET IsDeleted = 1
  FROM Customer c
  JOIN deleted d ON d.CustomerId = c.CustomerId;
END;

DISABLE TRIGGER trg_Employee_SalaryAudit ON Employee;   -- غیرفعال موقت
ENABLE  TRIGGER trg_Employee_SalaryAudit ON Employee;
DROP    TRIGGER IF EXISTS trg_Employee_SalaryAudit;

-- فهرست Triggerهای یک جدول
SELECT name, is_disabled FROM sys.triggers WHERE parent_id = OBJECT_ID('Employee');