در این مقاله می خواهیم در مورد DLLTrigger ها صحبت کنیم . توصیه میشود که ابتدا مقاله Trigger IN SQL را  مطالعه کنید و بعد به این بحث بپردازید.

DDLTrigger  ها چی هستند ؟ این نوع اجرا کننده ( Trigger ) ها برای اجرا یک سری دستورات که هر دستور sql می باشد استفاده می شوند . پس فرق آن با اجرا کننده های عادی چیست ؟

اگر یادتان باشد اجرا کننده های عادی در سطح بانک اطلاعاتی بودند و قبل ، بعد یا به جای دستورات اضافه ، ویرایش ، حذف اجرا میشدند . اما اجرا کننده های که ما درمورد ان بحث میکنیم در سطح سرور و یا بانک اطلاعاتی اجرا میشوند و قبل یا بعد از اجرای دستورات تعریف داده ها (definition data language ) اجرا میشوند.

یک مثال ساده از این نوع اجرا کننده ها :

CREATE TRIGGER Createtbl

ON DATABASE

FOR CREATE_TABLE

AS PRINT ‘created one TABLE’

با اجرای این دستور در sql2005 یک اجرا کننده ایجاد میشود که در دیتابیسی که ان را اجرا کرده اید ، با ایجاد هر جدول یک پیغام را چاپ میکند.

 شکل کلی این دستور :

CREATE TRIGGER trigger_name

ON { ALL SERVER | DATABASE }

[ WITH <ddl_trigger_option> [ ,...n ] ]

{ FOR | AFTER } { event_type | event_group } [ ,...n ]

AS { sql_statement  [ ; ] [ ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

توضیح پارامتر ها :

trigger_name :

این پارامتر حاوی نام اجرا کننده است . توجه کنید که اجرا کننده ای با این نام نباید وجود داشته باشد .

ON {ALL SERVER | DATABASE}

توسط این پارامتر میتوانیم مشخص کنیم که اجرا کننده ما در کدام سطح اجرا شود . آیا برای تمامی سرور های راه اندازی شده بروی این سیستم و یا بروی بانک اطلاعاتی جاری ؟

 WITH <ddl_trigger_option> ::=

    [ ENCRYPTION ]

    [ EXECUTE AS Clause ]

 توسط این پارامتر میتوانیم تنظیمات اجرا کننده را تعیین کنیم . این تنظیمات شامل این است که آیا این اجرا کننده به صورت کدشده ذخیره شود ؟ و توسط چه کسانی می توانند اجرا شود ؟

 ENCRYPTION

با ذکر این پارامتر اجرا کننده کد میشود و دسترسی به ان برای دیگران مفهومی ندارد . زیرا اگر ان را ببینند چیزی از ان متوجه نمی شوند.

 EXECUTE AS {CALLER | SELF | USER_NAME}

 یا ذکر این پارامتر می توانیم مشخص کنیم چه کسانی آن را اجرا کنند . مقدار پیش فرض برای آن CALLER  (گروهی که مجوز لازم را دارند ) می باشد . SELF همین کاربر جاری است . USERNAME نام هر کاربر می باشد .

{ FOR | AFTER } { event_type | event_group } [ ,...n ]

 در اینجا مشخص میکنیم که می خواهیم اجرا کننده برای چه اقداماتی مجموعه دستورمان را اجرا کند .

EVENT_TYPE , EVENT_GROUP:

در اینجا می توانید نام یک یا بیشتر از  گروه رویداد ها را و یا یک یا بیشتر از رویدادها را مشخص کنید . 

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

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

در بالا رویداد های مربوط به بانک های اطلاعتی را ملاحضه می کنید . در صورت استفاده از آنها ، به محض اجرای هر کدام که انتخاب کرده باشید در دیتابیسی که اجرا کننده را ایجاد کرده اید ، مجموعه دستورات شما اجرا میشود .

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

select * from sys.triggers

نکته : با اجرای دستور فوق تمامی اجرا کننده های ایجاد شده در این بانک را برمیگرداند . حال اگر می خواهید انها را به اجراکننده های DDL محدود کنید باید روی فیلد PARENT_CLASS شرط =0 را اعمال کنید.

لیست رویداد ها ی که برای بانک خود در اجرا کننده ها استفاده شده اند را می توانید با استفاده از دستورات زیر بدست بیاورید.

select * from DBNAME.sys.trigger_events

توسط دستور فوق می توانید اجرا کننده های ایجاد شده در سطح سرور را مشاهده کنید .

select * from sys.server_triggers

توسط دستور فوق می توانید رویداد های استفاده شده در سرور را مشاهده کنید.

نکته : اگر کمی به روابط بین جداول دقت کنید می توانید رویداد های مربوط به هر اجرا کننده را مشاهده کنید.

 AS { sql_statement  [ ; ] [ ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

در قسمت sql_statement دستوراتی که می خواهید اجرا شوند را وارد کنید . در اینجا میتوانید هر دستور sql را اجرا کنید.

EXTERNAL NAME < method specifier >  [ ; ]

برای فراخوانی توابع clr خارجی از این گزینه استفاده می کنیم . بهتر است ابتدا clr و مشتقات آن را فرا بگیرید و بعد از آن استفاده کیند.

چند مثال :

CREATE TRIGGER TRIGGER_DROPTABLE

ON DATABASE

FOR DROP_TABLE

AS

   PRINT 'You must disable Trigger "TRIGGER_DROPTABLE" to drop table!'

   ROLLBACK

GO

اجرا کننده ای را ایجاد می کند که هنگام حذف جداول اجرا میشود و این اجرا کننده یک پیغام را چاپ می کند و دستور حذف را بی اثر مینماید.

CREATE TRIGGER ddl_trig_login

ON ALL SERVER

FOR DDL_LOGIN_EVENTS

AS

    PRINT 'Login Event Issued.'

    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

GO

این اجرا کننده هنگام هر تغییر ای روی کاربران اجرا میشود . یک سری اطلاعات در مورد رویداد را نشان می دهد .

CREATE TRIGGER log

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @data XML

SET @data = EVENTDATA()

INSERT ddl_log

   (PostTime, DB_User, Event, TSQL)

   VALUES

   (GETDATE(),

   CONVERT(nvarchar(100), CURRENT_USER),

   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

GO

این اجرا کننده به محض اجرای هر دستور ddl در سطح بانک اطلاعاتی اجرا میشود و اطلاعات مربوط به رویداد را در جدول ddl_log ذخیره میکند .

اگر بدنه این اجرا کننده را به rollback تغییر دهیم دیگر هیچ تغییری را نمی توان در بانک انجام داد . توجه داشته باشید که برای کاربران caller . بهتر است این اجرا کننده را در پایان کار پروژه خود تنظیم کنید تا تمامی دسترسی ها غیر فعال کنید.