MySql insert trigger outputs NULL

Question

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?

Possible duplicate of My MySQL trigger doesn't work, simple sintax, not complicated — e4c5
– e4c5, Commented Nov 13, 2016 at 5:50
@e4c5, that is not a duplicate of this question. They have one thing in common—MySQL triggers—but the problem described is different. — Bill Karwin
– Bill Karwin, Commented Nov 13, 2016 at 16:46

Bill Karwin · Accepted Answer · 2016-11-13 16:51:43Z

0

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

edited Nov 13, 2016 at 16:51

answered Nov 13, 2016 at 6:15

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bowen Liu Over a year ago

Good point! I followed your idea to add a unique key on row_hash because I have many columns to check in real cases. Here is the minor change on your concat_ws with new.name and new.category. Big thanks!

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

Bill Karwin Over a year ago

Yep, that's it! You should make a trigger run BEFORE UPDATE too, to do the same thing in case one of the columns in the hash-key set of columns changes.

Bill Karwin Over a year ago

One nice thing about simpler triggers is that you don't need to worry about DELIMITER if your trigger body is a single statement. :-)

Collectives™ on Stack Overflow

MySql insert trigger outputs NULL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related