الدوال والمشغلات (Functions & Triggers)

DELIMITER // -- دالة بسيطة تُرجع قيمة محسوبة CREATE FUNCTION CalculateTax(amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN DECLARE tax_rate DECIMAL(5,4) DEFAULT 0.0825; RETURN amount * tax_rate; END // DELIMITER ; -- استخدام الدالة في الاستعلامات SELECT order_id, total_amount, CalculateTax(total_amount) AS tax, total_amount + CalculateTax(total_amount) AS total_with_tax FROM orders;

DELIMITER // -- DETERMINISTIC: نفس المدخل يعطي دائماً نفس المخرج CREATE FUNCTION GetDiscount(total_spent DECIMAL(10,2)) RETURNS DECIMAL(5,2) DETERMINISTIC BEGIN RETURN CASE WHEN total_spent >= 10000 THEN 20.00 WHEN total_spent >= 5000 THEN 15.00 WHEN total_spent >= 1000 THEN 10.00 ELSE 0.00 END; END // -- NOT DETERMINISTIC: قد يختلف المخرج (يستخدم NOW() أو RAND() وما إلى ذلك) CREATE FUNCTION GetCurrentYear() RETURNS INT NOT DETERMINISTIC READS SQL DATA BEGIN RETURN YEAR(NOW()); END // -- NO SQL: الدالة لا تحتوي على عبارات SQL -- READS SQL DATA: الدالة تقرأ البيانات لكن لا تعدلها -- MODIFIES SQL DATA: الدالة تعدل البيانات (استخدم بحذر!) DELIMITER ;

DELIMITER // -- دالة لحساب فئة ولاء العميل CREATE FUNCTION GetCustomerTier(customer_id_param INT) RETURNS VARCHAR(20) READS SQL DATA BEGIN DECLARE total_spent DECIMAL(10,2); DECLARE order_count INT; DECLARE tier VARCHAR(20); SELECT COALESCE(SUM(total_amount), 0), COUNT(*) INTO total_spent, order_count FROM orders WHERE customer_id = customer_id_param; IF total_spent > 10000 OR order_count > 20 THEN SET tier = 'Platinum'; ELSEIF total_spent > 5000 OR order_count > 10 THEN SET tier = 'Gold'; ELSEIF total_spent > 1000 OR order_count > 5 THEN SET tier = 'Silver'; ELSE SET tier = 'Bronze'; END IF; RETURN tier; END // -- حساب تشابه السلسلة (Levenshtein distance مبسط) CREATE FUNCTION StringSimilarity(str1 VARCHAR(255), str2 VARCHAR(255)) RETURNS INT DETERMINISTIC BEGIN DECLARE len1 INT DEFAULT LENGTH(str1); DECLARE len2 INT DEFAULT LENGTH(str2); IF str1 = str2 THEN RETURN 100; ELSEIF len1 = 0 OR len2 = 0 THEN RETURN 0; END IF; -- تشابه مبسط بناءً على الأحرف المشتركة RETURN ROUND( (LENGTH(str1) + LENGTH(str2) - LENGTH(REPLACE(CONCAT(str1, str2), SUBSTRING(str1, 1, 1), ''))) * 100 / (LENGTH(str1) + LENGTH(str2)) ); END // DELIMITER ; -- استخدام الدوال في الاستعلامات SELECT customer_id, customer_name, GetCustomerTier(customer_id) AS tier, GetDiscount(total_spent) AS discount_rate FROM customers ORDER BY total_spent DESC;

الدوال: ✓ يجب إرجاع قيمة واحدة ✓ يمكن استخدامها في SELECT و WHERE و HAVING ✓ لا يمكنها تعديل البيانات (عادةً) ✓ لا يمكنها استخدام معاملات OUT/INOUT ✓ يمكن أن تكون جزءاً من التعبيرات الإجراءات: ✓ قد تُرجع أو لا تُرجع قيماً ✓ لا يمكن استخدامها في SELECT مباشرةً ✓ يمكنها تعديل البيانات ✓ يمكنها استخدام معاملات OUT/INOUT ✓ يتم استدعاؤها بعبارة CALL

DELIMITER // -- مشغل BEFORE INSERT: يتحقق من البيانات قبل الإدراج CREATE TRIGGER validate_product_price BEFORE INSERT ON products FOR EACH ROW BEGIN IF NEW.price < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'لا يمكن أن يكون السعر سالباً'; END IF; IF NEW.stock_quantity < 0 THEN SET NEW.stock_quantity = 0; END IF; END // -- مشغل AFTER INSERT: ينفذ إجراء بعد الإدراج CREATE TRIGGER log_new_customer AFTER INSERT ON customers FOR EACH ROW BEGIN INSERT INTO audit_log (table_name, action, record_id, created_at) VALUES ('customers', 'INSERT', NEW.customer_id, NOW()); END // DELIMITER ;

مشغلات BEFORE: - تُنفذ قبل العملية - يمكنها تعديل قيم NEW - يمكنها منع العملية باستخدام SIGNAL - تُستخدم للتحقق وتعديل البيانات مشغلات AFTER: - تُنفذ بعد العملية - لا يمكنها تعديل قيم NEW - لا يمكنها منع العملية - تُستخدم للتسجيل والتغييرات المتسلسلة

DELIMITER // -- تتبع التغييرات في البيانات الحساسة CREATE TRIGGER track_price_changes BEFORE UPDATE ON products FOR EACH ROW BEGIN -- سجل فقط إذا تغير السعر فعلياً IF NEW.price != OLD.price THEN INSERT INTO price_history ( product_id, old_price, new_price, changed_by, changed_at ) VALUES ( OLD.product_id, OLD.price, NEW.price, USER(), NOW() ); END IF; END // -- تحديث الطوابع الزمنية تلقائياً CREATE TRIGGER update_product_timestamp BEFORE UPDATE ON products FOR EACH ROW BEGIN SET NEW.updated_at = NOW(); END // -- منع التغييرات غير المصرح بها CREATE TRIGGER protect_archived_orders BEFORE UPDATE ON orders FOR EACH ROW BEGIN IF OLD.status = 'archived' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'لا يمكن تعديل الطلبات المؤرشفة'; END IF; END // DELIMITER ;

DELIMITER // -- تنفيذ الحذف الناعم CREATE TRIGGER soft_delete_customer BEFORE DELETE ON customers FOR EACH ROW BEGIN -- بدلاً من الحذف، وضع علامة كغير نشط INSERT INTO deleted_customers SELECT *, NOW() AS deleted_at FROM customers WHERE customer_id = OLD.customer_id; -- منع الحذف الفعلي SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'استخدم الحذف الناعم بدلاً من ذلك'; END // -- حذف متسلسل مع التسجيل CREATE TRIGGER log_order_deletion BEFORE DELETE ON orders FOR EACH ROW BEGIN -- تسجيل الحذف INSERT INTO audit_log ( table_name, action, record_id, old_data, created_at ) VALUES ( 'orders', 'DELETE', OLD.order_id, JSON_OBJECT( 'customer_id', OLD.customer_id, 'total_amount', OLD.total_amount, 'status', OLD.status ), NOW() ); -- حذف عناصر الطلب ذات الصلة DELETE FROM order_items WHERE order_id = OLD.order_id; END // DELIMITER ;

