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.
A couple of possibilities, not sure if any of them will help you
Force recovery
[mysqld]
innodb_force_recovery = 4
If you run Force Recovery, your database will still be in an incoherent state. This will however allow you to make a backup of the data that you do have access to.
Usually, most of the data obtained in this way is intact. Serious
corruption might cause SELECT * FROM tbl_name statements or InnoDB
background operations to crash or assert, or even cause InnoDB
roll-forward recovery to crash. In such cases, use the
innodb_force_recovery option to force the InnoDB storage engine to
start up while preventing background operations from running, so that
you can dump your tables
Force InnoDB recovery
Permissions
Make sure the MySQL service user has full permissions on C:\wamp\bin\mysql\mysql5.6.12\data
The service that runs MySQL needs full control for the directory where you are storing your data files
Update
FS error
Problems reading from the C:\wamp\bin\mysql\mysql5.6.12\data directory ?
If you go to Control Panels-> Administrative Tools-> Events Viewer, then Windows Logs -> System, you might find more information about disk issues
One possible solution, would be to copy your data directory to another drive and/or directory.
Stop MySQL
Copy the data directory contents to the new drive and/or directory.
Open the C:\wamp\bin\mysql\mysql5.6.12\my.cnf file and change the datadir option under mysqld
[mysqld]
datadir=D:\some\other\directory
Start MySQL
Best Answer
Works for me with the latest version of SQuirreL. Make sure that you change the dialect to MySQL in the Alter Column dialog. Its just below the column name.