MySQL trigger to update a key if record exists

Question

I'm trying to build a trigger in MariaDB to follow this logic:

1. If the primary key val exists, increment the counter column
2. Else, insert the primary key into the table

Here is my prosed trigger:

delimiter //
CREATE TRIGGER volume_manager BEFORE INSERT ON individual_key_log
    FOR EACH ROW
    BEGIN
        IF NEW.reference_key not in (
            select *
            From individual_key_log
            where (NEW.reference_key = 'test')
        ) THEN -- MISSING THEN
           CALL `Insert not allowed`;
        END IF;
    END;
//
delimiter ; 

Answer 1

Why write a trigger for this ? I have two reasons why you should not do that in this instance.

REASON #1

Let's assume the following:

• reference_key
  • is not an auto_increment column
  • is the primary key
• counter
  • is the column to increment
  • is defined as INT NOT NULL DEFAULT 1

You can write the INSERT as follows

INSERT INTO individual_key_log (reference_key,...)
VALUES (...) ON DUPLICATE KEY UPDATE counter = counter + 1;

This is a much cleaner approach than a trigger

REASON #2

I noticed you have a CALL in the middle of your trigger. Believe me, you do not want to make a trigger that breaks midstream unless it is necessary. Calling anything in a trigger requires overhead and can hamper query performance. I wrote about this back on Jan 13, 2012 : Call a stored procedure from a trigger

I also wrote about how jerryrigging a trigger can halt normal operation if not done properly : See my post from Apr 25, 2011 : Trigger in MySQL to prevent insertion where I demonstrate how to halt a trigger to prevent insertion checking a separate table.

Answer 2

You have to use iNSERT ... ON DUPLICATE KEY semantic:

INSERT INTO individual_key_log (reference_key,...) 
       VALUES (...)
    ON DUPLICATE KEY UPDATE counter=counter+1;