پشتیبانی: 09131253620
ارتباط با ما
تلگرام: 09131253620

برجسته ترین ها
گروه های مقاله ها
HyperLink


تریگر و تراکنش در sql server تاریخ درج: ١٣٩۶/٠٨/٠٧

 تراکنش هاوتريگرها درSQL                                                                                                                                                  پيش آگاهي                                                                                                                                                                        مقدمه :يکي ازاصول تامين جامعيت داده اي ، امکان اعمال به روز رساني منتشرشونده وتعريف واحدهاي منطقي کاردرراستاي اجراي کامل ياعدم اجراي کليه مواردازيک مجموعه کارمي باشد . براي پياده سازي اين اصل SQL-Server تريگر(رهانا)وتراکنش رادراختياربرنامه نويس قرارداده است . احضارتريگرالزاما به صورت ضمني بوده وازطريق اجراي يکي ازدستورات insert ،delete ياupdate انجام مي شود . برنامه نويس رهانارابراي اجراي دستوراتي که بايدبه صورت خودکاربراي حفظ جامعيت داده ها انجام شود ، تعريف مي کند . بنابراين تريگررامي توان تراکنشي دانست که باتغييرات داده فعال مي شود وامکان اجراي يک واحد منطقي کاررا درجريان تغييرات داده اي فراهم      مي کند .

تراکنش  (Transaction)
تراکنش مجموعه اي از دستورات SQL است که معمولا تغييري در پايگاه داده ايجاد مي کند به گونه اي که جامعيت و يکپارچگي داده ها همچنان برقرار باشد.اين مجموعه دستورات يا به طورکامل اجراء مي گردند ويا درصورت وجود اشکال دربين دستورات اجراي تمامي آن ها لغومي گردد .
 درواقع تراکنش ،يک واحد منطقي کار(Logical unit work) است که با استفاده از آن مي توان از پايان درست وکامل کار اطمينان پيدا کرد.
transaction تراکنش در sql server
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- يک تابع بنويسيد که شماره کارمندي يک کارمند راگرفته ونام کليه رؤساي کارمندمذکوررااستخراج نمايد .

 دانلود فایل کامل این مقاله در قالب فایل word روی همین لینک لطفا کلیک فرمائید . . .


تگها: transaction در sql   آموزش ساخت تریگر   تراکنش sql   تریگر sql   
 

HyperLink

ارسال نظر در مورد این مطلب
نام :  
آدرس ایمیل :  
متن پیام :  
کد امنیتی :  
   
   
نظری برای نمایش وجود ندارد
 
این مطلب را به اشتراک بگذارید: