1

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?

1

2 Answers 2

1

I think that you don't need a trigger at all and - based on your comments - you want an INSERT ... ON DUPLICATE KEY UPDATE. If there is a UNIQUE constraint on (col2), this will work:

INSERT INTO table_name
  (col1, col2) 
VALUES 
  (@col1, @col2)                  -- the new values here
ON DUPLICATE KEY UPDATE
  col1 = col1 + VALUES(col1) ;    -- update the old value by adding the new one
1
  • Yees thats exactly what i needed. That works great, Thanks! Mar 30, 2013 at 12:30
0

I think what you are looking for is REPLACE.

Try this:

REPLACE INTO table(table.col1, table.col2) VALUES (value1, value2);

No need for a trigger at all.

3
  • Big thanks for u. Solved. If someone haves similar problem and it seems for him that solution not works, that means u dont use query properly. Replace use primary keys if u r not using primary key in query it works like insert statement.
    – user2226087
    Mar 30, 2013 at 10:36
  • :( nooo it is not working because the replace ain suport the incrementation. I need to use it like: REPLACE INTO table(table.col1, table.col2) VALUES (OLD.Value=OLD.value+value1, value2); but the replace dont see the OLD.value Mar 30, 2013 at 11:14
  • @user2226087 I suggest you edit your question and add the structure of the table (the output of SHOW CREATE TABLE table_name) and an example of what you want to happen when there is a duplicate. Mar 30, 2013 at 11:17

Your Answer

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

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