انواع تریگر (Trigger) در Sql Server

 مقدمه

تریگر (Trigger) در SQL Server یکی از ابزارهای قدرتمند در تسهیل کنترل رفتار داده‌ها در زمان رخداد عملیات DML یا DDL است.

تریگرها باعث می‌شوند که کدهایی به صورت خودکار اجرا شوند وقتی که اتفاقاتی مانند INSERT، UPDATE، DELETE یا تغییری در ساختار داده‌ها رخ می‌دهد.

استفاده صحیح از تریگرها می‌تواند به تضمین یکپارچگی داده‌ها، ورود اطلاعات کنترلی، لاگ کردن تغییرات و اعمال قواعد کسب‌وکار کمک کند.

انواع تریگر (Trigger) در SQL Server (مفاهیم و دسته‌بندی کلی)

در SQL Server، تریگرها به دسته‌های مختلفی تقسیم می‌شوند.

فهم درست این انواع، انتخاب صحیح هنگام طراحی سیستم را تسهیل می‌کند.

در ادامه انواع اصلی تریگرها را معرفی و مقایسه می‌کنیم:

•  تریگرهای DML (Data Manipulation Language)
•  تریگرهای DDL (Data Definition Language)
•  تریگرهای LOGON / ورود به سرور
•  تریگرهای سروری / SERVER-SCOPED

در ادامه، هر کدام را با جزئیات و مثال بررسی می‌کنیم.

 تریگرهای DML در SQL Server

تریگرهای DML رایج‌ترین نوع تریگرها هستند و در پاسخ به اعمال داده‌ای مانند INSERT، UPDATE و DELETE بر روی جداول یا نمای‌ها (views) اجرا می‌شوند.

تریگر DML پس از رخداد یک دستور DML یا به جای آن اجرا می‌شود و می‌تواند به صورت تراکنشی اجرا گردد.

SQL Server دو حالت عمده دارد: AFTER (یا FOR) و INSTEAD OF.

1.  در حالت AFTER، تریگر بعد از اجرای دستور اصلی اجرا می‌شود (اگر دستور موفق باشد).

2.  در حالت INSTEAD OF، تریگر به جای دستور اصلی اجرا می‌شود (دستور اصلی اجرا نمی‌شود مگر در داخل تریگر اجرا گردد)

تریگرهای DML اجازه دسترسی به جداول مجازی inserted و deleted را دارند که شامل داده‌های جدید و قدیم هستند.

 انواع تریگر DML

در عمل، ترکیبی از حالات زیر ممکن است وجود داشته باشد:

1. AFTER INSERT
2. AFTER UPDATE
3. AFTER DELETE
4. AFTER INSERT, UPDATE (ترکیبی)
5. INSTEAD OF INSERT
6. INSTEAD OF UPDATE
7. INSTEAD OF DELETE
8. تریگر ترکیبی INSTEAD OF INSERT, UPDATE, DELETE

مثال‌ کاربردی تریگر AFTER INSERT برای لاگ کردن تغییرات

فرض کنید جدولی به نام Employees دارید و می‌خواهید هر بار رکورد جدید اضافه می‌شود، اطلاعات آن را در جدول لاگ ذخیره کنید:

CREATE TABLE Employees
(
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100),
    Salary DECIMAL(18,2)
);

CREATE TABLE EmployeeAudit
(
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    Name NVARCHAR(100),
    Salary DECIMAL(18,2),
    ModifiedDate DATETIME,
    Operation NVARCHAR(10)
);

CREATE TRIGGER TR_AfterInsert_Employees
ON dbo.Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO dbo.EmployeeAudit (EmployeeID, Name, Salary, ModifiedDate, Operation)
    SELECT i.EmployeeID, i.Name, i.Salary, GETDATE(), 'INSERT'
    FROM inserted i;
END;

در اینجا وقتی یک رکورد جدید اضافه شود، تریگر اجرا شده و داده‌های آن به جدول لاگ وارد می‌شود.

مثال ۲: تریگر AFTER UPDATE برای بررسی تغییر در ستون خاص

CREATE TRIGGER TR_AfterUpdate_Employees
ON dbo.Employees
AFTER UPDATE
AS
BEGIN
    -- بررسی اینکه آیا ستون Salary تغییر کرده است
    IF UPDATE(Salary)
    BEGIN
        INSERT INTO dbo.EmployeeAudit (EmployeeID, Name, Salary, ModifiedDate, Operation)
        SELECT i.EmployeeID, i.Name, i.Salary, GETDATE(), 'UPDATE'
        FROM inserted i;
    END
END;

این تریگر فقط در صورتی اجرا می‌شود که ستون Salary تغییر کند، از تابع UPDATE(column) استفاده می‌کنیم.

مثال ۳: تریگر INSTEAD OF در نمای (View)

گاهی می‌خواهیم نمایی داشته باشیم که قابل نوشتن نیست، اما با تریگر INSTEAD OF امکان پذیر کنیم:

CREATE VIEW vw_Employees AS
SELECT EmployeeID, Name, Salary
FROM dbo.Employees;

GO

CREATE TRIGGER TR_InsteadOfInsert_vwEmployees
ON vw_Employees
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO dbo.Employees (Name, Salary)
    SELECT i.Name, i.Salary
    FROM inserted i;
END;

در این حالت هرگاه دستور INSERT بر روی نما اجرا شود، به جای آن تریگر اجرا خواهد شد و داده به جدول پایه منتقل می‌شود.

مزایا و معایب تریگرهای DML

تریگرهای DML در SQL Server ابزاری قدرتمند برای اجرای خودکار منطق در واکنش به تغییرات داده‌ای هستند.

با وجود کارایی بالا در کنترل فرآیندهای پایگاه داده، این تریگرها نقاط قوت و ضعف خاص خود را دارند که در ادامه به صورت خلاصه مرور می‌کنیم.

مزایای تریگرهای DML

•  خودکار بودن اجرای منطق در زمان رخداد عملیات داده‌ای
•  اطمینان از اجرای قوانین کسب‌وکار در سطح دیتابیس حتی اگر تغییرات از بیرون اپلیکیشن رخ دهد
• امکان نگهداری تاریخچه تغییرات (Audit)
•  جلوگیری از تکرار کد در لایه‌های مختلف (در اپلیکیشن)

معایب و محدودیت‌های تریگرهای DML

•  اضافه کردن سربار اجرایی (Overhead) برای عملیات داده‌ای
•  امکان ایجاد رفتارهای بازگشتی یا حلقه (recursion) اگر تریگرها به جداولی که خود به آن تریگر وابسته‌اند تغییر دهند
•  دشواری در اشکال‌یابی (Debugging)
•  در برخی سناریوها ممکن است باعث قفل شدن جداول شود
•  در تغییرات دسته‌ای بزرگ (Bulk) ممکن است عملکرد را کاهش دهد.

تریگرهای DDL در SQL Server

تریگرهای DDL برای کنترل تغییرات ساختاری در بانک اطلاعاتی مانند دستورهای CREATE, ALTER, DROP به کار می‌روند.

این نوع تریگر امکان مانیتورینگ و اعمال سیاست هنگام تغییر ساختار (مانند اضافه کردن جدول، حذف ستون، تغییر ایندکس و غیره) را فراهم می‌کند.

تریگر DDL در سطح بانک یا سرور تعریف می‌شود و به رویدادهای DDL واکنش نشان می‌دهد.

این تریگرها به دو سطح تقسیم می‌شوند:

•  تریگر DDL سطح دیتابیس (Database-scoped DDL Trigger)
•  تریگر DDL سطح سرور (Server-scoped DDL Trigger / ALL SERVER)

