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.
You should be using the inserted
and deleted
pseudo tables to find out the rows for which the column was affected by the trigger - and then update only the related rows in the second table:
CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders
AFTER UPDATE
AS
IF UPDATE(TradingDate)
BEGIN
UPDATE c
SET ZLastSale = i.TradingDate
FROM dbo.Customers AS c
JOIN inserted AS i
ON i.CustomerID = c.CustomerID -- use the appropriate column for joining
JOIN deleted AS d
ON i.TransheadersID = d.TransheadersID
AND ( i.TradingDate <> d.TradingDate
OR d.TradingDate IS NULL
) ;
END ;
Best Answer
One problem with your trigger is that it is potentially updating all rows in the table, even if only one row was updated or inserted. If someone accidentally sets the values to NULL, they'll then get updated to the current time, the next time the trigger runs. Does this make sense for your application? I would think it makes more sense to leave past values alone (deal with those separately, though it seems doubtful any still exist), and only bother updating rows that are currently impacted directly by the trigger. You do that by joining to the
inserted
pseudo-table:That said, I agree with @SoleDBAGuy, who suggested a default constraint. Here is how you could implement that:
And then drop the trigger. Note that the second command may fail; if you have null values in there already, it's hard to justify just giving them "right now" as their sale date, when they could potentially be years old. So you can look at those individually, or just apply some date to them all, like:
Finally, please always use the schema prefix.