تعریف Trigger در SQL Server

تعریف Trigger در کوئری نویسی در SQL Server

دستور CREATE TRIGGER به شما امکان می دهد یکtriggerجدید ایجاد کنید که هر زمان که رویدادی مانند INSERT، DELETE یا UPDATE در جدول رخ می دهد، به طور خودکار فعال می شود.

شکل زیر Syntax دستور CREATE TRIGGER را نشان می دهد:

CREATE TRIGGER [schema_name.]trigger_name
ON TABLE_NAME
AFTER {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
{sql_statements}

در این Syntax:
Schema_name نام اسکیمای است که Trigger جدید به آن تعلق دارد. نام اسکیمااختیاری است.
Trigger_name نام تعریف شده توسط کاربر برای Triggerجدید است.
table_name جدولی است که Trigger روی آن اعمال می شود.
رویداد در بند AFTER ذکر شده است. رویداد می تواند insert-update-deleteباشد. یک Triggerواحد می تواند در پاسخ به یک یا چند اقدام در یک جدول فعال شود.

گزینه NOT FOR RePLICATION به SQL Server دستور می دهد تا زمانی که اصلاح داده ها به عنوان بخشی از فرآیند تکرار انجام می شود، trigger را روشن نکنید
SQL_statements یک یا چند دستور T-SQLاست که برای انجام اقدامات پس از وقوع یک رویداد استفاده می شود.

جداول مجازی(inserted-deleted) برای Triggers

SQL Server دو جدول مجازی ارائه می دهد که به طور خاص برای Triggerبه نام جداول INSERTED و DELETED در دسترس هستند. SQL Server از این جداول برای گرفتن داده های ردیف اصلاح شده قبل و بعد از وقوع رویداد استفاده می کند.
جدول زیر محتوای جداول INSERTED و DELETED را قبل و بعد از هر رویداد نشان می دهد:

مثالی از کاربرد Trigger در SQL Server

گام ۱) یک جدول برای ثبت تغییرات ایجاد کنید.

بیایید به مثالی از ایجاد یک Triggerجدید نگاه کنیم. ما از جدول Products از اسکیمای Production از پایگاه داده نمونه برای نمایش استفاده خواهیم کرد.

عبارت زیر جدولی به نام product_audits از اسکیمای Productionایجاد می کند تا زمانی که یک رویداد INSERT یا DELETE در جدول .products از اسکیمای Productionرخ می دهد، اطلاعات را ثبت کند:
برای این منظور با استفاده از دستور Create table جدولی به نام Products_audits از اسکیمای Production ایجاد کرده که دارای فیلدهای change id,product id, Product_name,brand_id,category_id, model_year,lsit_priceupdated at,operation,check می باشد
برای این منظور کوئری زیر را می نویسیم.

CREATE TABLE production.product_audits(
change_id INT IDENTITY PRIMARY KEY,
product_id INT NOT NULL,
product_name VARCHAR(255) NOT NULL,
brand_id INT NOT NULL,
category_id INT NOT NULL,
model_year SMALLINT NOT NULL,
list_price DEC(10,2) NOT NULL,
updated_at DATETIME NOT NULL,
operation CHAR(3) NOT NULL,
CHECK(operation = 'INS' OR operation='DEL')
);

گام ۲) ایجاد یک تریگر پس از DML

ابتدا، برای ایجاد یک Trigger جدید، نام و اسکیمای را که Triggerبه آن تعلق دارد در عبارت CREATE TRIGGER مشخص کنید:

CREATE TRIGGER production.trg_product_audit

در مرحله بعد، نام جدولی را که تریگر هنگام وقوع یک رویداد فعال می شود، در عبارت ON مشخص می کنید:

ON production.products

سپس، یک یا چند رویداد را فهرست می‌کنید که Triggerرا در عبارت AFTER فراخوانی می‌کنند:

AFTER INSERT, DELETE

بدنه Triggerبا کلمه کلیدی AS شروع می شود:

AS

BEGIN

پس از آن، در داخل بدنه Trigger، SET NOCOUNT را روی ON تنظیم می کنید تا تعداد ردیف هایی که پیام های تحت تاثیر قرار می گیرند، هر زمان که Triggerفعال می شود، بازگردانده نشوند.

SET nocount ON;

هر زمان که ردیفی در جدول Production.Products حذف شود یا از آن حذف شود،Trigger یک ردیف را در جدول Product_audits از اسکیمای Productionوارد می کند. داده های درج شده از جداول INSERTED و DELETED از طریق عملگر UNION ALL تغذیه می شود:

INSERT INTO
production.product_audits
(
product_id,
product_name,
brand_id,
category_id,
model_year,
list_price,
updated_at,
operation
)
SELECT
i.product_id,
product_name,
brand_id,
category_id,
model_year,
i.list_price,
GETDATE(),
'INS'
FROM
inserted AS i
UNION ALL
SELECT
d.product_id,
product_name,
brand_id,
category_id,
model_year,
d.list_price,
getdate(),
'DEL'
FROM
deleted AS d

حال در صورتیکه که کلیه کوئری های فوق را کنار هم بگذاریم

--create trigger ایجاد
--trg_product_audit به نام
--production از اسکمای
CREATE TRIGGER production.trg_product_audit
ON production.products
AFTER INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO production.product_audits(
product_id,
product_name,
brand_id,
category_id,
model_year,
list_price,
updated_at,
operation
)
SELECT
i.product_id,
product_name,
brand_id,
category_id,
model_year,
i.list_price,
GETDATE(),
'INS'
FROM
inserted i
UNION ALL
SELECT
d.product_id,
product_name,
brand_id,
category_id,
model_year,
d.list_price,
GETDATE(),
'DEL'
FROM
deleted d;
END

در نهایت، کل دستور را برای ایجاد Trigger اجرا می کنید. هنگامی کهTrigger ایجاد شد، می توانید آن را در زیر پوشه Triggerجدول پیدا کنید، همانطور که در تصویر زیر نشان داده شده است:

گام سوم) آزمایش Trigger

عبارت زیر یک ردیف جدید را در جدول Product از اسکیمای Production وارد می کند:

برای این منظور با استفاده از دستور insert into-values یک رکورد به جدول products از اسکیمای Production اضافه می کنید:

INSERT INTO production.products(
product_name,
brand_id,
category_id,
model_year,
list_price
)
VALUES (
'Test product',
۱,
۱,
۲۰۱۸,
۵۹۹

);

به دلیل رویداد INSERT، یک Trigger به نام trg_product_audit از اسکیمای Production جدول production.products فعال شد.

بیایید محتویات جدول production.product_audits را بررسی کنیم:

حال در ادامه می خواهیم کوئری بنویسیم از جدول Products_aduits اسکیمای Production کلیه رکوردهای جدول را فراخوانی کنیم :

برای این منظور کوئری زیر را می نویسیم:

SELECT
*
FROM
production.product_audits;

خروجی کوئری فوق مطابق تصویر زیر می باشد:

عبارت زیر یک ردیف را از جدول Products اسکیمای Production حذف میکند.

DELETE FROM
production.products
WHERE
product_id = 322;

*
FROM
production.product_audits;

همانطور که انتظار می رفت،Trigger فعال شد و ردیف حذف شده در جدول Products_aduits از اسکیمای Production قرار داد

خروجی کوئری فوق مطابق تصویر زیر می باشد:

در این آموزش یاد گرفتید که چگونه در SQL Server یک تریگر ایجاد کنید تا به یک یا چند رویداد مانند insert,delete پاسخ دهد.

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