4

How can I access the the INSERTED table's values in a trigger?

For example:

INSERT INTO sales (sku, qty) 
VALUES
(1001, 5), //I need these values in the trigger
(1002, 1)
...

This is my trigger:

DELIMITER $$

CREATE TRIGGER after_sales_insert 
AFTER INSERT ON sales 
FOR EACH ROW BEGIN

    UPDATE products
    SET NEW.qty = OLD.qty - INSERTED.qty
    WHERE sku = INSERTED.sku;

END;
$$
DELIMITER;

Note that sales.sku is a foreign key for the products table.

SQL Server has the INSERTED keyword, which doesn't seem to work for MySQL.

Answer:

NEW.qty references the qty on the table that the trigger is set on, not the table that is being updated.

CREATE TRIGGER after_sales_insert 
AFTER INSERT ON sales 
FOR EACH ROW BEGIN

    UPDATE products
    SET qty = qty - NEW.qty
    WHERE sku = NEW.sku;

END;
Lord Elrond
  • 13,430
  • 7
  • 40
  • 80

1 Answers1

5

Given:

INSERT INTO sales (sku, qty) 
VALUES
(1001, 5), //I need these values in the trigger
(1002, 1)
...

I believe you want this:

CREATE TRIGGER after_sales_insert AFTER INSERT ON sales 
FOR EACH ROW BEGIN
    UPDATE products
    SET qty = qty - NEW.qty
    WHERE sku = NEW.sku;
END;

References:

paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • is it possible in mysql to join on the NEW, like you can join on INSERTED in SQL server...so not to do it one at a time? – Ctznkane525 Mar 02 '19 at 23:43
  • It all makes sense now. I thought `NEW.qty` was referencing the products table and not the sales table. Thanks! – Lord Elrond Mar 02 '19 at 23:49