دسترسی سریع به مجموعه آموزش‌ها

این راهنما نقشه راه کامل Triggerها در SQL Server است. برای مطالعه عمیق هر دستور یا نوع تریگر از پیوندهای زیر استفاده کنید؛ مقصد همه پیوندها مستقل از دامنه و آماده استفاده در سامانه مدیریت محتواست.

• آموزش کامل دستور CREATE TRIGGER
• آموزش کامل دستور ALTER TRIGGER
• آموزش کامل دستور DROP TRIGGER
• آموزش کامل تریگر DML
• آموزش کامل تریگر DDL
• آموزش کامل تریگر LOGON

Trigger چیست و چه زمانی باید از آن استفاده کرد؟

Trigger یک شیء برنامه‌پذیر در SQL Server است که در پاسخ به رخداد مشخص اجرا می‌شود. رخداد می‌تواند تغییر داده با INSERT، UPDATE یا DELETE، تغییر ساختار با دستورهایی مانند CREATE TABLE و ALTER PROCEDURE، یا ایجاد نشست کاربر در سطح Instance باشد. اجرای خودکار مزیت بزرگی است، زیرا قانون برای همه برنامه‌ها و ابزارهایی که به پایگاه داده متصل می‌شوند یکسان می‌ماند؛ اما همین ویژگی اگر مستند و مانیتور نشود منطق پنهان ایجاد می‌کند.

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

هر Trigger بخشی از تراکنش دستور فراخوان است. اگر تریگر خطا دهد یا تراکنش را Rollback کند، تغییر اصلی نیز بازگردانده می‌شود. اگر Query داخلی کند باشد، قفل‌های دستور اصلی طولانی‌تر نگه داشته می‌شوند و احتمال Blocking و Deadlock افزایش می‌یابد. بنابراین طراحی درست از شناخت مدل تراکنش، مجموعه ردیف‌ها، سطح دسترسی و الگوی بار کاری آغاز می‌شود.

مقایسه دستورها و انواع Trigger

دستور CREATE TRIGGER

دستور CREATE TRIGGER تعریف اولیه یک تریگر DML، DDL یا LOGON را ایجاد می‌کند. انتخاب دامنه ON، رویداد و زمان اجرا باید روشن باشد. در نسخه‌هایی که CREATE OR ALTER پشتیبانی می‌شود می‌توان استقرار تکرارپذیرتری ساخت، اما سیاست نسخه هدف باید از قبل کنترل شود.

برای Syntax، پارامترها، مثال‌های قابل اجرا، خطاهای رایج و نکات Performance، آموزش تخصصی دستور CREATE TRIGGER با مثال‌های عملی را بخوانید.

دستور ALTER TRIGGER

ALTER TRIGGER منطق شیء موجود را بدون تغییر نام و هویت آن جایگزین می‌کند. تغییر باید همراه آزمون بازگشت، مقایسه طرح اجرا و سناریوی چندردیفی باشد. بهتر است تعریف جدید در کنترل نسخه و استقرار آن داخل یک Change Script قابل ممیزی قرار گیرد.

برای Syntax، پارامترها، مثال‌های قابل اجرا، خطاهای رایج و نکات Performance، آموزش تخصصی دستور ALTER TRIGGER با مثال‌های عملی را بخوانید.

دستور DROP TRIGGER

DROP TRIGGER شیء را از دامنه جدول، دیتابیس یا سرور حذف می‌کند و با DISABLE تفاوت دارد. پیش از حذف باید وابستگی، تعریف فعلی و نیاز بازیابی ثبت شود. حذف شرطی و تراکنشی در اسکریپت استقرار از شکست اجرای مجدد جلوگیری می‌کند.

برای Syntax، پارامترها، مثال‌های قابل اجرا، خطاهای رایج و نکات Performance، آموزش تخصصی دستور DROP TRIGGER با مثال‌های عملی را بخوانید.

تریگر DML

تریگر DML به تغییر داده واکنش نشان می‌دهد و می‌تواند AFTER یا INSTEAD OF باشد. جداول inserted و deleted همیشه مجموعه تلقی می‌شوند. استفاده از Cursor یا متغیر تک‌ردیفی، یکی از دلایل کلاسیک خطای منطقی در عملیات دسته‌ای است.

برای Syntax، پارامترها، مثال‌های قابل اجرا، خطاهای رایج و نکات Performance، آموزش تخصصی تریگر DML با مثال‌های عملی را بخوانید.

تریگر DDL

تریگر DDL رخدادهای ساختاری را در سطح DATABASE یا ALL SERVER می‌بیند و EVENTDATA اطلاعات XML رخداد را فراهم می‌کند. این قابلیت برای ممیزی استقرار و حفاظت محدود مفید است؛ با این حال نباید جایگزین کنترل دسترسی، مهاجرت نسخه‌دار و Extended Events شود.

برای Syntax، پارامترها، مثال‌های قابل اجرا، خطاهای رایج و نکات Performance، آموزش تخصصی تریگر DDL با مثال‌های عملی را بخوانید.

تریگر LOGON

تریگر LOGON پس از احراز هویت و پیش از تکمیل نشست اجرا می‌شود. یک شرط اشتباه می‌تواند همه کاربران را قفل کند؛ بنابراین اتصال Dedicated Administrator، استثنای sysadmin، زمان‌بندی بازگشت و آزمون روی محیط جداگانه الزامی است.

برای Syntax، پارامترها، مثال‌های قابل اجرا، خطاهای رایج و نکات Performance، آموزش تخصصی تریگر LOGON با مثال‌های عملی را بخوانید.

شش مثال یکپارچه و کاربردی

مثال 1: ساختار پایه و اجرای نخستین سناریو

این مثال از یک سناریوی کوچک و قابل مشاهده شروع می‌کند تا ترتیب اجرای دستور، رویداد و اثر نهایی روشن شود. تمرکز این Query روی Triggers است و نام اشیای آزمایشی با پیشوند G17 انتخاب شده تا پاک‌سازی و تشخیص آن‌ها ساده باشد.

DROP TABLE IF EXISTS dbo.G17_Main_Log,dbo.G17_Main_Order; CREATE TABLE dbo.G17_Main_Order(ID int PRIMARY KEY,Amount int); CREATE TABLE dbo.G17_Main_Log(ID int,ActionName nvarchar(10));
EXEC(N'CREATE TRIGGER dbo.TR_G17_Main_Insert ON dbo.G17_Main_Order AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT dbo.G17_Main_Log SELECT ID,N''INSERT'' FROM inserted; END');
INSERT dbo.G17_Main_Order VALUES(1,100),(2,200); SELECT * FROM dbo.G17_Main_Log;
DROP TRIGGER dbo.TR_G17_Main_Insert; DROP TABLE dbo.G17_Main_Log,dbo.G17_Main_Order;

نکته کاربردی: این الگو را پیش از انتقال به تولید با عملیات چندردیفی، مقدار NULL و خطای عمدی آزمایش کنید. در سناریوی واقعی باید دسترسی‌ها حداقلی، نام اشیا ثابت، تراکنش کوتاه و جدول مقصد دارای ایندکس مناسب باشد؛ همچنین ثبت رخداد نباید اطلاعات حساس غیرضروری را ذخیره کند.

مثال 2: ثبت ممیزی روی جدول عملیاتی

در این سناریو تغییرات یک جدول کسب‌وکار در جدول ممیزی ذخیره می‌شود تا زمان، کاربر و نوع عملیات قابل پیگیری باشد. تمرکز این Query روی Triggers است و نام اشیای آزمایشی با پیشوند G17 انتخاب شده تا پاک‌سازی و تشخیص آن‌ها ساده باشد.

DROP TABLE IF EXISTS dbo.G17_Main_Audit,dbo.G17_Main_Product; CREATE TABLE dbo.G17_Main_Product(ID int PRIMARY KEY,Price int); CREATE TABLE dbo.G17_Main_Audit(ID int,OldPrice int,NewPrice int);
EXEC(N'CREATE TRIGGER dbo.TR_G17_Main_Update ON dbo.G17_Main_Product AFTER UPDATE AS BEGIN SET NOCOUNT ON; INSERT dbo.G17_Main_Audit SELECT i.ID,d.Price,i.Price FROM inserted i JOIN deleted d ON i.ID=d.ID; END');
INSERT dbo.G17_Main_Product VALUES(1,10); UPDATE dbo.G17_Main_Product SET Price=12 WHERE ID=1; SELECT * FROM dbo.G17_Main_Audit;
DROP TRIGGER dbo.TR_G17_Main_Update; DROP TABLE dbo.G17_Main_Audit,dbo.G17_Main_Product;

نکته کاربردی: این الگو را پیش از انتقال به تولید با عملیات چندردیفی، مقدار NULL و خطای عمدی آزمایش کنید. در سناریوی واقعی باید دسترسی‌ها حداقلی، نام اشیا ثابت، تراکنش کوتاه و جدول مقصد دارای ایندکس مناسب باشد؛ همچنین ثبت رخداد نباید اطلاعات حساس غیرضروری را ذخیره کند.

مثال 3: پردازش چندردیفی و مجموعه‌محور

هدف این مثال نشان‌دادن این نکته است که inserted و deleted ممکن است هم‌زمان چند ردیف داشته باشند و منطق نباید تک‌ردیفی نوشته شود. تمرکز این Query روی Triggers است و نام اشیای آزمایشی با پیشوند G17 انتخاب شده تا پاک‌سازی و تشخیص آن‌ها ساده باشد.

DROP TABLE IF EXISTS dbo.G17_Main_Rule; CREATE TABLE dbo.G17_Main_Rule(ID int PRIMARY KEY,Qty int);
EXEC(N'CREATE TRIGGER dbo.TR_G17_Main_Rule ON dbo.G17_Main_Rule AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON; IF EXISTS(SELECT 1 FROM inserted WHERE Qty<0) THROW 51004,N''مقدار منفی مجاز نیست.'',1; END');
BEGIN TRY INSERT dbo.G17_Main_Rule VALUES(1,-1); END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS Result; END CATCH;
DROP TRIGGER dbo.TR_G17_Main_Rule; DROP TABLE dbo.G17_Main_Rule;

نکته کاربردی: این الگو را پیش از انتقال به تولید با عملیات چندردیفی، مقدار NULL و خطای عمدی آزمایش کنید. در سناریوی واقعی باید دسترسی‌ها حداقلی، نام اشیا ثابت، تراکنش کوتاه و جدول مقصد دارای ایندکس مناسب باشد؛ همچنین ثبت رخداد نباید اطلاعات حساس غیرضروری را ذخیره کند.

مثال 4: استفاده در مسیر UPDATE

این نمونه رفتار موضوع را هنگام به‌روزرسانی بررسی می‌کند و تفاوت مقدار قدیم و جدید را بدون اتکا به متغیر اسکالر نشان می‌دهد. تمرکز این Query روی Triggers است و نام اشیای آزمایشی با پیشوند G17 انتخاب شده تا پاک‌سازی و تشخیص آن‌ها ساده باشد.

DROP TABLE IF EXISTS dbo.G17_Main_BatchLog,dbo.G17_Main_Batch; CREATE TABLE dbo.G17_Main_Batch(ID int PRIMARY KEY); CREATE TABLE dbo.G17_Main_BatchLog(ID int);
EXEC(N'CREATE TRIGGER dbo.TR_G17_Main_Batch ON dbo.G17_Main_Batch AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT dbo.G17_Main_BatchLog SELECT ID FROM inserted; END');
INSERT dbo.G17_Main_Batch VALUES(1),(2),(3); SELECT COUNT(*) AS LoggedRows FROM dbo.G17_Main_BatchLog;
DROP TRIGGER dbo.TR_G17_Main_Batch; DROP TABLE dbo.G17_Main_BatchLog,dbo.G17_Main_Batch;

