I am trying to create a simple trigger which is handled on update operations. The trigger is checking before update if a record of an update exists, then if not it is created.
DELIMITER ;;
CREATE TRIGGER trig
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
IF old is null then
INSERT INTO table(table.col1, table.col2) VALUES ('new.table.col1','new.table.col2');
END IF;
END;
;;
DELIMITER ;
Why is it not adding a record if update is trying to update some non existing record?
ON DUPLICATE KEY UPDATE
is usually better thanREPLACE
, especially if you have foreign key constraints:INSERT IGNORE
vsINSERT ... ON DUPLICATE KEY UPDATE
vsREPLACE