پنج شنبه ۴ خرداد ۱۳۹۶
 
 
 
کلمه عبور خود را فراموش کرده اید؟
 

 
 
 آشنایی با DDL Trigger
Database / LINQ / EF
تاریخ ثبت:  ۸۸/۱۱/۲۰
تعداد نمایش:  ۵۹۹۲
  نویسنده: مسعود تکلو
 
   ۲  نفر تا این لحظه به این مقاله امتیاز داده اند.
 
   Bookmark and Share

در این مقاله می خواهیم در مورد 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 . بهتر است این اجرا کننده را در پایان کار پروژه خود تنظیم کنید تا تمامی دسترسی ها غیر فعال کنید.

 

 

  کیفیت مقاله ارائه شده از نظر شما   
برای دادن رتبه به این مقاله می بایست Login کرده باشید.
  درباره نویسنده
مسعود تکلو
عضو گروه برنامه نویسی مشهد , و عضویت در شرکت متخصصان فناوری شرق با ریاست آقای محمد ژاله پور
همه مقاله های نوشته شده توسط این کاربر (۶)
 
  پیام جدید
صفحه ۱ - پیامهای اصلی ۱ تا ۱ از مجموع ۱ پیام اصلی
اولین قبلی بعدی

 عنوان فرستنده تاریخ
 
question زهرا مولائی ۱۳۹۴/۱۰/۱۳
اولین قبلی بعدی

Copyright © 2006 - 2016 All Rights Reserved.
Please direct your questions or comments to