Bowen Liu Bowen Liu - 23 days ago 6
MySQL Question

MySql insert trigger outputs NULL

I want to have an insert trigger to achieve incremental inserts. I have one column "row_hash" to record the MD5 of each row(here I use MD5 of column

name
and
category
). If the new record has the same hash value, the trigger should skip insert, otherwise it allows the insert.

DROP TRIGGER IF EXISTS test_trigger;
DELIMITER $$
CREATE TRIGGER test_trigger BEFORE INSERT ON test_table FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) FROM test_table WHERE row_hash = MD5(CONCAT_WS('', new.name, new.category))) < 1 THEN
INSERT INTO test_table(_id, name, category, row_hash) VALUES (new._id, new.name, new.category, MD5(CONCAT_WS('', new.name, new.category)));
END IF;
END$$


Here is the test table:

create table test_table(_id varchar(10) primary key, name varchar(10), category varchar(2), row_hash char(32));


When I insert one record, it outputs NULL on the
row_hash
column.

insert into test_table(_id, name, category) values('12344', 'dafas', 'A');

'12344','dafas','A',NULL


Can you tell me where I did it wrong on the trigger?

Answer

If you want to modify the row being inserted, you don't need to use an INSERT to the same table. Just SET NEW.row_hash values.

If you want to abort an insert, you'll have to use a SIGNAL.

But overall, it appears that what you're doing shouldn't require a conditional INSERT in the trigger at all. Just a UNIQUE constraint on name, category. In the trigger, just make sure the row_hash is set correctly, then the constraint will take care of aborting if there's a duplicate.

edit: Changed the unique key to use only the row_hash column, as you suggested.

ALTER TABLE test_table ADD UNIQUE KEY (row_hash);

CREATE TRIGGER test_trigger BEFORE INSERT ON test_table
FOR EACH ROW SET NEW.row_hash = MD5(CONCAT_WS('', name, category));