Add mysql trigger with PHP PDO

Here is my trigger:

DELIMITER //
CREATE TRIGGER vf_update_checksum AFTER INSERT ON `vf_params` FOR EACH ROW BEGIN
   SET @last_param_id = LAST_INSERT_ID();
   SET @object_id = (SELECT p.object_id
                                FROM vf_params as p
                            WHERE p.id = @last_param_id
                            LIMIT 1);

   SET @object_type = (SELECT ot.name
                                    FROM vf_objects as o
                            LEFT JOIN vf_objectTypes as ot ON o.object_type_id = ot.object_type_id
                                WHERE o.object_id = @object_id
                                    LIMIT 1);

   SET @params = (SELECT GROUP_CONCAT(COALESCE(CAST(p.int_value AS CHAR), CAST(p.float_value AS CHAR), p.text_value, p.array_value, CAST(p.reference_value AS CHAR)) SEPARATOR '') AS VALUE 
                    FROM vf_params as p
                        WHERE p.object_id = @object_id
                            GROUP BY 'all'
                                LIMIT 1);

   SET @object_heap = CONCAT(@object_type, @params);
   SET @chksm = MD5(@object_heap);

   UPDATE vf_objects as o SET o.checksum = @chksm WHERE o.object_id = @object_id;
END
//
DELIMITER ;

And I'm trying to make this code rigorous and execute it with help PDO, but it cannot create a trigger. I think the problem is DELIMITER //, but it cannot be created without it (or can it be?). I also tried $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);and $pdo->exec()... the same thing.

How to create such a trigger with PDO??

+4
source share

Source: https://habr.com/ru/post/1545507/


All Articles