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 ; 

• ypercubeᵀᴹ: What columns does the table have? The output of SHOW CREATE TABLE individual_key_log; would help. Which is the "counter" column? Do you want to insert or not if the key does not exist in the table? (that "insert not allowed" is confusing)
• Rick James: Show us the desired SQL without using a TRIGGER.

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;