0

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?

migrated from stackoverflow.com Mar 30 '13 at 11:01

This question came from our site for professional and enthusiast programmers.

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
  • Yees thats exactly what i needed. That works great, Thanks! – user2226087 Mar 30 '13 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.

  • 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 '13 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 – user2226087 Mar 30 '13 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. – ypercubeᵀᴹ Mar 30 '13 at 11:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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