Create MySQL Trigger via PHP

Unable to create trigger in PHP for mySQL

Apparently the real problem was the fact that I was not supposed to use the PDO::prepare statement, if anyone else is having this issue use PDO::exec.

Create a mysql trigger to insert data when a column updated

I got this to work. I am out of creative commentary at the moment.

SCHEMA:

create table itm_master
(   id int auto_increment primary key,
    transfer_status VARCHAR(100) NOT NULL,
    user VARCHAR(100) NOT NULL,
    item_serial VARCHAR(100) NOT NULL,
    master_item_model VARCHAR(100) NOT NULL,
    master_item_type VARCHAR(100) NOT NULL,
    item_status VARCHAR(100) NOT NULL
);
create table activity_tbl
(   id int auto_increment primary key,
    `evnt_date` DATETIME NOT NULL,
    `con_type` VARCHAR(100) NOT NULL,
    `username` VARCHAR(100) NOT NULL,
    `item_serial` VARCHAR(100) NOT NULL,
    `item_model` VARCHAR(100) NOT NULL,
    `item_type` VARCHAR(100) NOT NULL,
    `to_status` VARCHAR(100) NOT NULL
);

TRIGGER:

DROP TRIGGER IF EXISTS update_2;
DELIMITER $$
CREATE TRIGGER `update_2` 
AFTER UPDATE ON `itm_master`
FOR EACH ROW 
BEGIN
    IF NEW.transfer_status='YES' THEN
        INSERT into activity_tbl (`evnt_date`,`con_type`,`username`,`item_serial`,`item_model`,`item_type`,`to_status`)
        VALUES (now(),'Update',NEW.user,NEW.item_serial,NEW.master_item_model,NEW.master_item_type,NEW.item_status);
    END IF;
END;$$
DELIMITER ;

Test:

INSERT itm_master(transfer_status, user, item_serial, master_item_model, master_item_type, item_status) values
('a','b','c','d','e','f');

UPDATE itm_master SET transfer_status='blah' WHERE id=1;
select * from activity_tbl;
-- no rows

UPDATE itm_master SET transfer_status='YES' WHERE id=1;
select * from activity_tbl;
+----+---------------------+----------+----------+-------------+------------+-----------+-----------+
| id | evnt_date           | con_type | username | item_serial | item_model | item_type | to_status |
+----+---------------------+----------+----------+-------------+------------+-----------+-----------+
|  1 | 2016-09-13 00:14:26 | Update   | b        | c           | d          | e         | f         |
+----+---------------------+----------+----------+-------------+------------+-----------+-----------+

The DELIMITER wrapper is not needed for PHPMyAdmin.

Invoking a PHP script from a MySQL trigger

The trigger is executed on the MySQL server, not on the PHP one (even if those are both on the same machine).

So, I would say this is not quite possible -- at least not simply.

Still, considering this entry from the MySQL FAQ on Triggers :

23.5.11: Can triggers call an external application through a UDF?

Yes. For example, a trigger could
invoke the sys_exec() UDF available here:
https://github.com/mysqludf/lib_mysqludf_sys#readme

So, there might be a way via an UDF function that would launch the php executable/script. Not that easy, but seems possible. ;-)

Creating a MYSQL Trigger using PDO / PHP

(Adding answer for the sake of completeness, from Michael's comment).

PDO does not support multiple queries in the prepared statement. You are executing a DROP followed by a CREATE. Separate the actions. Call the DROP with $dbLink->exec("DROP...") first, then prepare the CREATE. Technically since you have no bound parameters, you could use exec() for the CREATE as well.

---

Related Topics