How to structure IF condition in MySQL trigger?

Question

I am trying to write a MySQL trigger. I have two tables like this:

Table A------------------------------Table B
order_id--------sku---------------order_id----order_#----sku_copy
 568---------AAA---------------568---------2345
 567---------BBB---------------567---------6789-------empty column
 566---------CCC---------------566---------1234

When a customer makes a purchase a new record is added to each table. I have added column ‘sku_copy’ to Table B, so it does not get populated when a new record is created.

When a new record is created, I want my trigger to copy the ‘sku’ field in Table A to the ‘sku_copy’ field in Table B. However, the problem I am having is how to structure the following condition in the trigger.

IF: ‘order_id’ in Table A matches ‘order_id’ in Table B. THEN: copy ‘sku’ from that Table A record to the record in Table B with the matching ‘order_id’. The data should be added to Table B ‘sku_copy'.

I am using the following SQL trigger but it gives this error when it's run:

"#1363 - There is no OLD row in on INSERT trigger"

Here is the trigger:

DELIMITER $$
CREATE TRIGGER trigger_name
 AFTER INSERT ON tableA
 FOR EACH ROW BEGIN
 INSERT INTO tableB
 SET sku_copy = OLD.sku,
 order_id = OLD.order_id,
 order = OLD.order;
END $$
DELIMITER ;

Can some one show me how to correct the error in this code or suggest a better one?

Thank you for any help you can give.

Here is an update:

I tried this trigger (this is the live data instead of simplified as in the above examples) but get an error code:

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE virtuemart_order_id=new.virtuemart_order_id; END IF; END' at line 7"

Here is that trigger:

DELIMITER $$
CREATE TRIGGER `sku_after_update` AFTER UPDATE ON `uau3h_virtuemart_order_items` 
 FOR EACH ROW
 BEGIN
 IF (old.order_item_sku_copy != new.order_item_sku)
 THEN
 UPDATE uau3h_virtuemart_orders
 SET order_item_sku_copy=new.order_item_sku, 
 WHERE virtuemart_order_id=new.virtuemart_order_id;
 END IF;
 END$$
DELIMITER ;

Does anyone have any suggestions on how to make this trigger work?

• András Váczi: There is no OLD record when inserting something new. Have you tried NEW instead?

Answer 1

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:

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 TableA_AfterInsert
 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');
GO
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
GO
CREATE TRIGGER TableA_AfterUpdate
 AFTER UPDATE ON TableA
 FOR EACH ROW BEGIN
 IF (OLD.sku != NEW.sku)
 THEN
 UPDATE TableB
 SET sku_copy = NEW.sku
 WHERE order_id = NEW.order_id;
 END IF;
END;
GO
UPDATE TableA
 SET sku = 'NEW'
WHERE order_id = 2;
GO

Query 1:

SELECT * FROM TableB;

Results :

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

In both cases the NEW and OLD virtual tables refer to the table the trigger is defined on. NEW contains the new version of the row that was inserted or changed. OLD contains the pre-change version of the row. OLD is only defined in an update trigger as there is no old version on an insert.

• tk748: Hello Sebastian - Thank you very much for responding. It will take me until tomorrow to digest and apply your post, as it is late here. I will post my results here. Again, thank you for sharing your knowledge.
• tk748: Hi Sebastian - Useing your examples I have successfully created the trigger. Here is the version that is working:
• tk748: DELIMITER $$ CREATE TRIGGER sku_AfterInsert AFTER INSERT ON uau3h_virtuemart_order_items FOR EACH ROW BEGIN UPDATE uau3h_virtuemart_orders SET order_item_sku_copy = NEW.order_item_sku WHERE virtuemart_order_id = NEW.virtuemart_order_id; END$$; DELIMITER
• Sebastian Meine: Great news. Glad I could help. Would you mind marking the question as solved then? (meta.stackexchange.com/questions/5234/…)
• tk748: Thank you for taking the time to answer my question. I want to say this forum is much more user friendly than some others I have tried. Instead of asking me why I would want to do something like this, you gave me the examples that allowed me to construct the trigger. I learned much from this post. Muchas gracias amigo!