نکته کاربردی: این الگو را پیش از انتقال به تولید با عملیات چندردیفی، مقدار NULL و خطای عمدی آزمایش کنید. در سناریوی واقعی باید دسترسی‌ها حداقلی، نام اشیا ثابت، تراکنش کوتاه و جدول مقصد دارای ایندکس مناسب باشد؛ همچنین ثبت رخداد نباید اطلاعات حساس غیرضروری را ذخیره کند.

مثال 5: اعمال شرط تجاری و کنترل تراکنش

سناریو یک قانون کسب‌وکار را در مرز پایگاه داده اعمال می‌کند و نحوه بازگردانی تغییر نامعتبر را به‌شکل شفاف نمایش می‌دهد. تمرکز این Query روی Triggers است و نام اشیای آزمایشی با پیشوند G17 انتخاب شده تا پاک‌سازی و تشخیص آن‌ها ساده باشد.

DROP TABLE IF EXISTS dbo.G17_Main_DDL_Audit; CREATE TABLE dbo.G17_Main_DDL_Audit(EventType sysname,ObjectName sysname);
IF EXISTS(SELECT 1 FROM sys.triggers WHERE name=N'TR_G17_Main_DDL' AND parent_class_desc=N'DATABASE') DROP TRIGGER TR_G17_Main_DDL ON DATABASE;
EXEC(N'CREATE TRIGGER TR_G17_Main_DDL ON DATABASE FOR CREATE_TABLE AS BEGIN SET NOCOUNT ON; DECLARE @x xml=EVENTDATA(); INSERT dbo.G17_Main_DDL_Audit SELECT @x.value(''(/EVENT_INSTANCE/EventType)[1]'',''sysname''),@x.value(''(/EVENT_INSTANCE/ObjectName)[1]'',''sysname''); END');
DROP TABLE IF EXISTS dbo.G17_Main_DDL_Target; CREATE TABLE dbo.G17_Main_DDL_Target(ID int); SELECT * FROM dbo.G17_Main_DDL_Audit WHERE ObjectName=N'G17_Main_DDL_Target';
DROP TRIGGER TR_G17_Main_DDL ON DATABASE; DROP TABLE dbo.G17_Main_DDL_Target,dbo.G17_Main_DDL_Audit;

نکته کاربردی: این الگو را پیش از انتقال به تولید با عملیات چندردیفی، مقدار NULL و خطای عمدی آزمایش کنید. در سناریوی واقعی باید دسترسی‌ها حداقلی، نام اشیا ثابت، تراکنش کوتاه و جدول مقصد دارای ایندکس مناسب باشد؛ همچنین ثبت رخداد نباید اطلاعات حساس غیرضروری را ذخیره کند.

مثال 6: رفتار مقدار NULL و داده ناقص

در این مثال حالت NULL عمداً وارد جریان می‌شود تا مقایسه سه‌ارزشی SQL و استفاده درست از COALESCE یا شرط IS NULL دیده شود. تمرکز این Query روی Triggers است و نام اشیای آزمایشی با پیشوند G17 انتخاب شده تا پاک‌سازی و تشخیص آن‌ها ساده باشد.

SELECT name,parent_class_desc,is_disabled,create_date,modify_date
FROM sys.triggers
ORDER BY parent_class_desc,name;

نکته کاربردی: این الگو را پیش از انتقال به تولید با عملیات چندردیفی، مقدار NULL و خطای عمدی آزمایش کنید. در سناریوی واقعی باید دسترسی‌ها حداقلی، نام اشیا ثابت، تراکنش کوتاه و جدول مقصد دارای ایندکس مناسب باشد؛ همچنین ثبت رخداد نباید اطلاعات حساس غیرضروری را ذخیره کند.

معماری، کارایی و مشاهده‌پذیری

