تريگرها درSQL Server در ويژوال بيسيك6 :
تريگر نوع خاصي از روال ذخيره شده است و در هنگام تغيير داده اي كه براي حفاظت از آن طراحي شده است فعال مي گردد. تريگرها با جلوگيري از تغييرات غير معتبر يا ناسازگار در داده ها به حفظ يكپارچگي داده اي كمك مي كنند. فرض كنيد دو جدول customer و orders در اختيار داريد بنابراين مي توانيد تريگري ايجاد كنيد كه با ايجاد هر سفارش جديد، اعتبار شماره مشتري آن را كنترل كند. همچنين مي توانيد تريگري ديگري ايجاد كنيد كه در صورت حذف يك مشتري جدول orders را كنترل كرده و در صورت وجود سفارش براي آن مشتري فرآيند حذف مشتري را متوقف سازد.
تريگرها مي توانند قواعد كاري پيچيده تري را نسبت به قيود اعمال كنند. براي مثال يك تريگر insert مي تواند در هنگام درج ركورد جديد به سفارشات فعال شود و وضعيت پرداخت مشتري را بررسي و نوع پرداخت مناسب وي را تعيين كند.
تريگرها پارامتر ندارند و صريحا فعال نمي شوند و براي فعال سازي آنها تغييرات داده اي لازم است.
تريگرها را مي توان تا 32 سطح تودرتو تعريف كرد. اين تريگرها به شكل زير عمل مي كنند:
براي مثال تريگر جدول orders يك ورودي به جدول حسابهاي دريافتي اضافه مي كند و اين ورودي به نوبه خود تريگر ديگري را براي بررسي وضعيت مشتري فعال مي نمايد. يك تريگر به روز رساني جدول را انجام مي ده و همين امر تريگر ديگري را فعال مي كند.
بطور پيش فرض تمامي تريگرها (UPDATE,DELETE,INSERT)پس از تغييرات داده اي فعال مي شوند و به تريگر AFTER معروف مي باشند. در ويرايش هاي قبلي SQL Server تنها اين نوع تريگر موجود بوده است. در SQL Server 2000 نوع ديگري به نام INSTEAD OF معرفي شده است كه بجاي تغييرات داده اي موردنظر فعال مي شود.
از نقطه نظر كارائي، تريگرها سربار زيادي ندارند. بيشتر زمان اجراي يك تريگر براي ارجاع به ساير جدول بكار مي رود. اين ارجاع در صورت وجود جداول در حافظه سريع و در صورت نيار به خواندن از ديسك كمي كندتر خواهد بود.
تريگرها بخشي از يك تراكنش (transaction) محسوب مي شوند. اگر تريگر يا هر بخشي از تراكنش با شكست مواجه شود كل تراكنش عقبگرد مي شود.
درك جدول Deleted و Inserted
تريگرها از دو جدول inserted و deleted استفاده مي كنند. اين جداول ساختار مشابهي با جدول پايه يا جدول trigger كه تريگر در آن ايجاد مي شود دارند. اين دو جدول در حافظه RAMقرار مي گيرند و جداول منطقي هستند . اگر ركورد جديدي را به جدول پايه اضافه كنيد اين ركورد هم در جدول پايه و هم در جدول inserted ثبت مي شود. وجود مقادير در دسترس در جدول insertedبه شما امكان مي دهد كه نيازي به ايجاد متغير براي نگهداري اطلاعات نداشته باشيد و به اطلاعات دلخواه دسترسي بيابيد. هنگام حذف يك ركورد، ركورد حذف شده در جدول deleted قرار مي گيرد. به روز رساني نيز يك حذف و درج متوالي است. اگر ركوردي را به روز رساني كنيد، ركورد اصلي در جدول deleted و ركورد تغيير يافته در جدول پايه و جدول inserted ذخيره مي شود.
نكته : شما نمي توايند محتواي جداول inserted و Deleted را ببينيد.
اما براي اينكار مي توانيد با استفاده از تريگري محتواي اين جداول را ببينيد.كه در قسمتهاي بعدي شرح داده ميشود.
ايجاد تريگرها با دستور CREATE TRIGGER
نگارش ايجاد تريگرها به شرح زير است :
CREATE TRIGGER [owner.]trigger_name
ON [owner.]table_name | view_name
{FOR..AFTER | INSTEAD OF] {INSERT | UPDATE | DELETE}
[WITH ENCRYPTION]
AS sql.statements
يك جدول مي تواند هر تعداد تريگر از انواع DELETE, UPDATE ,INNSERT داشته باشد. هر عمل را مي توان در يك يا چند تريگر ذخيره كرد. اگر عملي در چند تريگر ذخيره شود بايد نام تريگرها منحصر بفرد و يكتا باشد. براي مثال مي توانيد تريگري به نام trInsupAuthors را بر روي جدول authors براي عمل حذف بر روي همين جدول ايجاد كنيد. براي تغيير تريگر كافيست از محيط enterprise روي جدول موردنظرراست كليك كرده گرينه ي Manage Trigger را بزنيد و از ليست كشويي تريگر موردنظر خود را انتخاب كرده و آنرا ويرايش نماييد. و از محيط كوئري آناليز هم توسط دستور ALTER TRIGGER تريگر را ويرايش كنيد يا ابتدا آنرا حذف نموده و دوباره ايجاد كنيد.
توجه : تغيير تريگر با دستور ALTER TRIGGER، سبب جايگزيني كامل تريگر قديم با تريگر جديد مي شود. با حذف يك جدول، تريگرها موجود آن نيز به شكل خودكار حذف مي شوند.
قوانين حاكم بر تريگرها:
· تريگر را نميتوان براي جداول موقت ايجاد كرد اما ميتواند به ديدها و يا جداول موقت ارجاع داشته باشد.
· تريگر مجموعه جواب (resultest) باز نميگرداند بنابراين هنگام استفاده از دستورات select احتياط كنيد. بكارگيري عبارت if exists به عنوان بخشي از دستور select در كد تريگر متداول است.
· تريگر براي حفظ يكپارچگي دادهاي، ارجاعي و كپسوله كردن قوانين كاري بكار ميرود.
· بكارگيري گزينه with encryption سبب ميشود تريگرها در جدول syscomments به شكل رمز شده نگهداري شوند.
· دستورات writetext تريگرها را فعال نميكنند. اين دستورات براي تغييرات دادهاي متني (text) و تصوير (image) بكار ميرود و در فايل گزارش تراكنشها ثبت نميشود.
· دستورات SQL زير را نميتوان در تريگرها بكار برد: همه انواع دستورات CREATE، همه دستورات DROP و دستورات ALTER TABLE, ALTER DATABASE ,TRUNCATE TABLE, GRANT, TRANSACTION, LOAD DATABASE, ROCONFIGURE REVOKE, STATISTICS, UPDATE STATISTICS , SELECT INFO, و كليه دستورات DISK
· دستورات عقبگرد شده تراكنشها در تريگرها مي تواند نتايج غير منتظرهاي را در برنامه ها ايجاد كند.
تريگرهاي Insert و UpdateوDelete
حال به يك نمونه تريگر براي دستورات INSERT و UPDATE جدول توجه كنيد:
CREATE TRIGGER trAddAuther
ON authors
FOR INSERT, UPDATE
AS raiserror (“%d rows have been modified”, 0,1, @@rowcount)
RETURN
CREATE TRIGGER trDelAuthors
ON authors
FOR DELETE AS
raiserror(“%d rows are going to be deleted from this table!”,0 , 1 , @@rowcount)
توجه كنيد كه پيام را در صورتي مشاهده مي كنيد كه عمل حذف را در محيط كوئري آناليز انجام دهيد.
تريگري براي ديدن محتواي جداولinserted و Deleted
CREATE TRIGGER tr24 ON Teacher
FOR DELETE ,update,insert
AS
select * into del from deleted
select * into insl from inserted
نام تريگر بالا tr24 است. كه بر روي جدول teacher ساخته شده است. خط دوم يعني وقتي عمليات اضافه يا حذف يا ويرايش بر روي جدول teacher اتفاق افتاد اين تريگر فراخواني شود..خط چهارم ركوردهاي موجود در جدول Deleted را در جدول del مي ريزد و خط پنج هم محتواي جدولinserted را در جدولins مي ريزد.
توجه كنيد كه جدولهايinsو del نبايد وجود داشته باشند وگرنه پيام خطا ميدهد.
اعمال يكپارچگي دادهاي با تريگرها
تريگرها براي اعمال يكپارچگي دادهاي در پايگاه داده بكار ميروند. در گذشته يكپارچگي ارجاعي صرفا از طريق تريگرها تامين ميشد. در ويرايشهاي اخير SQL Server اينكار را ميتوان با قيد يكپارچگي ارجاعي انجام داد اما تريگرها كماكان براي كپسوله كردن قواعد كاري و تغييرات پلهاي پايگاه داده مفيدند. يك تغيير پلهاي ميتواند با تريگر ايجاد شود. فرض كنيد يك فروشگاه ديگر فعاليت نميكند. شما ميتوانيد تريگري ايجاد كنيد كه ابتدا اين فروشگاه را از جدول stores حذف كند و سپس كليه فروشهاي مربوط به آن را از جدول sales حذف نمايد
اعمال يكپارچگي ارجاعي
تريگرها براي اعمال يكپارچگي ارجاعي نيز بكار ميروند. اين كار هدف اصلي آنها در پايگاه داده است. بخصوص براي حذفها و به روز رساني پلهاي مفيدند. تريگرها در پايان تغييرات دادهاي كنترل ميشوند در حاليكه قيود در ابتدا كنترل ميشوند. اگر قيدي نقض شود. هيچ تريگري فعال نميشود. تريگر زير يكپارچگي دادهاي را اعمال ميكند. از طريق اين تريگر مطمئن ميشويم كه در صورت درج يك ركورد جديد در جدول Sales حتما كد فروشگاه معتبر آن در جدول stores وجود دارد.
CRETAE TRIGGER trInsUpdSales
On tblSales
FOR INSERT, UPDATE AS
IF(SELECT COINT(*) FROM tblStores , inserted
WHERE tblStores.stor_id = inserted.stor_id) =0
BEGIN
PRINT ‘The stor_id you have entered does not exist’
PRINT ‘in the stores table’
ROLLBACK TRANSACTION
END
اين تريگر براي هر دستور مجزاي INSERT يا UPDATE بر روي جدول tblSales بخوبي كار ميكند. اما اگر دستور SELECT INTO را اجرا كنيد، تريگر ممكن است كاملا درست فعال نشود. وقتي با چندين سطر سروكار داريد بايد مطمئن شويد تعداد سطرهاي درج شده stor_id با تعداد فروشهايي كه اضافه كردهايد برابر باشد. حال تريگر را به شكل زير تغيير ميدهيم:
DROP TRIGGER trInsUpdSales
GO
CRETAE TRIGGER trInsUpdSales
On tblSales
FOR INSERT, UPDATE AS
DECLARE @rc int
SELECT @rc = @@rowcount
IF(SELECT COINT(*) FROM tblStores , inserted
WHERE tblStores.stor_id = inserted.stor_id) =0
BEGIN
PRINT ‘The stor_id you have entered does not exist’
PRINT ‘in the stores table’
ROLLBACK TRANSACTION
END
IF(SELECT COINT(*) FROM tblStores , inserted
WHERE tblStores.stor_id = inserted.stor_id) <>@rc
BEGIN
PRINT ‘Not all sales have a valid stor_id’
PRINT ‘in the stores table’
ROLLBACK t TRANSACTION
END
بكار گيري تريگرهاي INSTEAD OF
هنگام استفاده از تريگرهاي INSTEAD OF كد اصلي ايجاد تغييرات در جدول اجرا نمي شود بلكه كد تريگر اجرا ميشود. براي مثال شما ميتوانيد تريگري را براي جدول authors ايجاد كنيد كه به كاربران اطلاع دهد مولفين را نميتوان حذف كرد. اين كار با تريگر معمول FOR،AFTER نيز قابل انجام است. اما مستلزم آن است كه تغييرات واقعي در ابتدا اعمال شوند سپس در كد تريگر عقبگرد شوند. اگر كد تريگر INSTEAD OF را بكار بريد، عمل به روز رساني انجام نميشود و طبيعي است اين تريگر كاراتر است. به مثال زير توجه كنيد.
USE pubs
GO
CREATE TRIGGER trlO_DelAuthors
ON authors INSTEAD OF DELETE AS
PRINT ‘you can not delete authors from the authors table!’
براي تست تريگر سعي كند مولفي با نام خانوادگي white را حذف كنيد:
USE pubes
GO
DELETE authors WHERE au_lname = ‘white’
جمع آوري اطلاعات تريگرها
با اجراي روال ذخيره شده sp_helptext ميتوانيد متن دستور تريگر را مشاهده كنيد. البته تريگرهاي رمز شده ركوردي در جدول Syscomments ندارند. در حالت كلي نبايد هيچ شيئي را رمزنگاري كنيد. مگر آنكه واقعا مجبور باشيد. هنگام ارتقا پايگاه داده كليه اشياء رمزنگاري شده بايد حذف و مجددا ايجاد شوند. اما اشياء رمزنشده بطور خودكار به ويرايش جديد ارتقاء داده ميشوند.