در این مقاله می خواهیم در مورد 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
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }
توضیح پارامتر ها :
trigger_name :
این پارامتر حاوی نام اجرا کننده است . توجه کنید که اجرا کننده ای با این نام نباید وجود داشته باشد .
ON {ALL SERVER | DATABASE}
توسط این پارامتر میتوانیم مشخص کنیم که اجرا کننده ما در کدام سطح اجرا شود . آیا برای تمامی سرور های راه اندازی شده بروی این سیستم و یا بروی بانک اطلاعاتی جاری ؟
WITH
[ 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 . بهتر است این اجرا کننده را در پایان کار پروژه خود تنظیم کنید تا تمامی دسترسی ها غیر فعال کنید.
پیوند ها