Thesql trigger on update with insert statement

MySQLtrigger

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?

Best Answer

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

Related Solutions

MySQL using trigger instead of check

You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

Trigger in MySQL to prevent insertion (Apr 25, 2011)
check constraint does not work? (Dec 23, 2011)

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply` 
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN          
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$

Mysql – How to structure IF condition in MySQL trigger

I assume you will value TableB first as it contains the order_no. In that case you need to use an update statement in your trigger instead of an insert statement:

SQL Fiddle

MySQL 5.6.6 m9 Schema Setup:

CREATE TABLE TableA(order_id INT, sku VARCHAR(10));

CREATE TABLE TableB(order_id INT, order_no VARCHAR(10),sku_copy VARCHAR(10));

GO
CREATE TRIGGER trigger_name
    AFTER INSERT ON TableA
    FOR EACH ROW BEGIN

    UPDATE TableB
    SET sku_copy = NEW.sku
    WHERE order_id = NEW.order_id;
END;
GO
INSERT INTO TableB(order_id, order_no)VALUES(1,'111');
INSERT INTO TableB(order_id, order_no)VALUES(2,'222');
INSERT INTO TableB(order_id, order_no)VALUES(3,'333');
GO
INSERT INTO TableA(order_id, sku)VALUES(1,'AAA'),(2,'BBB');

(The GO in this example is used as a batch separator and not send to MySQL.)

Query 1:

SELECT * FROM TableB;

Results:

| ORDER_ID | ORDER_NO | SKU_COPY |
----------------------------------
|        1 |      111 |      AAA |
|        2 |      222 |      BBB |
|        3 |      333 |   (null) |

If you also want to react to updates, just create an AFTER UPDATE trigger like this: