عند العمل مع قواعد البيانات العلاقية Relational Databases ولغة الاستعلام البنيوية SQL فإننا نجري معظم العمليات على البيانات الناتجة عن استعلامات منفَّذة صراحةً مثل استعلامات SELECT أو INSERT أو UPDATE. لكن يمكننا توجيه قواعد بيانات SQL لتنفيذ إجراءات مُعرَّفة مسبقًا تلقائيًا في كل مرة يقع فيها حدث معين باستخدام القوادح أو محفّزات التنفيذ Triggers. يمكننا مثلًا استخدام هذه القوادح للاحتفاظ بسجل يتضمن جميع تعليمات الحذف DELETE بحيث نحفظ بعد كل عملية حدث تقع تفاصيل هذه العملية ومن قام بها ومتى، كما يمكن استخدامها لتحديث البيانات التراكمية مثل المجموع أو المتوسط حيث يمكننا تحديث هذه البيانات الإحصائية كلما جرت عملية إضافة أو تحديث على البيانات الموجودة.
سنستخدم في هذا المقال قوادح SQL مختلفة لتنفيذ الإجراءات تلقائيًا عندما ندرج الصفوف أو نحدثها أو نحذفها.
مستلزمات العمل
يجب توفر حاسوب يشغّل نظام إدارة قواعد بيانات علاقية RDBMS مستند إلى لغة SQL. وقد اختبرنا التعليمات والأمثلة الواردة في هذا المقال باستخدام البيئة التالية:
- خادم عامل على توزيعة أوبنتو Ubuntu مع مستخدم ذي صلاحيات مسؤول مختلف عن المستخدم الجذر، وجدار حماية مضبوط باستخدام أداة UFW كما هو موضح في دليل الإعداد الأولي للخادم مع الإصدار 20.04 من أوبنتو، ومقال كيفية تثبيت توزيعة أوبنتو من لينكس بأبسط طريقة
- نظام MySQL مُثبَّت ومؤمَّن على الخادم كما هو موضح في مقال كيفية تثبيت MySQL على أوبنتو، وقد نفذنا خطوات هذا المقال باستخدام مستخدم MySQL مختلف عن المستخدم الجذر وفق الطريقة الموضحة في الخطوة التالية من المقال
-
معرفة أساسية بتنفيذ استعلامات
SELECTوINSERTوUPDATEوDELETEلمعالجة البيانات في قاعدة البيانات كما هو موضح في مقال كيفية الاستعلام عن السجلات من الجداول في SQL وكيفية إدراج البيانات في SQL وتحديث البيانات في لغة الاستعلام البنيوية SQL وحذف البيانات في لغة الاستعلام البنيوية SQL - معرفة أساسية باستخدام الاستعلامات المتداخلة كما هو موضَّح في مقال كيفية استخدام الاستعلامات المتداخلة في لغة SQL
- المعرفة الأساسية باستخدام الدوال الرياضية التجميعية كما هو موضَّح في مقال كيفية استخدام التعابير الرياضية والدوال التجميعية في SQL
ملاحظة: تجدر الإشارة إلى أنّ الكثير من أنظمة إدارة قواعد البيانات العلاقية RDBMS لها تقديماتها الفريدة من لغة SQL. فلا يفرض معيار SQL صيغةً للقوادح Triggers أو طريقة صارمة لتحقيقها بالرغم من أنها تُعَد جزءًا من هذا المعيار، لذا يختلف تقديمها من قاعدة البيانات إلى أخرى، وتستخدم الأوامر الموضَّحة في هذا المقال صيغة قاعدة بيانات MySQL وقد لا تعمل على محرّكات قواعد البيانات الأخرى.
سنحتاج أيضًا إلى قاعدة بيانات تحتوي على بعض الجداول المُحمَّلة ببيانات تجريبية نموذجية لنتمكّن من التدرب على استخدام المحفّزات، وفي القسم التالي نوضح تفاصيل الاتصال بخادم MySQL وإنشاء قاعدة بيانات تجريبية لاستخدامها في أمثلة هذا المقال.
الاتصال بخادم MySQL وإعداد قاعدة بيانات تجريبية نموذجية
سنتصل في هذا القسم بخادم MySQL وننشئ قاعدة بيانات تجريبية لتطبيق الأمثلة الواردة في هذا المقال، حيث سنستخدم قاعدة بيانات تحفظ مجموعة هدايا تذكارية افتراضية، ونخزّن كل تفاصيل الهدايا التذكارية المملوكة حاليًا، وقيمتها الإجمالية المتاحة ونحتاج للتأكد من أن إجراء حذف الهدايا التذكارية سيُحفَظ في سجل دائم يوضح كافة تفاصيل عملية الحذف.
إذا كان نظام قاعدة بيانات SQL الخاص بنا يعمل على خادم بعيد، نحتاج للاتصال بالخادم باستخدام بروتوكول SSH من جهازنا المحلي كما يلي:
$ ssh user@your_server_ip
ثم نفتح واجهة سطر أوامر خادم MySQL مع وضع اسم حساب مستخدم MySQL الخاص بنا مكان user:
$ mysql -u user -p
ننشئ قاعدة بيانات بالاسم collectibles:
mysql> CREATE DATABASE collectibles;
إذا أُنشئِت قاعدة البيانات بنجاح، فسيظهر الخرج التالي:
Query OK, 1 row affected (0.01 sec)
يمكن اختيار قاعدة البيانات collectibles من خلال تنفيذ تعليمة USE التالية:
$ USE collectibles;
وسيظهر الخرج التالي:
Database changed
اخترنا قاعدة البيانات، وسننشئ عدة جداول تجريبية ضمنها، حيث سيحتوي الجدول collectibles على بيانات مبسَّطة عن الهدايا التذكارية الموجودة في قاعدة البيانات، ويتضمن الجدول الأعمدة التالية:
-
name: يخزّن اسم كل هدية تذكارية، ويستخدم نوع البياناتvarcharبحد أقصى 50 محرفًا -
value: يخزّن قيمة الهدية التذكارية، ويستخدم نوع البياناتdecimalبحد أقصى 5 قيم قبل الفاصلة العشرية وقيمتين بعدها
أنشئ هذا الجدول التجريبي باستخدام الأمر التالي:
mysql> CREATE TABLE collectibles ( mysql> name varchar(50), mysql> value decimal(5, 2) mysql> );
إذا كان الخرج كما يلي، فهذا يعني إنشاء الجدول بنجاح:
Query OK, 0 rows affected (0.00 sec)
سنسمّي الجدول الثاني باسم collectibles_stats وسنستخدمه لتتبّع القيمة المتراكمة لجميع الهدايا التذكارية في المجموعة، وسيحتوي هذا الجدول على صف واحد من البيانات مع الأعمدة التالية:
-
count: يحتوي عدد الهدايا التذكارية المملوكة، ونمثّله باستخدام نوع البياناتint -
value: يخزّن القيمة المتراكمة لجميع الهدايا باستخدام نوع البياناتdecimalبحد أقصى 5 قيم قبل الفاصلة العشرية وقيمتين بعدها
أنشئ هذا الجدول التجريبي باستخدام الأمر التالي:
mysql> CREATE TABLE collectibles_stats ( mysql> count int, mysql> value decimal(5, 2) mysql> );
إذا كان الخرج كما يلي، فهذا يعني إنشاء الجدول بنجاح:
Query OK, 0 rows affected (0.00 sec)
سنسمي الجدول الثالث والأخير بالاسم collectibles_archive، والذي سيتتبّع جميع الهدايا التذكارية المحذوفة من المجموعة لضمان عدم اختفائها أبدًا، وسيحتوي على بيانات مشابهة للجدول collectibles مع تاريخ الإزالة، وسيستخدم الأعمدة التالية:
-
name: يحتوي اسم كل هدية تذكارية محذوفة، ونمثّله باستخدام نوع البياناتvarcharبحد أقصى 50 محرفًا -
value: يخزّن قيمة الهدايا التذكارية لحظة الحذف باستخدام نوع البياناتdecimalبحد أقصى 5 قيم قبل الفاصلة العشرية وقيمتين بعدها -
removed_on: يخزّن تاريخ ووقت الحذف لكل هدية تذكارية مؤرشفة باستخدام نوع البياناتtimestampباستخدام القيمة الافتراضيةNOW()التي تعني التاريخ الحالي لإدراج صف جديد في هذا الجدول
أنشئ هذا الجدول التجريبي باستخدام الأمر التالي:
mysql> CREATE TABLE collectibles_archive ( mysql> name varchar(50), mysql> value decimal(5, 2), mysql> removed_on timestamp DEFAULT CURRENT_TIMESTAMP mysql> );
إذا كان الخرج كما يلي، فهذا يعني إنشاء الجدول بنجاح:
Query OK, 0 rows affected (0.00 sec)
نحمّل بعد ذلك الجدول collectibles_stats بالبيانات الأولية لمجموعة الهدايا التذكارية من خلال تنفيذ عملية INSERT INTO التالية:
mysql> INSERT INTO collectibles_stats SELECT COUNT(name), SUM(value) FROM collectibles;
تضيف عملية INSERT INTO السابقة صفًا واحدًا إلى الجدول collectibles_stats مع القيم المحسوبة باستخدام الدوال التجميعية Aggregate Functions لحساب عدد الصفوف في الجدول collectibles ولجمع قيم جميع الهدايا التذكارية باستخدام العمود value والدالة SUM. يشير الخرج التالي إلى إضافة الصف بنجاح:
Query OK, 1 row affected (0.002 sec) Records: 1 Duplicates: 0 Warnings: 0
يمكننا التحقق من ذلك من خلال تنفيذ تعليمة SELECT التالية مع الجدول collectibles_stats:
mysql> SELECT * FROM collectibles_stats;
لا توجد هدايا تذكارية في قاعدة البيانات حتى الآن، لذا يكون العدد الأولي للعناصر هو 0 وتكون القيمة المتراكمة هي NULL كما يلي:
+-------+-------+ | count | value | +-------+-------+ | 0 | NULL | +-------+-------+ 1 row in set (0.000 sec)
نحن الآن جاهزون لمتابعة هذا المقال والبدء باستخدام القوادح Triggers في MySQL.
فهم Triggers
القوادح Triggers هي تعليمات مُعرَّفة لجدولٍ معين تنفّذها قاعدة البيانات تلقائيًا في كل مرة يحدث فيها حدث محدَّد في هذا الجدول، ويمكن استخدامها لضمان تنفيذ بعض الإجراءات بتناسق في كل مرة نُنفَّذ فيها تعليمة معينة مع الجدول بدلًا من أن يحتاج مستخدمو قاعدة البيانات لتنفيذها يدويًا.
يُعرَّف كل Trigger مرتبط بجدول باسم يحدّده المستخدم، وشرطين يوجّهان محرّك قاعدة البيانات بالوقت المناسب لتنفيذ المحفّز، ويمكن تجميع هذين الشرطين ضمن فئتين منفصلتين هما:
-
حدث قاعدة البيانات: يمكن تنفيذ المحفّزات عند تشغيل تعليمات
INSERTأوUPDATEأوDELETEمع الجدول -
وقت الحدث: يمكن تنفيذ المحفّزات أيضًا قبل
BEFOREأو بعدAFTERالتعليمة المحدَّدة
يؤدي الجمع بين مجموعتي الشروط السابقتين لستة احتمالات منفصلة للقوادح التي تُنفَّذ تلقائيًا في كل مرة يتحقق فيها الشرط المشترك. القوادح التي تحدث قبل تنفيذ التعليمة التي تحقق الشرط هي BEFORE INSERT و BEFORE UPDATE و BEFORE DELETE، ويمكن استخدامها لمعالجة البيانات والتحقق من صحتها قبل إدراجها أو تحديثها في الجدول أو لحفظ تفاصيل الصف المحذوف لأغراض التدقيق أو الأرشفة. المحفّزات التي تحدث بعد تنفيذ التعليمة التي تحقق الشرط هي AFTER INSERT و AFTER UPDATE و AFTER DELETE، ويمكن استخدامها لتحديث القيم الملخَّصة في جدول منفصل بناءً على الحالة النهائية لقاعدة البيانات بعد التعليمة.
يمكن تنفيذ إجراءات مثل التحقق من صحة بيانات الدخل، ومعالجتها، أو أرشفة الصف المحذوف، إذ تسمح قاعدة البيانات بالوصول إلى قيم البيانات من داخل القوادح، ويمكن استخدام البيانات المدرَجة حديثًا فقط بالنسبة لقوادح INSERT، ويمكن الوصول إلى كلٍّ من البيانات الأصلية والمُحدَّثة بالنسبة لمحفّزات UPDATE، وتكون بيانات الصف الأصلية فقط متاحة للاستخدام بالنسبة لمحفّزات DELETE نظرًا لعدم وجود بيانات جديدة للإشارة إليها.
يمكن الوصول للبيانات المُستخدَمة في جسم القادح ضمن السجل OLD بالنسبة للبيانات الموجودة حاليًا في قاعدة البيانات والسجل NEW بالنسبة للبيانات التي سيحفظها الاستعلام، ويمكن الإشارة إلى الأعمدة الفردية باستخدام الصيغة OLD.column_name و OLD.column_name.
يوضّح المثال التالي الصيغة العامة لتعليمة SQL المُستخدَمة لإنشاء قادح جديد:
mysql> CREATE TRIGGER trigger_name trigger_condition mysql> ON table_name mysql> FOR EACH ROW mysql> trigger_actions;
لنشرح التعليمة السابقة بالتفصيل:
-
CREATE TRIGGER: اسم تعليمة SQL المُستخدَمة لإنشاء قادح جديد في قاعدة البيانات -
trigger_name: هو الاسم الذي يحدّده المستخدم للقادح، ويصف دوره مثل استخدام أسماء الجداول وأسماء الأعمدة لوصف معناها -
ON table_name: نخبر قاعدة البيانات بأن القادح يجب أن يراقب الأحداث التي تحدث في الجدولtable_name -
trigger_condition: أحد الاختيارات الستة المُحتملة التي تحدد متى يجب تشغيل القادح مثلBEFORE INSERT. -
FOR EACH ROW: تخبر قاعدة البيانات بأنه يجب تشغيل القادح لكل صف يتأثر بالحدث. تدعم بعض قواعد البيانات أنماطًا إضافية للتنفيذ مختلف عن النمطFOR EACH ROW، ولكن تشغيل التعليمات من جسم القادح لكل صف متأثر بالتعليمة التي تسبّبت في تنفيذ القادح هو الخيار الوحيد في حال MySQL -
trigger_actions: جسم القادح الذي يحدّد ما يحدث عند تنفيذه، وهو تعليمة SQL واحدة، ويمكن تضمين تعليمات متعددة في جسم القادح لإجراء عمليات معقدة باستخدام الكلمات المفتاحيةBEGINوENDلتضمين التعليمات ضمن كتلة، ولكن ذلك خارج نطاق هذا المقال
اطّلع على التوثيق الرسمي للمحفّزات لمعرفة المزيد حول الصيغة المُستخدمَة لتعريف القوادح. سننشئ في القسم التالي أمثلة على قوادح تعالج البيانات قبل إجراء عمليتي INSERT و UPDATE.
معالجة البيانات باستخدام محفزات BEFORE INSERT و BEFORE UPDATE
سنستخدم في هذا القسم قوادح لمعالجة البيانات قبل تنفيذ تعليمات INSERT و UPDATE، حيث سنستخدم المحفّزات في للتأكّد من أن جميع الهدايا التذكارية في قاعدة البيانات تستخدم أسماءً بحروف كبيرة لتحقيق التناسق. في حال لم نستخدم قوادح سيتوجب علينا تذكّر استخدام أسماء الهدايا التذكارية بحروف كبيرة لكل تعليمة INSERT و UPDATE، وإذا نسينا، فستحتفظ قاعدة البيانات بالبيانات كما هي وهذا قد يؤدي إلى حدوث أخطاء محتملة في مجموعة البيانات.
لنبدأ بإدخال مثال لعنصر من الهدايا التذكارية بالاسم spaceship model وبقيمة 12.50 دولار، وسنكتب اسم العنصر بحروف صغيرة لتوضيح المشكلة. لننفّذ التعليمة التالية:
mysql> INSERT INTO collectibles VALUES ('spaceship model', 12.50);
تؤكد الرسالة التالية إضافة العنصر:
Query OK, 1 row affected (0.009 sec)
يمكننا التحقق من إدراج الصف من خلال تنفيذ استعلام SELECT التالي:
mysql> SELECT * FROM collectibles;
وسيظهر الخرج التالي:
+-----------------+-------+ | name | value | +-----------------+-------+ | spaceship model | 12.50 | +-----------------+-------+ 1 row in set (0.000 sec)
حُفِظ هذا العنصر كما هو مع كتابة اسمه بحروف صغيرة فقط. لنتأكد من كتابة جميع الهدايا التذكارية اللاحقة بحروف كبيرة دائمًا من خلال إنشاء قادح باسم BEFORE INSERT لمعالجة البيانات المُمرَّرة إلى قاعدة البيانات قبل حدوثها. نشغّل الآن التعليمة التالية:
mysql> CREATE TRIGGER uppercase_before_insert BEFORE INSERT mysql> ON collectibles mysql> FOR EACH ROW mysql> SET NEW.name = UPPER(NEW.name);
ينشئ الأمر السابق قادح باسم uppercase_before_insert، والذي سيُنفَّذ قبل كافة تعليمات INSERT في الجدول collectibles. تُنفَّذ التعليمة الموجودة في القادحSET NEW.name = UPPER(NEW.name) لكل صف مدرج، ويسند أمر SQL الذي هو SET القيمة الموجودة على الجانب الأيمن إلى الجانب الأيسر، حيث يمثل NEW.name قيمة العمود name الذي ستحفظه تعليمة الإدراج. نحوّل حالة الحروف للقيمة التي ستُحفَظ في قاعدة البيانات من خلال تطبيق الدالة UPPER على اسم الهدية وإسناده مرة أخرى لقيمة العمود.
ملاحظة: قد تظهر رسالة خطأ مشابهة للخطأ التالي عند تشغيل الأمر CREATE TRIGGER.
ERROR 1419 (HY000): You do not have the SUPER privilege, and binary logging is enabled
(you might want to use the less safe log_bin_trust_function_creators variable)
التسجيل الثنائي Binary Logging هو آلية تُسجل كل التعديلات التي تتم على قاعدة البيانات مثل إضافة أو تعديل أو حذف بيانات في سجل ثنائي، ويحتوي هذا السجل على أحداث Events تصف التعديلات التي حدثت وتحفظها بتنسيق ثنائي يمكن معالجته، وهذا التسجيل يكون مفعَّلًا افتراضيًا في محرّك قاعدة بيانات MySQL وذلك بدءًا من الإصدار MySQL 8، حيث يتعقّب التسجيل الثنائي جميع تعليمات SQL التي تعدّل محتويات قاعدة البيانات في صيغة أحداث محفوظة تَصِف هذه التعديلات، وتُستخدَم هذه السجلات في النسخ المتماثل Replication لقاعدة البيانات للحفاظ على مزامنة النسخ المتماثلة لقاعدة البيانات وأثناء استعادة البيانات في الوقت المناسب.
لكن لا يسمح MySQL بإنشاء القوادح Triggers والإجراءات المخزَّنة Stored Procedures في حال تفعيل تسجيل التعديلات أو الأحداث بصيغة ثنائية كإجراء احترازي لضمان سلامة البيانات وتكاملها في بيئات النسخ المتماثل Replication، ولكن فهم كيفية تأثير القوادح والإجراءات المُخزَّنة على النسخ المتماثل خارج نطاق هذا المقال.
يمكننا تجاوز القيود التي يفرضها MySQL عند تفعيل التسجيل الثنائي Binary Logging، وذلك لأغراض التعلم أو الاختبار في بيئة محلية على جهازنا الشخصي، ولكن لن يستمر هذا الإعداد الذي عدّلناه بالعمل وسيعود للقيمة الأصلية عند إعادة تشغيل خادم MySQL. يمكن تجاوز الإعداد الافتراضي من خلال تعديل الإعدادات الخاصة بـ MySQL كما يلي:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
يتحكم الإعداد log_bin_trust_function_creators بإمكانية الوثوق بالمستخدمين الذين ينشئون القوادح والدوال المخزنة بحيث لا ينشئون قوادح تتسبب في كتابة أحداث غير آمنة في السجل الثنائي. تكون قيمة الإعداد الافتراضية هي 0، مما يسمح للمستخدمين الذين يتمتعون بصلاحيات مميزة فقط بإنشاء قوادح في البيئة التي فعّلنا فيها تسجيل التعديلات أو الأحداث بصيغة ثنائية، وإذا عدّلنا القيمة إلى 1، فسنثق بأيّ مستخدم ينشئ تعليمات CREATE TRIGGER لفهم النتائج.
جرى الآن تحديث الإعداد، لنعمل إذًا على تسجيل الخروج كمستخدم جذر، وتسجيل الدخول مرة أخرى كمستخدم عادي، ونعيد تشغيل تعليمة CREATE TRIGGER. ويمكن الاطلاع على توثيق MySQL الرسمي: السجل الثنائي و تسجيل التعديلات أو الأحداث بصيغة ثنائية للبرنامج المخزن، كما يمكن مطالعة مقال كيفية إعداد النسخ المتماثل في MySQL لمعرفة المزيد حول تسجيل التعديلات أو الأحداث بصيغة ثنائية والنسخ المتماثل في MySQL وارتباطه بالقوادح.
ملاحظة: قد نتلقى خطأً عند تنفيذ أمر CREATE TRIGGER اعتمادًا على أذونات مستخدم MySQL الخاصة بنا
ERROR 1142 (42000): TRIGGER command denied to user 'user'@'host' for table 'collectibles'
لحل هذا الخطأ يمكن منح أذونات TRIGGER للمستخدم الخاص بنا من خلال تسجيل الدخول إلى MySQL كمستخدم جذر، وتنفيذ الأوامر التالية مع وضع اسم مستخدم MySQL والمضيف حسب الحاجة:
mysql> GRANT TRIGGER on *.* TO 'user'@'localhost'; mysql> FLUSH PRIVILEGES;
نحدّث أذونات المستخدم، ثم نسجّل الخروج كمستخدم جذر، ونسجّل الدخول مرة أخرى كمستخدم عادي، ونعيد تشغيل التعليمة CREATE TRIGGER، وسيطبع MySQL الرسالة التالية للتأكد من إنشاء القادح بنجاح:
Query OK, 1 row affected (0.009 sec)
نحاول الآن إدراج مجموعة هدايا تذكارية جديدة باستخدام وسيط بحروف صغيرة مع استعلام INSERT كما يلي:
mysql> INSERT INTO collectibles VALUES ('aircraft model', 10.00);
ثم نتحقّق من الصفوف الناتجة في الجدول collectibles كما يلي:
mysql> SELECT * FROM collectibles;
وسيظهر الخرج التالي:
+-----------------+-------+ | name | value | +-----------------+-------+ | spaceship model | 12.50 | | AIRCRAFT MODEL | 10.00 | +-----------------+-------+ 2 rows in set (0.000 sec)
يشير الإدخال الجديد هذه المرة إلى أن AIRCRAFT MODEL -مع جميع حروفه الكبيرة- يختلف عن الإدخال الذي حاولنا إدراجه، حيث شُغِّل المحفّز في الخلفية وحوّل حالة الحروف قبل حفظ الصف في قاعدة البيانات.
يحمي القادح جميع الصفوف الجديدة لضمان حفظ الأسماء بحروف كبيرة، ولكن لا يزال من الممكن حفظ البيانات غير المقيَّدة التي تستخدم تعليمات UPDATE، حيث يمكن حماية تعليمات UPDATE باستخدام التأثير نفسه، إذًا لننشئ قادحًا آخر كما يلي:
mysql> CREATE TRIGGER uppercase_before_update BEFORE UPDATE mysql> ON collectibles mysql> FOR EACH ROW mysql> SET NEW.name = UPPER(NEW.name);
يكمن الفرق بين القادحين في المعايير، فالقادح هنا BEFORE UPDATE، مما يعني تنفيذه في كل مرة تُنفَّذ فيها تعليمة UPDATE مع الجدول، ويؤثر ذلك على الصفوف الموجودة في كل تحديث، بالإضافة إلى الصفوف الجديدة التي يؤثر عليها القادح السابق. سيعطي MySQL تأكيدًا بإنشاء القادح بنجاح كما يلي:
Query OK, 0 row affected (0.009 sec)
يمكن التحقق من سلوك القادح الجديد من خلال تحديث قيمة سعر spaceship model كما يلي:
mysql> UPDATE collectibles SET value = 15.00 WHERE name = 'spaceship model';
ترشّح تعليمة WHERE الصف المراد تحديثه حسب الاسم، وتغيّر تعليمة SET القيمة إلى 15.00، وسيظهر الخرج التالي، مما يؤكد أن التعليمة قد غيّرت صفًا واحدًا:
Query OK, 1 row affected (0.002 sec) Rows matched: 1 Changed: 1 Warnings: 0
لنتحقّق الآن من الصفوف الناتجة في الجدول collectibles كما يلي:
mysql> SELECT * FROM collectibles;
وسيظهر الخرج التالي:
+-----------------+-------+ | name | value | +-----------------+-------+ | SPACESHIP MODEL | 15.00 | | AIRCRAFT MODEL | 10.00 | +-----------------+-------+ 2 rows in set (0.000 sec)
أصبح الاسم الآن SPACESHIP MODEL بالإضافة إلى تحديث السعر إلى 15.00 باستخدام التعليمة المُنفَّذة. إذا شغّلنا تعليمة UPDATE، فسيُنفَّذ القادح، مما يؤثر على القيم الموجودة في الصف المُحدَّث، مع تحويل عمود الاسم إلى حروف كبيرة قبل الحفظ.
أنشأنا في هذا القسم قادحين Triggersيعملان قبل استعلامات INSERT وقبل استعلامات UPDATE لجعل البيانات ملائمةً قبل حفظها في قاعدة البيانات، وسنستخدم في القسم التالي محفّزات BEFORE DELETE لنسخ الصفوف المحذوفة في جدول منفصل للأرشفة.
استخدام قوادح BEFORE DELETE
قد نرغب في أرشفة البيانات بدلاً من حذفها نهائيًا من قاعدة البيانات، خاصةً إذا كنا بحاجة للاحتفاظ بسجل لهذه البيانات في المستقبل، فإذ أنشأنا في بداية هذا المقال جدول آخر باسم collectibles_archive لتعقّب جميع الهدايا التذكارية المحذوفة من المجموعة وأرشفتها. سنستخدم قادح يُنفَّذ قبل تنفيذ تعليمات الحذف DELETE.
نتحقّق أولًا مما إذا كان جدول الأرشيف فارغًا بالكامل من خلال تنفيذ التعليمة التالية:
mysql> SELECT * FROM collectibles_archive;
وسيظهر الخرج التالي، مما يؤكد أن الجدول collectibles_archive فارغ:
Empty set (0.000 sec)
إذا نفّذنا استعلام DELETE مع الجدول collectibles، فيمكن حذف أيّ صفٍ من الجدول دون أيّ أثر. يمكن معالجة ذلك من خلال إنشاء قادح يُنفَّذ قبل جميع استعلامات DELETE مع الجدول collectibles، والغرض من هذا القادح هو حفظ نسخة من الكائن المحذوف في جدول الأرشيف قبل حدوث الحذف.
لنشغّل الآن الأمر التالي:
mysql> CREATE TRIGGER archive_before_delete BEFORE DELETE mysql> ON collectibles mysql> FOR EACH ROW mysql> INSERT INTO collectibles_archive (name, value) VALUES (OLD.name, OLD.value);
يُسمَّى هذا المحفّز باسم archive_before_delete ويحدث قبل أيّ استعلامات DELETE مع الجدول collectibles. ستُنفَّذ تعليمة INSERT لكل صفٍ سيُحذف، بينما تدرج تعليمة INSERT صفًا جديدًا في الجدول collectibles_archive مع قيم البيانات المأخوذة من السجل OLD، وهو السجل المقرّر حذفه، حيث يصبح OLD.name هو العمود name ويصبح OLD.value العمود value.
وتؤكد قاعدة البيانات إنشاء هذا القادح كما يلي:
Query OK, 0 row affected (0.009 sec)
نحاول حذف إحدى الهدايا التذكارية من الجدول collectibles الرئيسي مع استخدام القادح كما يلي:
mysql> DELETE FROM collectibles WHERE name = 'SPACESHIP MODEL';
ويؤكّد الخرج التالي نجاح تشغيل الاستعلام السابق:
Query OK, 1 row affected (0.004 sec)
لنسرد الآن جميع الهدايا التذكارية كما يلي:
mysql> SELECT * FROM collectibles;
وسيظهر الخرج التالي:
+----------------+-------+ | name | value | +----------------+-------+ | AIRCRAFT MODEL | 10.00 | +----------------+-------+ 1 row in set (0.000 sec)
حذفنا SPACESHIP MODEL ولم يَعُد موجودًا في الجدول مع بقاء AIRCRAFT MODEL، ولكن يجب تسجيل هذا الحذف في الجدول collectibles_archive باستخدام المحفّز الذي أنشأناه مسبقًا، إذًا لنتحقق من ذلك، ولننفّذ استعلامًا آخر كما يلي:
mysql> SELECT * FROM collectibles_archive;
وسيظهر الخرج التالي:
+-----------------+-------+---------------------+ | name | value | removed_on | +-----------------+-------+---------------------+ | SPACESHIP MODEL | 15.00 | 2022-11-20 11:32:01 | +-----------------+-------+---------------------+ 1 row in set (0.000 sec)
لاحظ القادح عملية الحذف في هذا الجدول تلقائيًا، مع تعبئة أعمدة name و value بالبيانات من الصف المحذوف، ولم يضبط القادح العمود الثالث removed_on صراحةً، لذلك سيأخذ القيمة الافتراضية المُحدَّدة أثناء إنشاء الجدول، أي تاريخ إنشاء أيّ صف جديد، مما يؤدي دائمًا إلى إضافة تعليق توضيحي لكل إدخال مُضاف بمساعدة القادح مع تاريخ الحذف.
يمكننا الآن مع وجود هذا القادح التأكد من أن جميع استعلامات DELETE ستؤدي إلى إدخال سجلٍ في الجدول collectibles_archive مع ترك معلومات حول الهدايا التذكارية المملوكة مسبقًا.
سنستخدم في القسم التالي القوادح التي تُنفَّذ بعد تعليمات تحديث الجدول الذي يحتوي على القيم المُجمَّعة بناءً على جميع الهدايا التذكارية.
استخدام محفزات AFTER INSERT و AFTER UPDATE و AFTER DELETE
استخدمنا في القسمين السابقين القوادح المُنفَّذة قبل التعليمات الرئيسية لتنفيذ العمليات المستندة إلى البيانات الأصلية قبل تحديث قاعدة البيانات، وسنحدّث في هذا القسم جدول البيانات التلخيصية بالعدد والقيمة المتراكمة لجميع الهدايا التذكارية المحدَّثة دائمًا باستخدام القوادح المُنفَّذة بعد التعليمات المطلوبة، وبذلك سنكون متأكدين من أن بيانات الجدول تأخذ في الحسبان الحالة الحالية لقاعدة البيانات.
لنبدأ الآن بفحص الجدول collectibles_stats كما يلي:
mysql> SELECT * FROM collectibles_stats;
لم نضف معلومات إلى هذا الجدول بعد، لذا يكون عدد عناصر الهدايا التذكارية المملوكة هو 0، والقيمة التراكمية هي NULL:
+-------+-------+ | count | value | +-------+-------+ | 0 | NULL | +-------+-------+ 1 row in set (0.000 sec)
لا توجد قوادح لهذا الجدول، وبالتالي لم تؤثر الاستعلامات الصادرة مسبقًا لإدراج الهدايا التذكارية وتحديثها على هذا الجدول. نريد ضبط القيم في صفٍ واحد من الجدول collectibles_stats لتقديم معلومات مُحدَّثة حول عدد الهدايا التذكارية وقيمتها الإجمالية، حيث نتأكد من تحديث محتويات الجدول بعد كل عملية إدراج INSERT أو تحديث UPDATE أو حذف DELETE من خلال إنشاء ثلاثة قوادح منفصلة وتنفيذها بعد الاستعلام المقابل لها. لننشئ أولًا القادح AFTER INSERT:
mysql> CREATE TRIGGER stats_after_insert AFTER INSERT mysql> ON collectibles mysql> FOR EACH ROW mysql> UPDATE collectibles_stats mysql> SET count = ( mysql> SELECT COUNT(name) FROM collectibles mysql> ), value = ( mysql> SELECT SUM(value) FROM collectibles mysql> );
سمّينا القادح بالاسم stats_after_insert وسيُنفَّذ بعد AFTER كل استعلام INSERT في الجدول collectibles مع تشغيل تعليمة UPDATE في جسم القادح. يؤثر استعلام UPDATE على جدول البيانات التلخيصية ويضبط العمودين count و value على القيم التي تعيدها الاستعلامات المتداخلة كما يلي:
-
SELECT COUNT(name) FROM collectibles: يحصل على عدد الهدايا التذكارية -
SELECT SUM(value) FROM collectibles: يحصل على القيمة الإجمالية لجميع الهدايا التذكارية
وتؤكّد قاعدة البيانات إنشاء القادح كما يلي:
Query OK, 0 row affected (0.009 sec)
نجرّب الآن إعادة إدراج spaceship model المحذوف مسبقًا في الجدول collectibles للتحقق من التحديث الصحيح لجدول البيانات التلخيصية كما يلي:
mysql> INSERT INTO collectibles VALUES ('spaceship model', 15.00);
وتطبع قاعدة البيانات رسالة النجاح التالية:
Query OK, 1 row affected (0.009 sec)
يمكن سرد جميع الهدايا التذكارية المملوكة باستخدام الاستعلام التالي:
mysql> SELECT * FROM collectibles;
وسيظهر الخرج التالي:
+-----------------+-------+ | name | value | +-----------------+-------+ | AIRCRAFT MODEL | 10.00 | | SPACESHIP MODEL | 15.00 | +-----------------+-------+ 2 rows in set (0.000 sec)
يوجد نوعان من عناصر الهدايا التذكارية التي تبلغ قيمتها الإجمالية 25.00. لنفحص الآن جدول البيانات التلخيصية بعد العنصر الذي أدرجناه حديثًا من خلال تنفيذ الاستعلام التالي:
mysql> SELECT * FROM collectibles_stats;
سيسرد الجدول هذه المرة عدد جميع عناصر الهدايا التذكارية المملوكة التي هي 2 وقيمتها التراكمية 25.00، والتي تطابق الخرج السابق:
+-------+-------+ | count | value | +-------+-------+ | 2 | 25.00 | +-------+-------+ 1 row in set (0.000 sec)
يُنفَّذ القادح stats_after_insert بعد استعلام INSERT ويحدّث الجدول collectibles_stats بالبيانات الحالية count و value للمجموعة، وتُجمَع إحصائيات محتويات المجموعة بأكملها، وليس الإدخال الأخير فقط. تحتوي المجموعة الآن على عنصرين spaceship model و aircraft model، لذا يسرد جدول البيانات التلخيصية عنصرين مع قيمتهما الإجمالية، وبالتالي ستؤدي إضافة أيّ عنصر هدية تذكارية جديد إلى الجدول collectibles إلى تحديث جدول البيانات التلخيصية بالقيم الصحيحة، ولكن لن يؤثر تحديث العناصر الموجودة أو حذف الهدايا التذكارية على جدول البيانات التلخيصية أبدًا، لذا سننشئ قادحين إضافيين لإجراء عمليات متطابقة ولكن تحفّزها أحداث مختلفة كما يلي:
mysql> CREATE TRIGGER stats_after_update AFTER UPDATE mysql> ON collectibles mysql> FOR EACH ROW mysql> UPDATE collectibles_stats mysql> SET count = ( mysql> SELECT COUNT(name) FROM collectibles mysql> ), value = ( mysql> SELECT SUM(value) FROM collectibles mysql> ); mysql> mysql> CREATE TRIGGER stats_after_delete AFTER DELETE mysql> ON collectibles mysql> FOR EACH ROW mysql> UPDATE collectibles_stats mysql> SET count = ( mysql> SELECT COUNT(name) FROM collectibles mysql> ), value = ( mysql> SELECT SUM(value) FROM collectibles mysql> );
أنشأنا قادحين جديدين هما: stats_after_update و stats_after_delete، وسيُنفّذان مع الجدول collectible_stats عند تنفيذ تعليمة UPDATE أو DELETE في الجدول collectibles. يؤدي الإنشاء الناجح لهذين القادحين إلى طباعة الخرج التالي:
Query OK, 0 row affected (0.009 sec)
لنحدّث الآن قيمة السعر لإحدى الهدايا التذكارية كما يلي:
mysql> UPDATE collectibles SET value = 25.00 WHERE name = 'AIRCRAFT MODEL';
ترشّح تعليمة WHERE الصف المراد تحديثه حسب الاسم، وتغيّر تعليمة SET القيمة إلى 25.00.
يؤكّد الخرج التالي أن التعليمة غيّرت صفًا واحدًا:
Query OK, 1 row affected (0.002 sec) Rows matched: 1 Changed: 1 Warnings: 0
لنتحقق مرة أخرى من محتويات جدول البيانات التلخيصية بعد التحديث كما يلي:
mysql> SELECT * FROM collectibles_stats;
وسيسرد العمود value الآن القيمة 40.00، وهي القيمة الصحيحة بعد التحديث:
+-------+-------+ | count | value | +-------+-------+ | 2 | 40.00 | +-------+-------+ 1 row in set (0.000 sec)
الخطوة الأخيرة هي التحقق من أن جدول البيانات التلخيصية سيظهِر حذف إحدى الهدايا التذكارية بطريقة صحيحة، فلنحاول حذف العنصر aircraft model كما يلي:
mysql> DELETE FROM collectibles WHERE name = 'AIRCRAFT MODEL';
ويؤكد الخرج التالي تشغيل الاستعلام بنجاح:
Query OK, 1 row affected (0.004 sec)
لنسرد الآن جميع الهدايا التذكارية كما يلي:
mysql> SELECT * FROM collectibles;
وسيظهر الخرج التالي:
+-----------------+-------+ | name | value | +-----------------+-------+ | SPACESHIP MODEL | 15.00 | +-----------------+-------+ 1 row in set (0.000 sec)
لاحظ بقاء العنصر SPACESHIP MODEL فقط. لنتحقق الآن من القيم الموجودة في جدول البيانات التلخيصية كما يلي:
mysql> SELECT * FROM collectibles_stats;
وسيظهر الخرج التالي:
+-------+-------+ | count | value | +-------+-------+ | 1 | 15.00 | +-------+-------+ 1 row in set (0.000 sec)
يعرض العمود count الآن هدية تذكارية واحدة فقط في الجدول الرئيسي، والقيمة الإجمالية هي 15.00، وهي مطابقة لقيمة العنصر SPACESHIP MODEL.
تعمل المحفّزات الثلاثة السابقة مع بعضها البعض بعد استعلامات INSERT و UPDATE و DELETE للحفاظ على مزامنة جدول الملخص مع القائمة الكاملة للهدايا التذكارية.
سنتعلم في القسم التالي كيفية معالجة القوادح الموجودة مسبقًا مع قاعدة البيانات.
سرد وحذف القوادح Triggers
أنشأنا في الأقسام السابقة قوادح جديدة، ولكن يمكنك أيضًا سردها ومعالجتها عند الحاجة لأنها كائنات مسمَّاة ومُعرَّفة في قاعدة البيانات مثل الجداول، حيث يمكنك سرد جميع القوادح من خلال تنفيذ التعليمة SHOW TRIGGERS كما يلي:
mysql> SHOW TRIGGERS;
وسيتضمن الخرج جميع القوادح مع أسمائها وحدث التحفيز مع الوقت قبل BEFORE أو بعد AFTER تنفيذ التعليمة، بالإضافة إلى التعليمات التي تشكّل جزءًا من جسم القادح والتفاصيل الشاملة الأخرى لتعريفه كما يلي:
+-------------------------+--------+--------------+--------(...)+--------+(...) | Trigger | Event | Table | Statement | Timing |(...) +-------------------------+--------+--------------+--------(...)+--------+(...) | uppercase_before_insert | INSERT | collectibles | SET (...)| BEFORE |(...) | stats_after_insert | INSERT | collectibles | UPDATE (...)| AFTER |(...) | uppercase_before_update | UPDATE | collectibles | SET (...)| BEFORE |(...) | stats_after_update | UPDATE | collectibles | UPDATE (...)| AFTER |(...) | archive_before_delete | DELETE | collectibles | INSERT (...)| BEFORE |(...) | stats_after_delete | DELETE | collectibles | UPDATE (...)| AFTER |(...) +-------------------------+--------+--------------+--------(...)+--------+(...) 6 rows in set (0.001 sec)
يمكن حذف القوادح الموجودة مسبقًا من خلال استخدام تعليمات SQL التي هي DROP TRIGGER، فمثلًا إن لم نعد نرغب بفرض الحروف الكبيرة على أسماء الهدايا التذكارية، ولم نعد بحاجة إلى القادحين uppercase_before_insert و uppercase_before_update، فيمكننا إزالتهما من خلال تنفيذ الأمرين التالية:
mysql> DROP TRIGGER uppercase_before_insert; mysql> DROP TRIGGER uppercase_before_update;
ويستجيب MySQL برسالة النجاح التالية:
Query OK, 0 rows affected (0.004 sec)
لنجرّب الآن إضافة هدية تذكارية جديدة بحروف صغيرة كما يلي:
mysql> INSERT INTO collectibles VALUES ('ship model', 10.00);
وستؤكد قاعدة البيانات ذلك كما يلي:
Query OK, 1 row affected (0.009 sec)
يمكننا التحقق من إدراج الصف من خلال تنفيذ استعلام SELECT التالي:
mysql> SELECT * FROM collectibles;
وسيظهر الخرج التالي:
+-----------------+-------+ | name | value | +-----------------+-------+ | SPACESHIP MODEL | 15.00 | | ship model | 10.00 | +-----------------+-------+ 2 rows in set (0.000 sec)
نلاحظ كتابة الهدية التذكارية المضافة حديثًا بحروف صغيرة، إذًا لم يتغيّر الاسم عن الخرج الأصلي، وبالتالي تأكّدنا من أن القادح الذي حوّل حالة الحروف سابقًا لم يَعُد قيد الاستخدام.
الخلاصة
تعلمنا في هذا المقال ما هي القوادح Triggers في SQL وكيفية استخدامها في MySQL لمعالجة البيانات قبل استعلامات INSERT و UPDATE، وتعلّمنا كيفية استخدام قادح BEFORE DELETE لأرشفة الصف المحذوف في جدول منفصل، بالإضافة إلى استخدام قوادح AFTER بعد التعليمات لإبقاء جدول البيانات التلخيصية مُحدَّثة باستمرار.
يمكننا استخدام الدوال لتفريغ بعض عمليات معالجة البيانات والتحقق من صحتها في محرّك قاعدة البيانات، مما يضمن سلامة البيانات أو إخفاء بعض سلوكيات قاعدة البيانات عن المستخدم الذي يستخدم قاعدة البيانات يوميًا، وقد غطّى هذا المقال أساسيات استخدام القوادح لهذا الغرض فقط، ولكن يمكنك أيضًا إنشاء قوادح معقدة تتكوّن من تعليمات متعددة واستخدام المنطق الشرطي لتنفيذ الإجراءات بدقة أكبر. ويمكن مطالعة توثيق MySQL الخاص بالتعامل مع triggers لمزيد من المعلومات.
ترجمة -وبتصرف- للمقال How To Use Triggers in MySQL لصاحبَيه Mateusz Papiernik و Rachel Lee.
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.