1

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 ; 
2
  • 1
    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) May 27, 2016 at 16:17
  • Show us the desired SQL without using a TRIGGER.
    – Rick James
    Jun 4, 2016 at 21:18

2 Answers 2

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.

0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.