آموزش SQL Server DDL Triggers

DDL Triggers در SQL Server در این مقاله، نحوه استفاده از SQL Server data definition language trigger (DDL) برای نظارت بر تغییرات ایجاد شده در Object هایی دیتابیس خواهید آموخت. 

مقدمه ای بر DDL Triggers در SQL Server

DDL Triggers در SQL Server ها به رویدادهای سرور یا data base به جای تغییرات داده های جدول پاسخ می دهند. این رویدادها توسط دستور Transact-SQL ایجاد می‌شوند که معمولاً با یکی از کلیدواژه‌های CREATE، ALTER، DROP، GRANT، DENY، REVOKE یا UPDATE STATISTICS شروع می‌شوند.

برای مثال، می‌توانید هر زمان که یک کاربر دستور CREATE TABLE یا ALTER TABLE را صادر می‌کند، یکtriggerDDL بنویسید تا لاگین کنید.

تریگرهای DDL در موارد زیر مفید هستند:

• ثبت تغییرات در اسکیمای پایگاه داده
• از برخی تغییرات خاص در اسکیمای data baseجلوگیری کنید.
• به تغییر در اسکیمای data baseپاسخ دهید.

شکل زیر نحو ایجاد یکtriggerDDL را نشان می دهد:

CREATE TRIGGER trigger_name
ON { DATABASE | ALL SERVER}
[WITH ddl_trigger_option]
FOR {event_type | event_group }
AS {sql_statement}

مفهوم Trigger Name

نامtrigge r تعریف شده توسط کاربر را بعد از کلمات کلیدی CREATE TRIGGER مشخص کنید. توجه داشته باشید که لازم نیست یک اسکیمای برای یکtriggerDDL مشخص کنید زیرا به جدول یا view data baseمربوط نمی شود. پیشنهاد میکنیم برای درک بهتر مفاهیم کوئری نویسی را مطالعه کنید.

مفهوم DATABASE | ALL SERVER

اگر trigger به رویدادهای محدوده data baseپاسخ می دهد از DATABASE یا اگر trigger به رویدادهای محدوده سرور پاسخ می دهد از ALL SERVER استفاده کنید.

مفهوم ddl_trigger_option

ddl_trigger_option عبارت ENCRYPTION و/یا EXECUTE AS را مشخص می کند. ENCRYPTION تعریف trigger را رمزگذاری می کند. EXECUTE AS زمینه امنیتی را که trigger تحت آن اجرا می شود، تعریف می کند.

مفهوم event_type | event_group

event_type یک رویداد DDL را نشان می‌دهد که باعث می‌شود trigger فعال شود، مانند CREATE_TABLE، ALTER_TABLE و غیره. رویداد گروهی از رویدادهای نوع رویداد مانند DDL_TABLE_EVENTS است. یک trigger می تواند در یک یا چند رویداد یا گروه رویداد مشترک شود. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

مثالی از پیاده سازی DDL Trigger

فرض کنید می خواهید تمام تغییرات ایجاد شده در فهرست پایگاه داده را ثبت کنید تا بتوانید عملکرد سرور پایگاه داده را که به این تغییرات شاخص مربوط می شود نظارت کنید. ابتدا یک جدول جدید به نام index_logs ایجاد کنید تا تغییرات ایندکس را ثبت کنید:

CREATE TABLE index_logs (
log_id INT IDENTITY PRIMARY KEY,
event_data XML NOT NULL,
changed_by SYSNAME NOT NULL
);
GO

سپس، یک trigger DDL برای ردیابی تغییرات ایندکس و درج داده های رویدادها در جدول index_logs ایجاد کنید:

CREATE TRIGGER trg_index_changes
ON DATABASE
FOR
CREATE_INDEX,
ALTER_INDEX,
DROP_INDEX
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO index_logs (
event_data,
changed_by
)
VALUES (
() EVENTDATA,
USER
);
END;
GO

در بدنه trigger، از تابع EVENTDATA استفاده کردیم که اطلاعات مربوط به رویدادهای سرور یا پایگاه داده را برمی گرداند. این عملکرد فقط در داخل DDL یا trigger ورود به سیستم موجود است.

سپس، برای ستون های first_name و last_name جدول sales.customers ایندکس ایجاد کنید:

CREATE NONCLUSTERED INDEX nidx_fname
ON sales.customers(first_name);
GO
CREATE NONCLUSTERED INDEX nidx_lname
ON sales.customers(last_name);
GO

پس از آن، داده‌ها را از جدول index_changes کوئری بزنیدتا بررسی کنید که آیا رویداد ایجاد ایندکس به درستی توسطtriggerثبت شده است یا خیر:

SELECT
*
FROM
index_logs;

اگر روی سلول ستون event_data کلیک کنید، می توانید داده های XML رویداد را به صورت زیر مشاهده کنید:

<EVENT_INSTANCE>
<EventType>CREATE_INDEX</EventType>
<PostTime>2022-01-12T19:32:37.190</PostTime>
<SPID>55</SPID>
<ServerName>DESKTOP-OJBNID1</ServerName>
<LoginName>DESKTOP-OJBNID1VAIO</LoginName>
<UserName>dbo</UserName>
<DatabaseName>BikeStores</DatabaseName>
<SchemaName>sales</SchemaName>
<ObjectName>nidx_fname</ObjectName>
<ObjectType>INDEX</ObjectType>
<TargetObjectName>customers</TargetObjectName>
<TargetObjectType>TABLE</TargetObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE NONCLUSTERED INDEX nidx_fname
ON sales.customers(first_name)</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

سخن پایانی

DDL Triggers در SQL Server در این آموزش، یاد گرفتید که چگونه یک SQL Server DDL trigger ایجاد کنید که به یک یا چند رویداد DDL پاسخ دهد. تریگرهای DDL (Data Definition Language) در SQL Server نوع خاصی از رویه‌های ذخیره‌شده Transact-SQL هستند که در پاسخ به رویدادهای محدوده سرور یا پایگاه داده اجرا می‌شوند. تریگرهای DDL برای این موارد مفید هستند: ثبت تغییرات در اسکیمای پایگاه داده،جلوگیری از تغییرات خاص و پاسخ به تغییرات. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.