Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Read this answer about why ON DUPLICATE KEY UPDATE is usually better than REPLACE, especially if you have foreign key constraints: INSERT IGNORE vs INSERT ... ON DUPLICATE KEY UPDATE vs REPLACE –  ypercube Mar 30 '13 at 11:06
add comment

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

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

2 Answers

up vote 0 down vote accepted

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
share|improve this answer
    
Yees thats exactly what i needed. That works great, Thanks! –  user2226087 Mar 30 '13 at 12:30
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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