MySQL trigger to update a key if record exists 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 ;
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.