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?
Best Answer
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:
(The
GO
in this example is used as a batch separator and not send to MySQL.)Query 1:
Results:
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:
Query 1:
Results:
In both cases the
NEW
andOLD
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.