در امتداد آموزشهای MySQL و پس از دیدها (views) در MySQL به مبحث جذاب و معروف تریگر ها (trigger) در MySQL رسیدهایم. در این جا قصد داریم به مفهوم، دلیل استفاده، نحوه ایجاد، محدودیتها، حذف تریگرها بپردازیم. با ما در الفبایت همراه باشید.
تریگر (TRIGGER) چیست؟
شاید یکی از اصلی ترین سوالاتی که تا کنون میتوان پرسید چیستی تریگر باشد. تریگر شیءای از بانک اطلاعاتی است که مربوط به یک جدول میباشد. به تعبیری دیگر میتوان آن را ابزاری دانست که هنگام درج، بروزرسانی یا حذف سطرها از جدول فعال میشود و یک یا چند دستور SQL را به طور خودکار اجرا میکند.
هر تریگر در بانک اطلاعاتی باید نامی متفاوت از بقیه تریگرها داشته باشد. رویدادها و اتفاقاتی که تریگرها میتوانند بر روی آنها فعال شوند عبارتند از: insert, update, delete. هر تریگر تنها بر روی یک جدول اعمال میشود. اما برای یک جدول میتوان چندین تریگر تعریف کرد به طوری که هر تریگر به یکی از این رویداد های update , insert , delete پاسخ دهد.
نکته: تریگرها توسط کاربر فراخوانی نمیشوند و بهطور خودکار اجرا میشوند.
تریگرها را میتوان طوری تعریف کرد که قبل و بعد از یک رویداد مانند insert اجرا شوند. به این ترتیب برای هر رویداد میتوان دو تریگر تعریف کرد.
چرا از تریگرها استفاده میکنیم؟!
تاکنون به مفهوم تریگر، چیستی و نحوه کار کرد آن پرداختیم. زمان آن رسیده که دلایل استفاده از تریگرها را مورد بررسی قرار دهیم.
فواید استفاده از تریگرها:
- تریگر میتواند مقادیر سطری را که باید اضافه یا بروزرسانی و یا حذف شود را بررسی کند و تعیین کند که چه مقادیری باید پذیرفته شوند. به این اتفاق اعتبارسنجی میگویند. فرض کنید در حال ثبت سن افراد در پایگاه داده میباشید. سن یک شخص نباید منفی و یا بیشتر از ۱۲۰ سال باشد (فرض کنید هیچ انسانی بیشتر از ۱۲۰ سال عمر نکند!!). حال کاربر به اشتباه برای سن ۵۰ یک صفر بیشتر وارد که و سن وارد شده ۵۰۰ میباشد در این جا تریگر به کمک میآید و با اعتبارسنجی خود این مشکل را برطرف میسازد.
- می توانید عملکرد insert, update , delete را اصلاح کنید. به عنوان مثال قبل از اجرای دستور insert میتوانید مقادیر پیشفرض تهیه کنید که براساس زمان فعلی مربوط به ستونها تعیین میشود. معمولا ستون های از نوع timestamp میتوانند به طور خودکار زمانی بر اساس زمان حال را به آنها نسبت داد.
نکته: مواردی که در بالا گفته شد تنها مثال بوده و اینها تنها به الگوی طراحی پایگاهداده شما وابسته میباشد. شما میتوانید از تریگرها با رعایت اصول به هرشکل که مایل هستید استفاده کنید.
چگونه میتوان یک تریگر ایجاد کرد؟
برای ایجاد و یا تعریف تریگر باید از دستور create trigger که به شکل زیر میباشد استفاده کرد:
[crayon-662f1115ced1d596999453/]در این دستور trigger_name نام تریگر است که توصیه میشود برای طراحی زیباتر و خواناتر بودن، نام تریگر را ترکیبی از نام جدول و مشخصات تریگر انتخاب کرد. به عنوان مثال تریگر users_bi کاری که قرار است انجام دهد آن است که قبل از (before) اضافه کردن (insert) در جدول Users عملیاتی را انجام دهد.
قسمت after یا before زمان اجرا شدن تریگر را مشخص میکند که before به معنای قبل و after به معنای بعد میباشد.
قسمت for each row نشان میدهد که تریگر برای هرسطر که رویدادها بر روی آن اتفاق میافتد اعمال شود نه برای تمامی سطرهای جدول.
Trigger_statement دستوری است که پس از فعال شدن تریگر اجرا میشود. این دستور میتواند مرکب باشد که با استفاده از begin/end ساخته میشود. دستورات مرکب را در داخل مثالها مورد بحث و بررسی قرار خواهیم داد.
دستیابی به مقادیر قبل و جدید ستونها:
در هنگام تعریف تریگر میتوان به ستونهای مربوط به سطری که اضافه، بروزرسانی و یا حذف شده دسترسی داشت. با این دسترسی میتوان مقادیر قبل و بعد را مورد بررسی قرار داد.
برای دسترسی به یک ستون خاص از old قبل از نام ستون باید استفاده کرد و برای دستیابی به مقدار جدید باید از new استفاده کرد. به عنوان مثال برای دستیابی به مقدار قبلی ستون username از جدول users از پایگاهداده alefbyte باید از old قبل username مانند old.username استفاده کرد و برای بازیابی مقدار جدید new.username را به کار برد.
چند نکته موجود در رابطه با این بازیابیها:
- در تریگر مربوط به رویداد insert عبارت new.column مقداری را نشان خواهد داد که باید در جدول ثبت شوند. اما old دیگر قابل استفاده نمیباشد.
- در تریگر delete عبارت old.column قابل استفاده میباشد و مقداری که باید حذف شود را نشان میدهد اما new دیگر قابل استفاده نیست. (چون مقدار جدیدی نخواهد داشت.)
- در تریگر update عبارات old.column و new.column هر دو قابل استفاده میباشند و مشکلی نخواهند داشت.
نکته: یک جدول تنها میتواند یک تریگر before insert داشته باشد و نمیتوان برای آن تریگر before insert دیگری تعریف کرد. بنابراین درنهایت هر جدول میتواند ۶ تریگر داشته باشد. این اتفاق یک محدودیت نیست زیرا میتوان چند کار را در یک تریگر انجام داد و آن را در دستور مرکب که در مثالها به آنها اشاره خواهد شد قرار داد.
مثال: روی جدول users از پایگاهداده alefbyte تریگری ایجاد کنید که قبل از update کردن سطرهای این جدول نام کاربری شناسه کاربری فعلی user را که قرار است در متغیرهایی در MySQL ذخیره کند.
برای انجام این کار باید به شکل زیر و طبق دستورات عمل کرد:
- باز کردن پایگاهداده alefbyte:
- دستور زیر برای تغییر علامت پایان دهنده دستورات:
با کمک این دستور میتوان به جای علامت “;” از علامت دیگری استفاده کرد. این کار در زمان نوشتن دستورات مرکب به کمک ما خواهند آمد.
- دستورات ساخت تریگر به همراه دستور مرکب آن:
در این دستور خط اول تا چهارم مشخص هستند و در توضیح ساخت تریگر به توضیح آنها پرداختیم. اما از خط ۵ تا آخر خط ۷ مربوط به دستور مرکب میباشد. دستور مرکب به مجموعه دستوراتی گفته میشود که چندین کار برای ما انجام دهند یا به عبارتی سادهتر مجموعه دستوراتی که در یک مرحله اجرا خواهند شد و چندین کار را انجام خواهند داد میگویند. ابتدای این نوع دستورات با دستور begin و انتهای آن ها با دستور end پایان میپذیرد. اما اگر توجه کنید در زمان نوشتن این دستور از خاتمه دهنده “;” استفاده شده است. اگر از “$” استفاده می کردیم MySQL فکر میکرد که دستور ساخت تریگر به پایان رسیده و آن را خاتمه میداد و بعد با ارورهایی مواجه میشدیم.
در خط ۶و۷ در متغیرهای username و id مقادیر قدیمی ستونهای name و username نگهداری خواهند شد.
- دستور تغییر خاتمه دهنده دستورات SQL:
حال خاتمه دهنده را به شکل اولیه و مورد نظر خود در آوردیم و زمان اجرای تریگر به اروری برخورد نخواهیم کرد.
- تست کردن و امتحان تریگر:
- دستور زیر را اجرا کنید تا متغیرهای name و username که در MySQL ذخیره شدهاند را مشاهده کنیم.
همانطور که مشاهده میکنید هنگام اجرای دستور update تریگر آپدیت مربوط به جدول users فعال شده و متغیرهای name و username را ذخیره میکند که میتوان با دستور select آنها را بازیابی کرد.
تا اینجا سادهترین مثال ممکن را بررسی کردیم در ادامه با مثالی دیگر مبحث را کمی پیچیدهتر خواهیم کرد.
مثال دوم: تریگری ایجاد کنید که قبل از اضافه کردن به جدول users بررسی کند اگر id وارد شده منفی میباشد مقدار آن را تغییر و برابر x قرار میدهد. مقدار x نیز در هر بار یکی بیشتر شود سپس اطلاعات را در جدول ذخیره کند.
برای انجام این کار باید طبق دستورات زیر عمل کرد:
- تغییر خاتمه دهنده دستورات SQL:
- متغیری در پایگاه داده به نام @x تعریف کنید و مقدار آن را برابر صفر قرار دهید.
- ساخت تریگر مربوطه:
خطوط اول تا آخر خط پنجم که واضح هستند. در این تریگر باید از دستور کنترلی if استفاده کنیم. اگر کمی با زبانهای برنامهنویسی آشنا باشید با ساختارهای for، if و… آشنا میباشید که برای کنترل و بررسی هستند. در SQL نیز این دستورات وجود دارند. در اینجا پس از if باید شرط مورد نظر خود را بیان کنیم new.id < 0 اگر این شرط درست باشد هر دستوری که پس از then نوشته میشود اجرا میشود اگر درست نباشد اجرا نخواهد شد در این مثال ما بررسی میکنیم اگر new_id کمتر از صفر باشد دستورات set که زیر if قرار دارند اجرا شوند. فراموش نکنید در انتهای دستورات که میخواهید زیر if اجرا شود حتما باید دستور if را با endif پایان دهید در غیر این صورت دچار ارورهای خواهید شد.
- تغییر خاتمه دهنده دستورات SQL:
- دستور select برای مشاهده اطلاعات موجود در جدول users:
- وارد کردن اطلاعات user جدید به جدول با دستور insert:
در دستور بالا سطری به جدول users وارد میشود که قبل از تریگر به کار افتاده و اطلاعات را تصحیح میکند.
- برای اطمینان از کار انجام شده توسط تریگر از دستورات زیر استفاده کنید و نتیجه را مشاهده کنید:
نکته: ممکن است شما در اینجا به علت auto_inceriment بودن ستون id نتوانید مقداری وارد کنید. اگر با این ارور برخورد کردید باید ساختار جدول را تغییر دهید. برای تغییر ساختار میتوانید به “نحوه کار کردن با جدولها در MySQL” مراجعه کنید. در ضمن جدول users در “مفهوم دیدها در MySQL – قسمت اول” ساخته شد.
تریگرها محدودیتهایی نیز دارند!
تریگرها در MySQL دارای محدودیتهایی نیز هستند:
- قابل فراخوانی نمیباشند: نمیتوان تریگری را به طور جداگانهای فراخوانی کرد حتما باید رویدادی برای جدول پیش بیاید تا تریگر فعال شده و کاری انجام دهد.
- نمیتوان داخل یک تریگر تراکنشی پیادهسازی کرد (برای آشنایی با تراکنشها به “مفهوم تراکنش در MySQL” مراجعه کنید.) ممکن است یک رویداد داخل تراکنش اتفاق بیافتد و تریگر در آن اجرا میشود اما نمیتوان داخل تریگر تراکنش ایجاد کرد. در “مفهوم تراکنش در MySQL” گفته بودیم اگر تراکنشی آغاز شده باشد و تراکنش دیگری را بخواهیم آغاز کنیم تراکنش قبلی بسته خواهد شد. به این منظور نمیتوان داخل تریگر یک تراکنش دیگر ایجاد کرد و دچار ارورهایی خواهید شد و پیادهسازی پایگاهداده شما را دچار مشکلاتی خواهد کرد.
- برای دیدها (view) یا جدولهای موقت (temporary) نمیتوان تریگری ایجاد کرد (برای آشنایی با دیدها به “مفهوم دیدها در MySQL – قسمت اول” و جدولهای موقت به “نحوه کار کردن با جدولها در MySQL” میتوانید مراجعه کنید.)
MySQL خطاهایی که هنگام اجرای تریگر به وحود میآیند را به صورتهای گوناگونی اداره میکند:
- اگر تریگر از نوع before باشد و دچار مشکل و ارور شود، عملیات روی سطر مود نظر انجام نمیشود.
- تریگر از نوع after زمانی اجرا خواهد شد که تریگر before (در صورت وجود) و رویداد مورد نظر با موفقیت انجام شده باشند.
- خطاها در تریگر از نوع before یا after موجب اجرا نشدن رویداد مورد نظرما میشود.
حذف تریگرها:
گاهی ممکن است بخواهیم به هر دلیلی تریگر مورد نظر را حذف کنیم و یا آن را از ابتدا ایجاد کنیم. برای این کار باید از دستور drop به شکل زیر استفاده کنیم:
[crayon-662f1115cee11378749588/]مثال: تریگر users_bi را از پایگاه داده alefbyte حذف کنید.
[crayon-662f1115cee1f056196571/]نکته: گاهی اوقات ممکن است تریگر به طور خودکار حذف شود:
- زمانی که جدول حذف میشود.
- زمانی که پایگاه داده مورد نظر حذف میشود.
به پایان این قسمت از آموزش MySQL رسیدیم.
شما می توانید سوالات خود را در قسمت دیدگاه ها از ما بپرسید. فراموش نکنید نظرها و دیدگاههای خود را برای افزایش کیفیت پستهای بعدی با ما درمیان بگذارید.
منبع: سایت رسمی MySQL ، تریگر