مفهوم تریگر ها :
مفهوم تریگر ها :
فرض کنید بخواهید مقدار فیلد شماره درس را در جدول 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
Trigger :
نوعی روال ذخیره شده است که در مواقع خاص اجرا می شود مثلا در زمان درج داده جدید یا ویرایش داده ها اجرا می شود . توجه کنید که trigger هیچ گاه فراخوانی نمی شود و بلکه اتوماتیک اجرا میشود(البته در زمانی که مشخص می کنید) توسط دستور زیر ایجاد میشود.
نام تریگر create trigger
نام جدولی که تریگر روی ان عمل می کند on
for insert/update/delete
as
دستوراتی بعد از عمل مشخص شده انجام میشود
نکته: به جای کلمه for می توان از کلمه after , inetred af نیز استفاده کردو کلمه after پیش فرض است.
after: بعد از انجام موفقیت آمیز عملیات (insert/update/delete)
for: در همان لحظه اجرا می شود.
ineterd of: ترگیر را به یک قطعه کد T.SQl عادی تبدیل می کند و مانند یک پروسیجر معمولی تریگر را می سازد.
ایجاد یک تریگر به صورت ویزاردی:
راست کلیک روی جدول مورد نظر/گزینهall taks/mange trigger و نوشتن کد های مورد نظر
مثال:
create trigger std_trigger
on student
for insert/update
as
if (select max(grade) from student) >20
‘print ‘a range is not valid
نتیجه : در لحظه ورود یا تغییر داده ها اگر بزرگترین نمره در جدول از ۲۰ بیشتر بود پیغام مناسب را نمایش می دهد.
مثال ۲: تریگری بنویسید که بعد از درج داده جدید در جدول sudent اگر داده ها بیشتر از ۱۰ رکورد شد یک پیغام خطا چاپ کند و رکورد های درج شده را حذف کند.
create trigger isert_trigger
on student
for insert
as
if (select count(*) from student) >10
begin
‘print ‘you cannot insert
Rollback
end
دستور raiserror در تریگرها
توسط این دستور یک پیغام خطا تولید میشود که در جدول sys_message در بانک اطلاعاتی master (جدول پیغام های سیستمی ) ذخیره می شود.
روش استفاده :
(حالت ،شدت،”متن خطا “)raiserror
متن خطا:یا متن یا شماره پیغامی از پیغام های سیستمی که در جدول پیغام های سیستمی ذخیره شده
شدت:عددی بین ۱ تا ۲۵ برای پیام هایی که خودمان پیاده می کنیم می توانیم یک عدد بین ۱ تا ۱۸ بدهیم
حالت: عددی است بین ۱ تا ۱۲۷ که اطلاعاتی راجع به حالت خطا را نگه می دارد
تابع()update در تریگر
در ازای عملیات های insert و update یک مقدار بولین برمی گرداند که اگر true باشد یعنی آن ستون تغییر کرده است.
روش استفاده:
… (نام ستون یا فیلد)if update
نکته :این تابع فقط در بدنه تریگر ها استفاده می شود.
مثال: تریگری بنویسید که اجازه ندهد فیلد نام تغییر کند.
create trigger name_std
on student
after upfate , insert
as
(if update (std_name
begin
(raiserror(”you can not change name”,10,2
rollback
end
مفهوم جداول deleted ,inserted
دستورات DML که باعث اجرای تریگر می شودبا ۲ جدول موقت inserted و deleted کار می کند که این دو جدول به صورت اتوماتیک ایجاد و مقدار دهی می شوند ما می توانیم از این جداول برای تست کردن عملیات های DML قبل از اعمال روی جداول استفاده کنیم.
در جدول deleted یک نسخه از تمام رکورد های حذف شده در اثر دستورات delete یا update را نگه می دارد و جدول inserted یک نسخه از تمام رکورد های جدید اضافه شده بر اثر عملیات های insert , update را نگه می دارد دقت کنید که عمل update هردو جدول را مقدار دهی می کند.
ما از طریق نوشتن دستورات join جداول موقت را با جداول اصلی می توانیم مقادیری را که قبلا حذف شده بود یا تازه درج شده را ببینم:
مثال:تریگری بنویسید که بعد از عملیات درج رکورد های درج شده جدید جدول insert را نمایش دهد.
create trigger inset_trigger
on student
for insert
as
select student.std_num ,inserted.std_num
from inserted,student
where student.std_num=inserted.std_num
تابع drop برای حذف تریگر
نام تریگر drop trigger
تابع alter برای تغییر تریگر
نام تریگری که میخواهیم تغییر بدهیم alter trigger
نام جدول on
….. for
…..as
یکی از موارد on,forیا as تغییر می کند.
بررسی تریگرها
تعریف :
تریگر مکانیسمی است برای پیاده سازی قاعده فعال و بر اساس مدل ECA (Event-Condition-Action) در سیستم های رابطه ای ارائه شده است . تریگر یک رویه ذخیره شده است که در واکنش به یک تغییر مشخص در پایگاه داده ها توسط سیستم فراخوانده می شود . بنابراین راه انداز مکانیسمی است برای کنترل جامعیت پایگاه داده ها از طریق انجام خودکار عملیات منتشر شونده .
مزایای تریگرها :
• اجرای آن تحت کنترل متمرکز است ( سیستم یا مدیر پایگاه داده )
• نیازی به اعمال محدودیت ها در برنامه کاربردی نیست .
• در معماری پایگاهی مشتری / خدمتگزار و بطور کلی در معماری پایگاه داده های توزیع شده بسیار سودمند است .
کاربردهای تریگر :
1- اعمال محدودیت های جامعیتی .
2- اعمال قواعد ایمنی مبتنی بر مقادیر .
3- درج رکوردها در فایل ثبت .
4- بهنگام سازی منتشر شونده ( بویژه در پایگاه داده های توزیع شده وقتی چند نسخه از داده داشته باشیم ) .
5- ساختن ( و ذخیره کردن ) دید کاربر .
6- اعمال قواعد و فعالیت های محیط کاری .
7- اعمال قواعد مدیریت جریان کاری .
- کاربردهای 1 تا 5 را اصطلاحا کاربردهای داخلی و کاربردهای 6 و 7 را کاربردهای خارجی گویند .
تعریف تریگر در SQL3
کد:
CREATE TRIGGER name
{BEFOR | AFTER | UPDATE OF column}
ON TABLE name [ORDER value]
[REFERENCING {NEW | OLD | NEW-TABLE | OLD-TABLE } AS name]
{WHEN condition(n)}
(SQL3 procedure
[FOR EACH {ROW | STATEMENT}])
مشکلات تریگر :
- وجود تریگرهای بازگشتی ، یعنی نوعی تریگر که اجرای آن منجر به فعال کردن خود تریگر می شود .
- ترتیب غیرقابل پیش بینی برای اجرای تریگرها وقتی که مجموعه ای از تریگرها باید اجرا شوند .
- وجود زنجیره تریگرها ، یعنی اجرای یک تریگر سبب اجرای تریگر دیگری می شود .
در عمل طراحان سیستم های کاربردی بزرگ چندان تمایلی به استفاده از تکنیک تریگرها ندارند ، زیرا این تکنیک بویژه در سیستم های کاربردی بزرگ ، مشکلاتی را ایجاد میکند .
برخی از این مشکلات عبارتند از :
- عدم امکان بیان ECA ها (Event-Condition-Action) بطور مستقیم در برخی موارد ، که خود ناشی از محدودیت زبان هایی است که برای نوشتن تریگر در سیستم های پایگاه داده ها وجود دارد.
- بروز مشکلات ایمنی پایگاه داده ها و کاهش قابلیت اطمینان سیستم کاربردی .
- تاثیر منفی در کارایی سیستم کاربردی ، بویژه جدایی بین تراکنش ها و تریگرها ، بهینه سازی سراسری برنامه کاربردی را با مشکل مواجه می کند .
- کاستی های تریگرها در سیستم های رابطه ای :
- امکان تعریف داده های پیچیده وجود ندارد .
- دستیابی مستقیم به زبان های برنامه نویسی و یا به سیستم عامل زیرین امکان پذیر نیست .
- تنها مقادیر اتومیک را می توان به عنوان پارامتر به رویه ذخیره شده داد .
- یک تریگر را نمی توان روی بیش از یک رابطه (جدول) اعمال کرد .
- اگر روی یک رابطه ، تریگر جدیدی با همان عملیات که در تریگر قبلی وجود دارد ، تعریف شود، تریگر قبلی جایگزین می شود و اخطاری هم داده نمی شود .
- رویداد نمی تواند نامدار باشد و دوباره به کار برده شود .
- امکان تعریف رویداد مرکب وجود ندارد .
مهندسی نرم افزار کامپیوتر