مفهوم تریگر ها :

فرض کنید بخواهید مقدار فیلد شماره درس را در جدول Course تغییر دهید یا حذف کنید . ممکن است این مقدار در جدول Grade وجود داشته باشد . در این صورت ، اگر مقدار شماره درس در جدول course تغییر کند یا حذف شود ، اطلاعات جدول Grade که این شماره درس را دارند ، به رکوردهای نا معتبر تبدیل خواهند شد . به عنوان مثال دیگر ، فرض کنید شماره درسی را در جدول grade وارد کنید ، ولی این شماره درس در جدول Course موجود نیست . برای جلوگیری از این این مشکلات می توانید از تریگر ها استفاده کنید .ترگر ها ، نوع خاصی از رویه های ذخیره شده  اند که در هنگام تغییر داده های جدول برای محافظت از طراحی بانک اطلاعاتی فعال می شوند و از ورود داده های نا معتبر جلوگیری می کنند.

دو تفاوت بین ترگر ها و رویه های ذخیره شده :

۱-      تریگر ها فاقد پارامتر  هستند . ولی رویه های ذخیره شده می توانند پارامتر داشته باشد .

۲-      تریگر ها در هنگام تغییر داده های جدول فعال می شوند ، ولی رویه های ذخیره های ذخیره شده حتما باید فراخوانی شوند تا اجرا گردند.

 هر جدول می تواند حداکثر ۳ تریگر داشته باشد که عبارتند از :

o  تریگر در هنگام اضافه کردن رکورد (Insert )

o  تریگر در هنگام به روز رسانی رکورد ( Update )

o  تریگر در هنگام حذف رکورد ( Delete )

 به طور پیش فرض ، کلیه تریگر های جدول پس از تغییرات جدول فعال می شوند . این تریگر ها AFTER نام دارند . تریگر های دیگری نیز وجود دارند که INSTEAD OF نامیده می شوند و به جای تغییر در داده های مورد نظر ، باعث فعال سازی خودشان می شوند . در SQL SERVER تریگر ها از دو جدول Inserted و Deleted   استفاده می کنند . این جداول مضابه ساختار جدول پایه ای است که تریگر ، برای آن جدول ایجاد شده است. اگر کوردی به جدول اضافه کنید ، این رکورد علاوه بر این که در جدول پایه اضافه می شوند ، در جدول Inserted نیز اصافه خواهند شد . اگر رکوردی را در جدول پایه تغییر دهید ، این رکورد از جدول پایه حذف به جدول Deleted اضافه خواهد شد . رکورد جدید به جدول پایه و جدول Inserted اضافه می شود.

 نکته :  تریگر ها ، توابع بسیار خوبی برای استفاده هستند و جامعیت بانک اطلاعاتی را بررسی می کنند ، اما موجب افزایش I/O می گردند.

ایجاد تریگر ها در SQL Server

-          CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]
    }
}

 

trigger_name :

نام تریگر را مشخص می کنند

 ON { table | view }:

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

 { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }

{ FOR | AFTER | INSTEAD OF } :

 مشخص می کند که تریگر در چه زمانی اجر شود قبل از عمل ، بعد از عمل یا به جای عمل ..

 { [ INSERT ] [ , ] [ UPDATE ] } :

مشخص می کند که برای کدام عمل اصلی اعمال شود که  می تواند مقادیر (INSERT , UPDATE , DELETE)  باشد  اما قسمت اول

 sql_statement [ ...n ] :

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

مثال :

ایجاد تریگری به نام tr_delete که در هنگام حذف رکوردی از جدول type فعال شود.

 Create Trigger tr_delete  

ON type

FOR DELETE AS

      Raiserror('%d record delete ' ,0,1,@@rowcount)

ایجاد تریگری برای به نام tr_delete1 که  به جای عملیات delete جدول type فعال می شود.

 Create Trigger tr_delete1

On type

INSTEAD OF delete AS

      Delete from temp where id=@@identity

ایجاد تریگری به نام tr_InUp که در هنگام اضافه کردن و یا ویرایش رکورد فعال می شود.

 Create Trigger tr_InUp

ON type

FOR INSERT , UPDATE

AS

Raiserror('%d redord inserted or updateted',0,1,@@rowcount)

 تغییر تریگر در بانک اطلاعاتی SQL Server

برای این منظور از دستور ALTER TRIGGER استفاده کنید.

ALTER TRIGGER trigger_name
ON ( table | view )
[ WITH ENCRYPTION ]
{
    { ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
        [ NOT FOR REPLICATION ]
        AS
        sql_statement [ ...n ]
    }
یا  
    { ( FOR | AFTER | INSTEAD OF ) { [ INSERT ] [ , ] [ UPDATE ] }
        [ NOT FOR REPLICATION ]
        AS
        { IF UPDATE ( column )
        [ { AND | OR } UPDATE ( column ) ]
        [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
        { comparison_operator } column_bitmask [ ...n ]
        }
        sql_statement [ ...n ]
    }
}

 trigger_name :

نام تریگر را مشخص می کنند.

 ON { table | view }:

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

    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }

{ FOR | AFTER | INSTEAD OF } :

مشخص می کند که تریگر در چه زمانی اجر شود قبل از عمل ، بعد از عمل یا به جای عمل ..

{ [ INSERT ] [ , ] [ UPDATE ] } :

 مشخص می کند که برای کدام عمل اصلی اعمال شود که  می تواند مقادیر (INSERT , UPDATE , DELETE)  باشد  اما قسمت اول

sql_statement [ ...n ] :

مثال :      دستوراتی که عملکرد تریگر tr_delete را تغییر میدهد به طوری که رکوردی را از جدول student حذف کرده و پیغامی به کاربر نمایش می دهد.

Alter Trigger tr_delete

On Student

FOR DELETE

AS

PRINT ('record deleted')

 حذف تریگر :

 برای این منظور از دستور DROP TRIGGER استفاده کنید.

 DROP TRIGGER { trigger}[,…n]

 Trigger…n  : تریگر هایی هستند که باید حذف شوند

مثال :     دستوراتی که تریگر tr_delete  tr_InUp, را حذف می کند.

 DROP TRIGGER tr_delete , tr_InUp