تریگرهای DDL می‌توانند برای کنترل تغییرات ناخواسته، ثبت لاگ تغییرات اسکیم (Schema changes) و اعمال سیاست‌های امنیتی مفید باشند.

 مثال کاربردی DDL Trigger

فرض کنید می‌خواهید زمانی که جدول یا نمایی حذف (DROP) یا ساخته می‌شود، اطلاعات تغییر را در جدول لاگ ذخیره کنید:

-- جدول لاگ برای تغییرات DDL
CREATE TABLE DDLEventLog
(
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    EventType NVARCHAR(100),
    ObjectName NVARCHAR(200),
    EventTime DATETIME,
    LoginName NVARCHAR(128),
    TSQLCommand NVARCHAR(MAX)
);
GO

-- تریگر DDL در سطح بانک
CREATE TRIGGER TR_DDL_Database_Changes
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
    CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    DECLARE @eventXml XML = EVENTDATA();
    INSERT INTO DDLEventLog(EventType, ObjectName, EventTime, LoginName, TSQLCommand)
    VALUES
    (
        @eventXml.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @eventXml.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(200)'),
        GETDATE(),
        @eventXml.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)'),
        @eventXml.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)')
    );
END;
GO

در این تریگر از تابع ()EVENTDATA استفاده شده است که اطلاعات مربوط به رویداد DDL رخ داده را به صورت XML بازمی‌گرداند.

نکات کلیدی و ملاحظات مهم

 1. تریگرهای DDL در سطح اسکیم (schema) تعریف نمی‌شوند، بلکه در سطح بانک یا سرور هستند.

2. تابع OBJECT_ID معمولاً برای تریگرهای DDL قابل استفاده نیست، باید از نماهایی مانند sys.triggers و sys.server_triggers استفاده شود.

3. اگر بخواهید یک DDL Trigger سطح سرور ایجاد یا حذف کنید، باید از مجوزهای مدیریتی (CONTROL SERVER) بهره ببرید.

4. هنگام حذف شیّی (DROP) گاهی ممکن است تریگر نتواند قبل از حذف کامل، کد شیء را بگیرد (مثلاً اگر شیء حذف شود تریگر نمی‌تواند آن را بخواند)

تریگرهای LOGON ( ورود به سرور )

تریگر ورود (Logon Trigger) در سطح سرور تعریف می‌شود و زمانی اجرا می‌شود که اتصال جدیدی به SQL Server برقرار شود (Logon).

 کاربردهای تریگرهای LOGON

•  اعمال سیاست‌های امنیتی (مثلاً عدم اجازه به ورود در ساعات خاص)
•  ثبت لاگ ورود کاربران همراه با آی‌پی کلاینت
•  جلوگیری از ورود کاربرانی که شرایط خاص را ندارند.

 مثال ساده Logon Trigger

CREATE TRIGGER TR_Logon_Audit
ON ALL SERVER
FOR LOGON
AS
BEGIN
    INSERT INTO Master.dbo.ServerLogonAudit
    (
        LoginName,
        LoginTime,
        ClientHost
    )
    SELECT 
        ORIGINAL_LOGIN(),
        GETDATE(),
        (SELECT client_net_address 
         FROM sys.dm_exec_connections 
         WHERE session_id = @@SPID);
END;

نکته: Azure SQL Database از تریگرهای ورود پشتیبانی نمی‌کند.

 تریگرهای سروری   ALL SERVER

تریگرهایی که در سطح کل سرور تعیین می‌شوند و می‌توانند بر رویدادهای متعددی نظیر DDL سرور، Logon، تغییر ایندکس‌ها و دیگر رویدادهای سطح سرور واکنش نشان دهند.

زمانی که بخواهیم رویدادی در سطح سرور کنترل شود (مثلاً زمانی که دیتابیسی جدید ایجاد می‌شود یا کلمه عبور تغییر می‌کند)، از تریگر سرور استفاده می‌کنیم.

این نوع تریگر باید با گزینه ON ALL SERVER تعریف شود.

 مثال کاربردی