-- إنشاء جدول سجل تدقيق شامل CREATE TABLE audit_trail ( audit_id INT AUTO_INCREMENT PRIMARY KEY, table_name VARCHAR(64), operation VARCHAR(10), record_id INT, old_values JSON, new_values JSON, changed_by VARCHAR(255), changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); DELIMITER // -- مشغل تدقيق عام لـ INSERT CREATE TRIGGER audit_product_insert AFTER INSERT ON products FOR EACH ROW BEGIN INSERT INTO audit_trail ( table_name, operation, record_id, new_values, changed_by ) VALUES ( 'products', 'INSERT', NEW.product_id, JSON_OBJECT( 'product_name', NEW.product_name, 'price', NEW.price, 'stock_quantity', NEW.stock_quantity ), USER() ); END // -- مشغل تدقيق عام لـ UPDATE CREATE TRIGGER audit_product_update AFTER UPDATE ON products FOR EACH ROW BEGIN INSERT INTO audit_trail ( table_name, operation, record_id, old_values, new_values, changed_by ) VALUES ( 'products', 'UPDATE', NEW.product_id, JSON_OBJECT( 'product_name', OLD.product_name, 'price', OLD.price, 'stock_quantity', OLD.stock_quantity ), JSON_OBJECT( 'product_name', NEW.product_name, 'price', NEW.price, 'stock_quantity', NEW.stock_quantity ), USER() ); END // -- مشغل تدقيق عام لـ DELETE CREATE TRIGGER audit_product_delete BEFORE DELETE ON products FOR EACH ROW BEGIN INSERT INTO audit_trail ( table_name, operation, record_id, old_values, changed_by ) VALUES ( 'products', 'DELETE', OLD.product_id, JSON_OBJECT( 'product_name', OLD.product_name, 'price', OLD.price, 'stock_quantity', OLD.stock_quantity ), USER() ); END // DELIMITER ;

DELIMITER // -- تحديث إجماليات الطلبات تلقائياً CREATE TRIGGER calculate_order_total AFTER INSERT ON order_items FOR EACH ROW BEGIN UPDATE orders SET total_amount = ( SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = NEW.order_id ) WHERE order_id = NEW.order_id; END // -- تحديث إحصائيات العميل CREATE TRIGGER update_customer_stats AFTER INSERT ON orders FOR EACH ROW BEGIN UPDATE customers SET total_orders = total_orders + 1, total_spent = total_spent + NEW.total_amount, last_order_date = NEW.order_date WHERE customer_id = NEW.customer_id; END // DELIMITER ;

-- إظهار جميع المشغلات في قاعدة البيانات الحالية SHOW TRIGGERS; -- إظهار المشغلات لجدول محدد SHOW TRIGGERS WHERE `Table` = 'products'; -- إظهار تعريف المشغل SHOW CREATE TRIGGER validate_product_price; -- حذف مشغل DROP TRIGGER IF EXISTS validate_product_price; -- الاستعلام عن information_schema للحصول على تفاصيل المشغل SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING, ACTION_STATEMENT FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_database' ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING;

✓ اجعل المشغلات بسيطة وسريعة ✓ تجنب المنطق المعقد في المشغلات ✓ لا تنشئ مشغلات تستدعي مشغلات (تكرارية) ✓ استخدم المشغلات لسلامة البيانات، وليس لمنطق الأعمال ✓ وثق جميع المشغلات بشكل كامل ✓ اختبر المشغلات بشكل مكثف ✓ ضع في اعتبارك تأثير الأداء ✓ استخدم مشغلات BEFORE للتحقق ✓ استخدم مشغلات AFTER للتسجيل ✗ لا تضع كل منطق الأعمال في المشغلات ✗ تجنب المشغلات التي تعدل جداول متعددة ✗ لا تستخدم المشغلات كبديل لمنطق التطبيق

DELIMITER // -- دالة للتحقق من توفر المخزون CREATE FUNCTION CheckStockAvailable( p_product_id INT, p_quantity INT ) RETURNS BOOLEAN READS SQL DATA BEGIN DECLARE available INT; SELECT stock_quantity INTO available FROM products WHERE product_id = p_product_id; RETURN available >= p_quantity; END // -- مشغل للتحقق وتحديث المخزون CREATE TRIGGER validate_order_item BEFORE INSERT ON order_items FOR EACH ROW BEGIN IF NOT CheckStockAvailable(NEW.product_id, NEW.quantity) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'مخزون غير كافٍ لهذا المنتج'; END IF; -- حجز المخزون UPDATE products SET stock_quantity = stock_quantity - NEW.quantity WHERE product_id = NEW.product_id; -- تسجيل تغيير المخزون INSERT INTO inventory_log ( product_id, change_type, quantity_change, reason, created_at ) VALUES ( NEW.product_id, 'SALE', -NEW.quantity, CONCAT('Order item #', NEW.order_item_id), NOW() ); END // DELIMITER ;