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.
The first problem is that you don't have an END IF;
.
The next problem (assuming that you aren't doing this and just didn't include it in the question) is that when you have a compound statement, you have to change the statement delimiter from ;
to something else so that the client will send the entire CREATE TRIGGER
statement to the server as a single statement.
Finally, the column "first_name" probably needs to be qualified with NEW
(assume you're referencing a column in wp_usermeta called "first_name".
DELIMITER $$
DROP TRIGGER IF EXISTS update_customers $$
CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta
FOR EACH ROW
BEGIN
IF NEW.wp_metakey = NEW.first_name THEN
INSERT INTO test_customer(first_name) values (NEW.wp_meta_value);
END IF;
END $$
DELIMITER ;
Best Answer
This is very straightforward.
There is no
==
operator.Please change
to
UPDATE 2018-02-26 11:04 EST
You need to change the delimiter before and after the trigger:
Please see my old post Unique combination key MySQL for an example