-- تریگر سروری که هنگام ایجاد دیتابیس جدید، یک پیام یا هشدار ارسال می‌کند
CREATE TRIGGER TR_Server_CreateDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
    DECLARE @xml XML = EVENTDATA();
    DECLARE @dbName NVARCHAR(200) = @xml.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(200)');

    RAISERROR('A new database %s was created on server at %s', 16, 1, @dbName, GETDATE());
END;

در این مثال، هنگام ایجاد هر دیتابیس جدید، پیامی خطا (با سطح 16) صادر می‌شود که می‌تواند در مانیتورینگ یا هشدار استفاده شود.

 نحوه ایجاد، تغییر (ALTER) و حذف (DROP) تریگرها

در این بخش، ساختار دستورات کلی به همراه نکات عملی برای نحوه ایجاد، تغییر و حذف تریگر ارائه می‌شود.

ساخت تریگر: CREATE TRIGGER

هنگام ایجاد تریگر، باید بدانیم در کدام جدول/نما (یا سطح سرور/دیتابیس) و در چه نوع رویدادی می‌خواهد اجرا شود.

ساختار کلی:

CREATE TRIGGER [schema_name.] trigger_name
ON { table_or_view | DATABASE | ALL SERVER }
[ WITH <options> ]
{ FOR | AFTER | INSTEAD OF } 
    { [ INSERT ], [ UPDATE ], [ DELETE ] }
[ NOT FOR REPLICATION ]
AS
BEGIN
    -- بدنه تریگر
END;

برخی گزینه‌ها (WITH <options>) شامل:

•  ENCRYPTION: کد تریگر را رمزگذاری می‌کند
•  EXECUTE AS: تعیین سطح امنیت اجرای تریگر
•  SCHEMABINDING: تضمین اینکه جداول مورد استفاده نمی‌توانند حذف شوند
•  NATIVE_COMPILATION: برای تریگرهای حافظه‌ای (memory-optimized)
•  NOT FOR REPLICATION: تریگر در هنگام تکثیر (replication) اجرا نشود

همچنین از نسخه SQL Server 2016 به بعد امکان استفاده از CREATE OR ALTER TRIGGER فراهم شده است که اگر تریگر وجود داشته باشد آن را تغییر داده و در غیر این صورت ایجاد می‌کند.

 تغییر تریگر: ALTER TRIGGER

اگر نیاز به اصلاح کد تریگر یا تغییر رفتار آن دارید، از دستور:

ALTER TRIGGER [schema_name.] trigger_name
ON { table_or_view | DATABASE | ALL SERVER }
[ WITH <options> ]
{ FOR | AFTER | INSTEAD OF }
    { [ INSERT ], [ UPDATE ], [ DELETE ] }
AS
BEGIN
    -- کد جدید تریگر
END;

  حذف تریگر: DROP TRIGGER

برای حذف تریگر از دستور زیر استفاده می‌شود:

DROP TRIGGER [ IF EXISTS ] [ schema_name. ] trigger_name
    [ ON { table_or_view | DATABASE | ALL SERVER } ];

نکات تریگر  DROP TRIGGER

1.  IF EXISTS از SQL Server 2016 به بعد پشتیبانی می‌شود تا اگر تریگر وجود نداشته باشد خطا ندهد.

2. وقتی جدولی حذف شود، تریگرهای آن جدول نیز به صورت خودکار حذف می‌شوند.

3. برای حذف تریگر سرور یا DDL سرور نیاز به مجوز CONTROL SERVER دارید.

4.همچنین، اگر تریگرهای DDL با ON ALL SERVER تعریف شده باشند، باید ON ALL SERVER را هنگام حذف ذکر کنید.

 

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

نکات عملی و بهترین شیوه‌ها برای استفاده از تریگرها

بهره‌گیری کارا از تریگرها نیازمند رعایت چند نکته و بهترین شیوه‌ها است تا هم کارایی حفظ شود و هم از مشکلات پیچیده اجتناب شود:

1. حداقل کردن منطق داخل تریگر

   ‌تریگرها نباید شامل پردازش‌های سنگین یا محاسبات پیچیده باشند.

اگر منطق سنگینی دارید، تریگر فقط باید وظیفه مقصد را انجام داده و سپس یک اس کیو ال ذخیره‌شده (Stored Procedure) را فراخوانی کند.

2. اجتناب از تأثیر متقابل (Nested / Recursive Triggers) 

 اگر تریگر بر روی جدولی تاثیری بگذارد که خود منجر به وقوع تریگر دیگری شود، می‌تواند به حلقه یا recursion بینجامد.

می‌توانید ویژگی RECURSIVE_TRIGGERS را کنترل کنید و در صورت لزوم DISABLE TRIGGER موقتی انجام دهید.

3. استفاده از SET NOCOUNT ON در ابتدای تریگرها
   برای جلوگیری از بازگرداندن تعداد ردیف‌های تأثیر گذاشته شده و بهبود کارایی.

4. استفاده از TRY...CATCH برای مدیریت خطاها

   برای پوشش خطاها و امکان rollback در تریگرها (در محدودیت‌های مجاز)

5. انقطاع موقتی تریگرها (DISABLE / ENABLE)

  در مواقعی که عملیات دسته‌ای (Bulk) زیادی انجام می‌شود، ممکن است تریگر را غیرفعال کرده، عملیات را انجام دهید و سپس آن را فعال نمایید، اما باید مراقب باشید که داده‌ها ناسازگار نشوند.

6. مستندسازی کامل تریگرها

   توضیح دهید که تریگر چه کاری انجام می‌دهد، دلیل وجود آن، وابستگی‌ها و نکات خاص آن. این کار به نگهداری پروژه کمک بزرگی می‌کند.

7. محدود کردن استفاده از تریگرها در جداول خیلی حساس به عملکرد

   در جداولی که عملیات زیادی روی آن انجام می‌شود، تریگر ممکن است تأثیر منفی بر کارایی بگذارد. باید با احتیاط استفاده شود.

8. انجام تست و مانیتورینگ کارایی

   همیشه پس از افزودن تریگر، تأثیر بر عملکرد را با ابزارهایی مانند SQL Server Profiler یا Extended Events بررسی کنید.

9. محدود کردن دسترسی و مجوزها

   کاربران عادی نباید اجازه تغییر یا حذف تریگرهای حیاتی را داشته باشند. کنترل مجوزها از اهمیت بالایی برخوردار است.

پرسش‌های انواع تریگر (Trigger) در Sql Server 

1. آیا می‌توان تریگر را موقتاً غیرفعال کرد؟

بله، با استفاده از DISABLE TRIGGER و سپس ENABLE TRIGGER می‌توانید به صورت موقت تریگر را غیرفعال کنید.

البته باید مراقب سازگاری داده‌ها باشید چون عملیات ممکن است بدون اجرای تریگر انجام شود.

 2. چگونه بفهمم تریگرهای یک جدول کدامند؟

می‌توانید از نماهایی مانند sys.triggers و sys.trigger_events و همچنین OBJECTPROPERTY و sp_helptrigger استفاده کنید تا لیستی از تریگرهای تعریف‌شده بر روی جدول یا نما به دست آورید.

نتیجه‌گیری

در این مقاله، ابتدا به معرفی مفهوم تریگر و سپس بررسی انواع تریگر در SQL Server پرداختیم تریگرهای DML، تریگرهای DDL، تریگرهای LOGON و تریگرهای سروری.

سپس روش‌های ایجاد (CREATE)، تغییر (ALTER) و حذف (DROP) تریگرها را همراه با مثال‌های عملی مرور کردیم. در بخش نکات عملی، بهترین شیوه‌ها و هشدارهای استفاده از تریگرها بیان شد تا از خطاها و مشکلات عملکردی جلوگیری شود.