آموزش SQL Server DDL Triggers

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

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

SQL Server DDL triggerها به رویدادهای سرور یا 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

نامtriggerتعریف شده توسط کاربر را بعد از کلمات کلیدی 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 می تواند در یک یا چند رویداد یا گروه رویداد مشترک شود.

مثالی از پیاده سازی 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-OJBNID1\VAIO</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>

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

رفرنس مقاله

https://www.sqlservertutorial.net/sql-server-triggers/sql-server-ddl-trigger