Create a mysql trigger to insert data when a column updated

Question

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

I'd like to create a trigger to insert data into activity_tbl whenever the transfer_status field updated in item_master table. I use this query but i am receiving Mysql Error

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);

MySQL said: Documentation

1064 - Erreur de syntaxe prÃ¨s de '' Ã la ligne 7

Personally I try to avoid triggers, when you look at the code it will seem like magic that this data is added. It's more readable to insert the data when the other data is inserted. Besides that I cant read that error message... — ArtisticPhoenix
Which is your code, the first block or the second? I can see that the 2nd would have a syntax error as it's incomplete (no END IF, no END for the BEGIN). Is that what the error says in English? — Robert

Drew Drew · Accepted Answer · 2016-09-13T04:16:14

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.

Create a mysql trigger to insert data when a column updated

1064 - Erreur de syntaxe prÃ¨s de '' Ã la ligne 7

1 Answers