طراحی حرفه‌ای Trigger با ترسیم جریان داده شروع می‌شود: رخداد ورودی، مجموعه ردیف‌ها، جداول خوانده‌شده، جداول نوشته‌شده، خطاهای ممکن و اثر Rollback مشخص می‌شوند. سپس برای هر اتصال ایندکس مناسب بررسی و تعداد Queryها کم می‌شود. اگر تنها هدف ثبت رخداد است، ثبت سبک در جدول باریک و پردازش غیرهم‌زمان بعدی معمولاً بهتر از محاسبه کامل گزارش درون تریگر است.

اندازه‌گیری باید پیش و پس از تغییر انجام شود. مدت اجرای دستور اصلی، CPU، logical reads، اندازه Transaction Log، زمان انتظار قفل و نرخ Deadlock شاخص‌های مهم هستند. Extended Events برای رخدادها و خطاها، Query Store برای Queryهای قابل مشاهده، Dynamic Management Viewها برای نشست و قفل، و برنامه اجرا برای تشخیص Scan و تخمین نادرست به کار می‌روند. هدف صفرکردن هزینه نیست، بلکه قابل قبول و پایدارکردن آن زیر بار واقعی است.

در امنیت، اصل حداقل دسترسی اجرا می‌شود. حساب برنامه نباید بتواند تریگرهای سروری یا دیتابیسی را تغییر دهد. جدول ممیزی باید در برابر تغییر کاربران عادی محافظت شود و داده حساس بیش از نیاز ثبت نشود. LOGON Trigger آخرین انتخاب برای محدودیت ورود است، زیرا یک خطا می‌تواند دسترسی مدیریتی را مختل کند؛ Policy، Firewall، Login Permission یا Resource Governor در بسیاری سناریوها شفاف‌ترند.

سؤالات متداول

پرسش 1: Triggers دقیقاً چه مسئله‌ای را حل می‌کند؟

این موضوع اجرای خودکار یا مدیریت منطق واکنشی را نزدیک داده ممکن می‌کند. انتخاب آن باید پس از بررسی جایگزین‌هایی مانند قید، رویه ذخیره‌شده، Extended Events و لایه سرویس انجام شود تا پیچیدگی پنهان ایجاد نشود.

پرسش 2: برای شروع یادگیری Triggers چه پیش‌نیازهایی لازم است؟

شناخت تراکنش، سطوح دسترسی، دستورهای DML و DDL و شیوه کار جداول منطقی inserted و deleted پایه مناسبی است. تمرین روی پایگاه آزمایشی و مشاهده execution plan و آمار زمان اجرا مسیر یادگیری را بسیار کوتاه‌تر می‌کند.

پرسش 3: آیا استفاده از Triggers برای یک پروژه تجاری مناسب است؟

بله، وقتی الزام ممیزی یا قانون یکپارچگی باید مستقل از برنامه‌های متعدد اجرا شود انتخاب خوبی است. در پروژه تجاری لازم است هزینه نگهداری، مانیتورینگ، آزمون بار و روش غیرفعال‌سازی اضطراری نیز در برآورد فنی دیده شود.

پرسش 4: هزینه طراحی و پیاده‌سازی حرفه‌ای Triggers چگونه تعیین می‌شود؟

حجم تراکنش، تعداد جدول‌ها، حساسیت امنیتی، نیاز گزارش‌گیری و سطح آزمون عوامل اصلی هستند. پیش از برآورد، مشاوره SQL Server باید سناریوهای شکست، هم‌زمانی و بازیابی را استخراج کند تا دامنه کار دقیق باشد.

پرسش 5: Triggers چه تفاوتی با اجرای همان منطق در برنامه دارد؟

منطق تریگر برای همه مصرف‌کنندگان داده یکسان اجرا می‌شود، اما در برنامه مشاهده‌پذیری و کنترل جریان ساده‌تر است. تصمیم درست معمولاً ترکیبی است: قواعد حیاتی نزدیک داده و گردش‌کارهای طولانی در سرویس برنامه قرار می‌گیرند.

