تریگر و تراکنش در sql server
تراکنش هاوتريگرها درSQL پيش آگاهي مقدمه :يکي ازاصول تامين جامعيت داده اي ، امکان اعمال به روز رساني منتشرشونده وتعريف واحدهاي منطقي کاردرراستاي اجراي کامل ياعدم اجراي کليه مواردازيک مجموعه کارمي باشد . براي پياده سازي اين اصل SQL-Server تريگر(رهانا)وتراکنش رادراختياربرنامه نويس قرارداده است . احضارتريگرالزاما به صورت ضمني بوده وازطريق اجراي يکي ازدستورات insert ،delete ياupdate انجام مي شود . برنامه نويس رهانارابراي اجراي دستوراتي که بايدبه صورت خودکاربراي حفظ جامعيت داده ها انجام شود ، تعريف مي کند . بنابراين تريگررامي توان تراکنشي دانست که باتغييرات داده فعال مي شود وامکان اجراي يک واحد منطقي کاررا درجريان تغييرات داده اي فراهم مي کند .
تراکنش (Transaction)
تراکنش مجموعه اي از دستورات SQL است که معمولا تغييري در پايگاه داده ايجاد مي کند به گونه اي که جامعيت و يکپارچگي داده ها همچنان برقرار باشد.اين مجموعه دستورات يا به طورکامل اجراء مي گردند ويا درصورت وجود اشکال دربين دستورات اجراي تمامي آن ها لغومي گردد .
درواقع تراکنش ،يک واحد منطقي کار(Logical unit work) است که با استفاده از آن مي توان از پايان درست وکامل کار اطمينان پيدا کرد.
SQL براي اجراي تراکنش ها از فايل ثبت تراکنش ها (Log file) استفاده مي کند که کليه تغييرات درآن ذخيره مي شود. براي افزايش سرعت وهمچنين براي اطمينان ازاجراي درست وکامل تراکنش ،معمولا تغييرات موردنظرعلاوه برآن که درفايل هاي اصلي اعمال مي شود ، درفايل log نيزنوشته شده والبته رکوردهاي تغييرداده شده درحالت lock قرارمي گيرند . درانتها درصورتي که قرارباشد تغييرات برگشت داده شود، ازفايل log استفاده شده وتغييرات برگشت داده شده ودرنهايت به هرحال رکوردهاي lock شده آزادخواهند شد .
دستورات کنترل تراکنش
Begin Transaction : نقطه شروع يک تراکنش است که ساختارکلي آن به شکل زيراست :
BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable
[ WITH MARK [ 'description' ] ] ]
transaction_name : نامي اختياري است که به تراکنش داده شود وحداکثر32 کارکترمي باشد. درتراکنش هايي که به صورت تودرتونوشته مي شود، بهتراست به بيروني ترين تراکنش نامي اختصاص داده شود . @tran_name_variable : نام يک متغيرتعريف شده توسط کاربراست که بايدبايکي ازانواع داده اي char ، nchar ،varchar وياnvarchar قبلا تعريف شود .
دفترتراکنش
دردفترتراکنش) Log (Transaction ،تراکنش بانامي که بعدازbegin tran آورده شده ،همچنين نام پايگاه داده اي که تراکنش برروي آن تعريف شده ، نام کاربر ،تاريخ ، زمان ،شرح تراکنش وشماره ترتيب Log (LSN) ثبت مي شود . براي هرتراکنش که mark مي شود يک رکورددرجدول logmarkhistory درmsdb درنظرگرفته مي شود . اگريک تراکنش markشده، تغييراتي درپايگاه داده ايجادکرد وکاربربخواهدکه تغييرات انجام نشود ،براي بازسازي پايگاه به وضعيت آن در يک زمان وتاريخ مشخص کافي است بادادن يکي ازمشخصات تراکنش ،آن داده هارادوباره restore کرد .
WITH MARK [ 'description' ]: اگراين عبارت استفاده شود، بايدبراي تراکنش نامي بيان شده باشد . کاربرمي تواند بدين وسيله بادادن نام تراکنش ، آن رادردفترتراکنش) Log (Transactionثبت کند.
description : دراين قسمت توضيحي درموردتراکنش مي تواندآورده شود .
Commit Transaction : به ازاي هرbegin transaction حداقل يک commit transaction وجوددارد وپايان منطقي يک تراکنش رابيان مي کند . پس ازاجراي اين دستورهمه تغييراتي که ازشروع تراکنش تااين قسمت درپايگاه داده بايدانجام شود،دائمي شده و منابعي که تراکنش دراختيارگرفته آزادمي گردد .
COMMIT[ TRAN [ SACTION ] [ transaction_name | @tran_name_variable ] ]
transaction_name و@tran_name_variable : نام هايي هستند که بعدازbegin tran آورده مي شود .
اگر ترکنش هابه صورت تودرتوتعريف شده باشند ، commit اي که دروني تعريف شده تاوقتي که بيروني ترين commit tran اجراءنشود ، منابع انحصاري راآزادنمي کند .
Save Transaction : اين عبارت يک نقطه را دربين تراکنش براي استفاده دردستورRollback transaction تعيين مي کند .
SAVE TRAN [ SACTION ] { savepoint_name | @savepoint_variable }
پارامترها همانند قبل تعريف مي شوند .
ROLLBACK TRANSACTION : اين دستور، وضعيت داده هاي پايگاه رابه نقطه شروع تراکنش ويا به نقطه اي که توسط savepoint ،که باSAVE TRANSACTION مشخص شده ، به عقب برمي گرداند يعني تغييرات ايجادشده ازابتداي تراکنش ياازsavepoint تااينجاراخنثي مي کند . اگراين دستوربدون نام آورده شود ، تاشروع تراکنش تغييرات رابه عقب برمي گرداند واگردريک تراکنش تودرتوبه کاررود،تمام تراکنش هاي داخلي راهم عقبگردمي کندتابه بيروني ترين BEGIN TRAN برسد . اگر دردرون تراکنش چندsavapoint وجودداشت ROLLBACK تانزديک ترين نقطه savepointبه ROLLBACK ، عقبگرد مي کند .
transaction_name و@tran_name_variable : دقيقا مانند begin tran تعريف مي شوند .
savepoint_name : نامي است که در SAVE TRANSACTION تعريف شده است وباعث مي شود که ROLLBACK TRAN تا اين نقطه به عقب برگردد .
@savepoint_variable : نام متغيري است که در SAVE TRANSACTION مشخص مي شود وقبلا بايدتعريف شده باشد .
@@Trancount : متغيري سراسري است که مقدار آن ازنوع integer است ومعرف تعدادتراکنش هاي فعال مي باشد . باهرBEGIN TRANSACTION يک واحد به آن اضافه مي شود وباهرCOMMIT TRAN يک واحدازآن کم مي شود .ROLLBACK TRAN مقدار آن راصفرمي کند به جزROLLBACK TRAN savepoint_name که تغييري درمقدار آن ايجاد نمي کند .
@@error: يک متغيرسراسري است ومقدارآن معرف اين است که آخرين دستوراجراشده باموفقيت انجام شده يانه .اين متغير يک مقدارinteger رابرمي گرداندکه کد خطايي که دراجراي آخرين دستور T_SQL انجام شده، رخ داده است را برمي گرداند . که در صورت عدم وجود خطا ،مقدار صفرمحتوي آن مي باشد .
متن خطا همراه باشماره اي که مشخص کننده نوع خطاست درجدول سيستمي sysmessages آورده شده است .
مثال 1 : مي خواهيم يک مجموعه دستور بنويسيم که در ابتدا دانشجويي را در ترم خاص ودردرس خاص ثبت نام نمايد ، در صورتي که مجموع واحد هاي ثبت نام شده براي دانشجوکمتر از 100 واحد باشد ، ثبت نام وي را در اين درس لغو و در صورتي که معدل کل وي کمتر از 10 باشد ثبت نام وي را در اين ترم لغو نمايد.
begin Transaction
Insert into STDTRM values (8006530,'3811',null)
Save Transaction beforeregiser
insert into REG values(8006530,'1117340','3811',null)
if (select
TotRegUnit from STD
where STD.S# = 8006530)<100)
begin
print ' You can not Register in this course '
Rollback Transaction beforeregiser
end
else if(select Gpa from STD WHERE STD.s#=8006530)<10
begin
print ' You can not Register in this term '
Rollback Transaction
end
PRINT 'success'
Commit Transaction
تريگر (TRIGGER)
تريگر(رهانا) نوع خاصي از رويه است که توسط کاربر درراستاي تامين جامعيت درپايگاه داده هاتهيه شده ودرزمان تغيير پايگاه ازطريق درج ، حذف واصلاح يک tableياview فعال شده وعمليات موردنظررا انجام مي دهد . بدين ترتيب مي توان باتعريف يک تريگراز اعمال تغييرات غيرمعتبرياناسازگار در پايگاه داده ها جلوگيري کرده وازجامعيت ودرستي داده ها اطميينان حصل نمود . اين رويه پارامتر ندارد و به طور ضمني فعال مي شود يعني همانند توابع و رويه ها به صورت مستقيم احضار نمي شوند بلکه در ضمن اجراي يکي ازدستورات Update,Insert يا Delete اجرا مي شوند ، بنابراين هيچ دستوري وجود ندارد که بتوان با استفاده از نام تريگر آن تريگر را احضارنمود و تنها عامل فعال کردن آن تغييرات داده مي باشد.
برروي هر يک ازدستورات insert ،delete وupdate مي توان چند تريگر تعريف کرد .اگردراجراي بخشي ازتريگراشکالي ايجادشود، از کليه عمليات انجام شده صرفنظر مي شود، به اين معنا که همواره يا کل عمليات انجام شده ويا هيچ عملي انجام نخواهدشد .
همچنين مي توان تريگرهاراباعبارات TRIGGER ENABLEوDISABLE TRIGGER که درALTER TABLE به کاربرده مي شوند ، به ترتيب فعال ياغيرفعال کرد .
جداول مجازي Deleted و Inserted
اين جداول ساختار مشابه با جدولي دارد که در آن عمل درج ، حذف ، ويا اصلاح انجام شده است و تريگر روي آن تعريف شده است وبه صورت خودکار توسط SQL ايجاد مي شوند. وجود اين دو جدول نياز به تعريف متغير براي نگهداري اطلاعات را برطرف مي کند .اگر رکورد جديدي اضافه شود اين رکورد هم در جدول اصلي وهم در جدول Inserted وارد مي شود و اگر رکوردي حذف گردد آن رکورد وارد جدول Deleted نيز مي شود. در هنگام Update نيز رکورد قبلي در جدول Deleted و رکورد جديد در جدول Inserted قرار مي گيرد. در واقع براي هر جدولي که تريگر تعريف شده است اين دو جدول در هنگام فعال شدن تريگر به صورت پويا براي هر کاربر در حافظه RAM سيستم ايجاد مي گردد .
به تاپل هاي اين دو جدول مانندتمام جداول ديگر به کمک دستور هاي SQL مي توان دسترسي داشت ولي مستقيما نمي توان آن را تغيير داد.
ايجاد تريگر ها
ساختاراصلي يک تريگر به صورت زيراست :
CREATE TRIGGER trigger_name
ON { table | view }
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ …n ]
} ]
sql_statement [ …n ]
}
}
توجه شود که جدول ياديدي که تريگربرروي آن تعريف مي شود ، بعدازکلمه ON بيان مي شود.
AFTER: يعني اين تريگر بعد از اعمال تغييرات دادهاي در جدول مورد نظر فعال گردد.
:INSTEAD OF يعني تغييرات داده اي در جدول يا ديد مورد نظر اعمال نشده و فقط تريگر فعال گردد. البته ممکن است تغييرات بعداٌ از طريق دستورهاي تريگر در جدول يا ديد مورد نظر اعمال شود.
FOR :مانندAFTER عمل مي کند بااين تفاوت که مي تواند روي ديد ها هم تعريف شود .
تذکر: تريگر درصورت مشخص نکردن هيچ کدام ازسه مورد بالا ، AFTER راپيش فرض قرار مي دهد .
پس ازکلمه AS بدنه تريگر قرارمي گيرد که درآن عملي که تريگر قرار است انجام دهد ، نوشته مي شود .
update(column) : مقدار اين عبارتtrue ياfalse بوده و نشان مي دهد که آيا روي column مورد نظر عمل اصلاح انجام شده است يا نه (در هنگام درج و اصلاح فيلد مورد نظر نيز اين مقدارtrue مي باشد. به کمک اين عبارت و دستورif مي توانيم در مقابل تغيير بعضي از فيلدهاي مورد نظر عکس العمل مناسب پيش بيني نمود.
نکته :دربدنه تريگرنمي توان ازدستورات CREATE DATABASE ، ALTER DATABASE وDROP DATABASE استفاده کرد .
تغيير و حذف تريگر
براي تغييروياحذف يک تريگربه ترتيب ازدستورات ALTER TRIGGER و DROP TRIGGER استفاده مي شود .
ALTER TRIGGER trigger_name . . .
DROP TRIGGER trigger_name
مثال2 : باتوجه به اين که به دليل قانون جامعيت ارجاعي نمي توان دانشجويي که ثبت نام دارد را از جدول STD حذف نمود تريگري روي اين جدول مي نويسيم که به جاي دستور Delete ابتدا دروس ثبت نامي وي در جدول REG وبعدا رکورد هاي مرتبط با دانشجو در جدول STDTRM را حذف نموده ونهايتا دا نشجو را از جدول STD حذف نمايد.
create trigger Tr_Delete_STD
ON STD
INSTEAD OF DELETE
AS
Begin
DECLARE @s# int
select @s# = s# from Deleted
delete from REG where REG.S# = @S#
delete from STDTRM where STDTRM.S# = @S#
delete from STD where STD.S# = @S#
print ' Delete student from REG and STDTRM and STD '
End
مثال3 : تريگري مي نويسيم که وقتي نمره دانشجو در جدول REG اصلاح مي شود معدل ترم دانشجو(TrmGpa) در جدول STDTRM اصلاح شود .
CREATE Trigger Tr_Update_TrmGpa
ON REG
after Update
AS
if Update(Grade)
BEGIN
declare @SumGrade dec(4,1)
declare @SumUnit dec(4,1)
DECLARE @S# INT
DECLARE @TrmNo char(4)
DECLARE @c# char(7)
SELECT @S# = S# , @TrmNo = TrmNo , @C# = C# FROM INSERTED
select @SumGrade = sum(CRS.Unit*REG.Grade),@SumUnit = sum (unit) from REG,CRS
where REG.c# = CRS.c#
and
REG.C# = @C#
and
REG.TrmNO =@TrmNo
and
Reg.s# = @s#
update STDTRM SET TrmGpa=(@sumGrade/@SumUnit)
where stdtrm.s# =@S#
and
stdtrm.TrmNo=@TrmNo
print 'Grade and TrmGpa Updated'
END
مثال 4 : تريگر ي مي نويسيم که هر بار که يک دانشجو در درسي ثبت نام مي کند ، در صورتي که درس اخذ شده جزء دروس گذرانده وقبول شده دانشجو باشد با دادن پيغام مناسب، از ثبت نام وي جلوگيري نمايد .
create trigger Tr_RepeatedCourse
on reg
after insert
as
begin
if ((select count(*) from reg,inserted
where
REG.c# = inserted.c#
and
REG.s# = INSERTED.s#
and
REG.TrmNo <> Inserted.TrmNo
and
REG.grade> (select passgrade from CRS where CRS.c# =Inserted.c#))>0)
begin
print 'You register this course in previous terms'
rollback transaction
return
end
print 'You register this course '
commit transaction
End
مثال 5 : مي خواهيم تريگري بنويسيم که هر بار که يک دا نشجو در درسي ثبت نام مي کند تعداد افرادي را که در آن درس ثبت نام کرده اند محاسبه نموده ، در صورتي که تعداد آن ها بيش از 60 نفر باشد با دادن پيغام مناسب ، ازثبت نام وي جلوگيري نمايد.
create trigger Tr_CRS_Capacity
ON REG
After INSERT
AS
Begin
declare @C# char(7)
declare @cnt int
DECLARE @TrmNO char(4)
select @C#=C# , @TrmNO=TrmNO from INSERTED
select @cnt=count(*) from reg
where
REG.c# =@c#
and
REG.TrmNo = @TrmNO
if @cnt > 60
begin
print 'This course have not capacity!'
rollback transaction
return
end
commit transaction
End
دستور کار
1- با استفاده تابع TotRegUniFn_Updateتريگري به نام Tr_Update_TotRegUnit بنويسيد که هر بار که در جدول REG رکوردي درج يا حذف مي شود مقدار جديذTotRegUnit در جدول STD اصلاح کند.
2 - تريگري به نام Tr_Update_Gpa_TotPassUnitبنويسيد که وقتي نمره دانشجو در جدول REG اصلاح مي شود معدل کل دانشجو(Gpa) و مجموع واحد پاس شده دانشجو (TotPassUnit) در جدول STD اصلاح شود .
3- تريگر به نام Tr_Del_Prereq بنويسيد که هر بار که يک دا نشجو در درسي ثبت نام مي کند قبل از درج ثبت نام بررسي کرده در صورتي که درس هاي پيش نياز آن را اخذ نکرده باشد با دادن پيغام مناسب، از بت نام وي جلوگيري نمايد .
4- يک تريگر به نام Tr_Unit_Limitبنويسيد که هر بار که يک دا نشجو در درسي ثبت نام کند و يا ثبت نام خود را از نظر درس اخذ شده اصلاح کند . مجموع وا حد هاي ثبت نامي اورا در ترم جاري محاسبه نموده ، در صورتي که بيش از20واحد ثبت نام نموده بعد از دادن پيغام مناسب ، ازثبت نام وي جلوگيري نمايد .
آزمايش 6
پيش آگاهي
در اين آزمايش مي خواهيم جدول جديدي تعريف کنيم وبااستفاده از آن به سؤالاتي پاسخ دهيم که در آنها ارجا ع جدول به خود جدول صورت مي گيرد که دراين صورت براي join کردن جدولي باخودش الزامابايدازنام مستعاراستفاده شود .
جدول زيررادرنظرمي گيريم :
EMPLOYEE(E#,Ename,EMgr#)
دراين جدول، E# شماره کارمنديبانوع داده اي nchar(5) ، Ename نام کارمند بانوع داده اي nvarchar(50) وEMgr# شماره کارمندي رئيس کارمندبانوع داده ايnchar(5) مي باشد و کليد اصلي فايل ،صفت خاصه E# مي باشد وEMgr# راکليدخارجي که به E# رجوع مي کند ، تعريف مي کنيم .
مثال : مي خواهيم تابعي به نام fn_FindReports تعريف کنيم که مقدارپارامترINE# رابه عنوان ورودي دريافت کرده ويک جدول بانام retFindReports ،شامل شماره ونام کارمندان زيردست کارمندINE# رادرکليه سطوح تاپايين ترين سطح رابرگرداند . توجه شودکه الگوريتم مذکوربه صورت کلي يک الگوريتم بازگشتي مي باشد که به دليل عدم وجود امکانات بازگشتي درMS_SQL الگوريتم مذکورشبيه سازي شده است .
Create function fn_FindReports (@INE# nchar(5))
RETURNS @retFindReports TABLE (E# nchar(5) primary key,
Ename nvarchar(50) NOT NULL,
EMgr# nchar(5) refrences EMPLOYEE(E#))
AS
BEGIN
متغيري رابراي نگاه داشتن تعداد سطرهاي اضافه شده به جدول کمکي ، تعريف مي کنيم .
DECLARE @RowsAdded int
جدولي تعريف مي کنيم تا نتايج مياني تابع رادرآن نگاه داريم ودراين جدول يک فيلد به نام PROCESSED تعريف مي کنيم تابدين وسيله بتوانيم سطرهاي پردازش شده راازسطرهاي جديدواردشده، جداکنيم .
DECLARE @reports TABLE (E# nchar(5) primary key,
Ename nvarchar(50) NOT NULL,
EMgr# nchar(5),
processed tinyint default 0)
جدول ايجادشده رابادادن مشخصات مربوط به کارمندي که شماره مستخدمي آن به عنوان ورودي داده شده ، توسط جدول مقداردهي اوليه مي کنيم .
EMPLOYEE
INSERT @reports
SELECT E#, Ename, EMgr#,0
FROM employee
WHERE E# = @InE#
@RowsAdded درابتدابامقدار1 set مي شود.
SET @RowsAdded = @@rowcount
اضافه شده که زيردست هايش پيدانشده، کارهاي زيرراانجام بده . @reports تاوقتي که کارمندجديدي در جدول
WHILE @RowsAdded > 0
BEGIN
فيلدprocessed هرکارمندي که قراراست زيردست هايش پيداشود رابامقدار1 set کن .
UPDATE @reports
SET processed = 1
WHERE processed = 0
. وارد کنreports برابر 1دارد رادرجدول processed زيردست هاي هرکارمندکه مشخصات
INSERT @reports
SELECT e.E#, e.Ename, e.EMgr#, 0
FROM employee e, @reports r
WHERE e.EMgr#=r.E# and e.EMgr# <> e.E# and r.processed = 1
بريز. @RowAdded اضافه شده رادرمتغير@reports تعدادسطرهايي که جديدا به جدول
SET @RowsAdded = @@rowcount
کنset کارمنداني که زيردستان آن ها پيداشده رابامقدار2 processedفيلد
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
نتيجه نهايي رادرجدول خروجي تابع وارد کن
INSERT @retFindReports
SELECT E#, Ename, EMgr#
FROM @reports
RETURN
END
GO
دستورکار
1- جدول EMPLOYEE رابامشخصات گفته شده درقسمت پيش مطالعه ايجاد کرده وآن راباداده مناسب پرنماييد . وسپس به سؤالات زيرپاسخ دهيد .
2- تابع مثال ارائه شده درقسمت پيش آگاهي را به کمک دستور select وباشماره کارمندي هاي مختلف ، احضار نموده ونتايج حاصله رابررسي نماييد .
3- يک تابع بنويسيد که شماره کارمندي يک کارمند راگرفته ونام رئيس ونام رئيس رئيس کارمند رااستخراج نمايد .
4- تابعي بنويسيد که شماره کارمندي يک کارمند راگرفته ونام کارمنداني رااستخراج کند که رئيس آن ها رئيس کارمندمذکورباشد .(يعني کارمنداني که رئيس مشترک دارند .)
5- يک تابع بنويسيد که شماره کارمندي کارمند را به عنوان ورودي گرفته ونام زيردستان کارمندرادرسطح چهارم استخراج نمايد.
6- تابعي تعريف کنيد که شماره کارمندي يک کارمند راگرفته ونام رئيس کارمندرادرسطح چهارم استخراج کند .
7- يک تابع بنويسيد که شماره کارمندي يک کارمند راگرفته ونام کليه رؤساي کارمندمذکوررااستخراج نمايد .