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.
Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox anyway. Why not just run the process more frequently, if timing is so important?
Please give this tip a look-through.
If you really, really, really want to do this the wrong way, you can just enable xp_cmdshell
and fire away.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
Now, assuming the user has access to xp_cmdshell
and/or the SQL Server service account can see the folder where the python script is stored, you should be able to do this from within your trigger:
EXEC master..xp_cmdshell N'C:\Python27\python.exe C:\source\NotifyAgents.py';
As an aside, you should state in your question that you aware that this is a very bad thingTM, but you are not concerned with that, for whatever reason. I still don't think you're going to get as real time as you expect, even if you do fire this from the trigger. Have you considered database mail instead of python?
Best Answer
You need a before insert trigger ("after insert", the bird has flown, it's useless modifying the new vales - if MySQL allows you). In the trigger body, use the following code:
See the effect here: https://www.db-fiddle.com/f/eEiK6pKGEQEoYL9BPdfooP/0