پرسش 6: برای بازبینی یا اصلاح Triggers موجود چه خدمتی لازم است؟

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

پرسش 7: رایج‌ترین خطا در Triggers چیست؟

فرض تک‌ردیفی بودن inserted یا deleted، حذف SET NOCOUNT ON و اجرای Query سنگین برای هر ردیف از خطاهای پرتکرار هستند. راه‌حل، منطق مجموعه‌محور، تراکنش کوتاه، مدیریت خطا و تست INSERT یا UPDATE دسته‌ای است.

پرسش 8: Triggers چه اثری بر Performance دارد؟

زمان اجرای تریگر بخشی از زمان همان تراکنش است و قفل‌ها را طولانی‌تر نگه می‌دارد. ایندکس ستون‌های اتصال، محدودکردن ستون‌های ثبت‌شده، پرهیز از فراخوانی شبکه و پایش Query Store یا Extended Events ضروری است.

پرسش 9: بهترین روش نگهداری Triggers چیست؟

تعریف شیء باید در کنترل نسخه باشد و همراه با آزمون مثبت، منفی، NULL، چندردیفی و هم‌زمانی منتشر شود. نام‌گذاری استاندارد، توضیح هدف، مالک مشخص و اسکریپت Rollback باعث می‌شود تغییرات بعدی قابل اعتماد بماند.

پرسش 10: Triggers با کدام نسخه‌های SQL Server سازگار است؟

هسته قابلیت در نسخه‌های متداول SQL Server وجود دارد، ولی گزینه‌هایی مانند CREATE OR ALTER یا برخی رویدادها به نسخه وابسته‌اند. پیش از استقرار باید مستندات همان نسخه، Compatibility Level و محدودیت Azure SQL بررسی شود.

سؤالات مصاحبه تخصصی

سؤال 1: چرا یک تریگر باید مجموعه‌محور باشد؟

زیرا یک دستور می‌تواند صفر، یک یا هزاران ردیف را تغییر دهد و inserted یا deleted نماینده کل مجموعه است، نه یک ردیف تضمین‌شده.

سؤال 2: SET NOCOUNT ON چه فایده‌ای دارد؟

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

سؤال 3: چگونه اثر کارایی را اندازه می‌گیرید؟

مدت تراکنش، CPU، logical reads، waitها، قفل‌ها و برنامه اجرای Queryهای داخل تریگر پیش و پس از تغییر مقایسه می‌شوند.

سؤال 4: چه زمانی تریگر انتخاب مناسبی نیست؟

وقتی پردازش طولانی، تماس شبکه، ارسال ایمیل یا گردش‌کار قابل تکرار لازم است، صف یا سرویس پس‌زمینه انتخاب امن‌تری است.

سؤال 5: استقرار Triggers چگونه ایمن می‌شود؟

با نسخه‌گذاری، آزمون روی داده مشابه تولید، پنجره استقرار، اسکریپت بازگشت، حساب اضطراری و مانیتورینگ پس از انتشار.

جمع‌بندی و مسیر مطالعه

Triggerها ابزار قدرتمندی هستند، اما قدرت آن‌ها از اجرای خودکار درون تراکنش می‌آید و همین نقطه منبع ریسک نیز هست. منطق مجموعه‌محور، قرارداد روشن برای خطا، تراکنش کوتاه، ایندکس مناسب، کنترل نسخه و آزمون بار اصول غیرقابل مذاکره‌اند. هر تریگر باید مالک، هدف، معیار کارایی و اسکریپت بازگشت داشته باشد.

برای تکمیل مسیر، مقاله‌های دستور CREATE TRIGGER، دستور ALTER TRIGGER، دستور DROP TRIGGER، تریگر DML، تریگر DDL، تریگر LOGON را به‌ترتیب نیاز پروژه مطالعه کنید. هر صفحه مثال‌های مستقل، خروجی نمونه، FAQ و نکات استقرار دارد و می‌تواند به‌عنوان چک‌لیست بازبینی کد نیز استفاده شود.