تریگرها در SQL Server

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

به عنوان مثال، سناریویی را در نظر بگیرید که در آن حقوق یک کارمند در جدول Employee به روز می شود. ممکن است بخواهید جزئیات حقوق قبلی را قبل از اینکه به ارزش جدید به روز شود، در یک جدول حسابرسی جداگانه حفظ کنید. هر زمان که مقدار جدول کارمند به روزرسانی شد، می توانید یک ماشه ایجاد کنید تا به طور خودکار داده های به روزرسانی شده کارکنان را در جدول حسابرسی جدید وارد کنید.

سه نوع تریگر در SQL Server وجود دارد

• هنگامی که یک رویداد INSERT، UPDATE یا DELETE روی یک جدول رخ می دهد، محرک های DML به طور خودکار فعال می شوند.
• هنگامی که یک رویداد CREATE، ALTER یا DROP در پایگاه داده رخ می دهد، تریگرهای DDL به طور خودکار فراخوانی می شوند. در پاسخ به یک رویداد با دامنه سرور یا پایگاه داده اجرا می شود.
• هنگامی که یک رویداد LOGON در هنگام ایجاد یک جلسه کاربر ایجاد می شود، ماشه ورود به سیستم فراخوانی می شود.

محرک های DML

هنگامی که دستور INSERT، UPDATE یا DELETE روی جدول اجرا می شود، ماشه DML (زبان دستکاری داده ها) به طور خودکار فراخوانی می شود.

از دستور CREATE TRIGGER برای ایجاد یک ماشه در SQL Server استفاده کنید.

در نحو بالا:

• schema_name (اختیاری) نام طرحی است که در آن تریگر جدید ایجاد می شود.
• trigger_name نام تریگر جدید است.
• برکلمه کلیدی جدول یا نام نمایشی را مشخص می کند که تریگر روی آن ایجاد می شود.
• بند AFTER رویداد INSERT، UPDATE یا DELETE را مشخص می کند که ماشه را فعال می کند. بند AFTER مشخص می کند که تریگر تنها پس از اینکه SQL Server با موفقیت اجرای عملیاتی را که آن را اجرا کرده است، فعال می شود. تمام اقدامات و محدودیت های دیگر باید قبل از شلیک ماشه با موفقیت اجرا شوند.
• عبارت STEAD OF برای پرش از دستور INSERT، UPDATE یا DELETE به جدول استفاده می شود و در عوض، سایر دستورات تعریف شده در تریگر را اجرا می کند. بنابراین، عبارت واقعی INSERT، UPDATE یا DELETE اصلا اتفاق نمی افتد. نمی توان از بند به جای تریگرهای DDL استفاده کرد.
• عبارت [NOT FOR RePLICATION] برای دستور دادن به SQL Server مشخص شده است که وقتی یک عامل تکرار جدول را تغییر می دهد، ماشه را فراخوانی نکند.
• sql_statements عملی را که باید هنگام وقوع یک رویداد اجرا شود را مشخص می کند.

تریگرهای DML از دو جدول موقت ویژه به نام جداول درج شده و جداول حذف شده استفاده می کنند. SQL Server به طور خودکار این جداول را ایجاد و مدیریت می کند. SQL Server از این جداول برای یافتن وضعیت جدول قبل و بعد از تغییر داده ها استفاده می کند و بر اساس آن تفاوت اقدام می کند.

بیایید یک ماشه ایجاد کنیم که در عملیات INSERT، UPDATE و DELETE در جدول Employee فعال شود. برای آن، یک جدول جدید EmployeeLog ایجاد کنید تا تمام عملیات انجام شده در جدول Employee را ثبت کنید.

در جدول بالا، LogID شماره سریال با افزایش خودکار است، UpdatedDate تاریخی است که جدول Employee در آن به روز شده است. ستون Operation نوع عملیات انجام شده را در جدول ذخیره می کند. یا "INSERT"، "UPDATE"، یا "DELETE".

برای محرک ها

محرک های FOR را می توان روی جداول یا نماها تعریف کرد. تنها زمانی فعال می شود که تمام عملیات مشخص شده در دستور SQL راه اندازی با موفقیت آغاز شود. تمام اقدامات آبشاری ارجاعی و بررسی های محدودیت نیز باید قبل از شروع آتش سوزی با موفقیت انجام شود.

FOR زیر در عملیات INSERT در جدول Employee فعال می شود.

The above will create the trgEmployeeInsert trigger in the ->پوشه Triggers، همانطور که در زیر نشان داده شده است.

تریگرها در SQL Server

برای مشاهده رکوردهای موجود، دستور انتخاب را در جداول Employee و EmpLog اجرا کنید.

جدول EmpLog زیر است.

حال، عبارت INSERT زیر را اجرا کنید تا تریگر trgEmployeeInsert را فعال کند.

مطابق شکل زیر یک ردیف جدید در جدول Employee وارد می کند.

