coderkid coderkid - 6 months ago 72
MySQL Question

MariaDB sql trigger to update if key exists or insert if doesn't exist?

I'm new to SQL, but trying to build a simple trigger that will execute on each insertion. I want it to update some table values if the key already exists, or insert it with some new values if it doesn't exist.

Below is what I have attempted, but I would ideally want tester to be the thing that is being inserted. Something like NEW.reference_key?

CREATE TRIGGER key_access_monitor
BEFORE INSERT ON individual_key_log
FOR EACH ROW
BEGIN
IF EXISTS (SELECT reference_key FROM individual_key_log WHERE key = 'tester')
SELECT 'Found it!'
ELSE
SELECT 'Cannot find it!'
END IF
END

Answer

You want to insert a new row if a row with the same key does not already exist, and update the existing row if it does exist, is that correct?

You'd probably find it easier to use INSERT ... ON DUPLICATE KEY UPDATE, as follows:

INSERT INTO individual_key_log
    (key, col1, col2, col3, ...)
  VALUES
    ('tester', 'val1', 'val2', 'val3', ...)
  ON DUPLICATE KEY UPDATE
    SET col1 = 'val1', col2 = 'val2', col3 = 'val3', ...;

One of the problems with your trigger approach is that it is bad practice to attempt to query a table from within a row trigger on that same table, as it violates the ACID principle; many DBMSes do not permit it at all, raising a "Mutating Table" error. Updating a row from within an insert row trigger has the same problem magnified. A row trigger should never try to access its target table directly, instead accessing only the triggering row indirectly through OLD and NEW.

Hope that helps.