创建一个mysql触发器，以在列更新时插入数据

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

Answer 1

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.