trgEmployeeInsert فعال می شود و مانند شکل زیر یک ردیف در جدول EmpLog قرار می دهد.

می بینید که برای هر دستور INSERT برای جدول Employee یک ردیف جدید در جدول EmpLog درج شده است.

توجه: به هر دلیلی، اگر تریگرهای FOR از کار بیفتند، INSERT نیز از کار افتاده و هیچ ردیفی درج نخواهد شد.

پس از محرک ها

ماشه AFTER تنها پس از اتمام موفقیت آمیز عبارت محرک SQL فعال می شود. محرک های AFTER را نمی توان روی نماها تعریف کرد.

برای مثال، تریگر زیر پس از هر عبارت UPDATE در جدول Employee فعال می شود.

برای آزمایش این تریگر، عبارت UPDATE زیر را اجرا کنید.

اکنون ردیف ها را از جدول EmpLog انتخاب کنید. تریگر trgEmployeeUpdate باید یک ردیف جدید در جدول EmpLog قرار داده باشد، همانطور که در زیر نشان داده شده است.

به جای محرک ها

به جای Trigger به شما امکان می دهد تا درج ، به روزرسانی یا حذف عملیات را روی یک جدول یا نمای نادیده بگیرید. عملیات واقعی DML به هیچ وجه اتفاق نمی افتد.

به جای Delete Trigger به جای رویداد حذف واقعی روی یک جدول یا نمای اجرا می شود. به جای مثال Delete Trigger در زیر ، هنگامی که یک دستور حذف در جدول کارمند صادر می شود ، یک ردیف جدید در جدول Emplog ایجاد می شود که عملیات را به عنوان "حذف" ذخیره می کند ، اما ردیف حذف نمی شود.

اکنون عبارت حذف زیر را برای آزمایش ماشه فوق اجرا کنید.

بیانیه فوق ماشه Trginsteadofdelete را آتش می زند که به جای حذف یک ردیف در جدول کارمندان ، یک ردیف جدید را در جدول Emplog وارد می کند.

به جای حذف ماشه به همان شیوه برای حذف فله نیز کار می کند. هنگامی که یک عبارت SQL را که چندین ردیف را حذف می کنید اجرا می کنید ، ردیف ها حذف نمی شوند ، اما تعداد مساوی از ردیف ها در جدول Emplog وارد می شوند.

چندین محرک

در SQL Server ، چندین محرک را می توان در یک جدول برای همان رویداد ایجاد کرد. هیچ ترتیب مشخصی برای اجرای این محرک ها وجود ندارد.

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

• نام TriggerName نام ماشه ای است که باید سفارش داده شود
• order = ترتیب ماشه. اول ، آخرین یا هیچکدام
• Stmttype = نوع بیانیه. به روزرسانی ، حذف ، ورود به سیستم یا هر رویداد بیانیه TSQL را که در رویدادهای DDL ذکر شده است ، وارد کنید.
• namespace مشخص می کند که آیا ماشه DDL در پایگاه داده یا سرور ایجاد شده است.

فرض کنید که چندین محرک دارید که در جدول به روزرسانی در جدول کارمندان آتش می گیرند. مثال زیر مشخص می کند که Trigger TrgeMplemeUpdate اولین ماشه برای آتش سوزی پس از انجام یک عمل به روزرسانی در جدول کارمندان است.

با استفاده از SSM یک ماشه DML ایجاد کنید

مرحله 1: SSMS را باز کرده و وارد سرور پایگاه داده شوید. در Object Explorer ، نمونه پایگاه داده را گسترش داده و پایگاه داده ای را که می خواهید یک ماشه ایجاد کنید انتخاب کنید.

مرحله 2: جدول را در جایی که می خواهید یک ماشه ایجاد کنید ، گسترش دهید. بر روی پوشه Triggers راست کلیک کرده و Trigger New را انتخاب کنید. Syntax Create Trigger برای یک ماشه جدید در ویرایشگر پرس و جو باز خواهد شد.

مرحله 3: در منوی پرس و جو ، روی مشخصات مقادیر پارامترهای الگو کلیک کنید.

در کادر گفتگو ، نام ماشه ، تاریخ ایجاد شده ، نام طرحواره ، نویسنده The Trigger را مشخص کنید و پارامترهای دیگر را پر کنید. OK را کلیک کنید

مرحله 4: در ویرایشگر پرس و جو ، در بخش اظهار نظر ، اظهارات SQL را برای ماشه وارد کنید - در اینجا بیانیه هایی را برای ماشه وارد کنید.

مرحله 5: می توانید نحو را با کلیک روی تجزیه و تحلیل در زیر منوی پرس و جو تأیید کنید.

مرحله 6: برای ایجاد ماشه ، روی Execute کلیک کنید.

مرحله 7: جدول را تازه کنید. ماشه جدید در زیر پوشه Triggers جدول ایجاد می شود.< Pan> مرحله 3: در منوی پرس و جو ، روی مشخصات مقادیر پارامترهای الگو کلیک کنید.