An insert is always within a transaction.
If you don't have an explicit BEGIN TRAN ... COMMIT
or SET IMPLICIT_TRANSACTIONS ON
then the statement runs as a self contained auto commit transaction.
The trigger is always part of the transaction for the action that fires the trigger. If an error occurs in the trigger that causes transaction rollback then the firing action will be rolled back too.
Triggers implicitly have XACT_ABORT
on. An error with this setting on will automatically lead to transaction rollback (except for errors raised in the code with the RAISERROR
statement).
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.
Best Answer
I fear the
where
clause with thein
is causing problems. Tryjoining
to theinserted
table instead. Here are two examples - one with thein
clause and one with ajoin
to theinserted
table. The results are different using my sample data.The trigger code runs as part of the transaction that actually inserted the rows in the base table. If the trigger encounters a fatal error, the transaction should roll back everything that was done during that transaction. It's an all-or-nothing process.
You cannot swallow an exception inside a trigger even with try/catch logic. Any exception inside a trigger WILL abort the transaction.