Creating Insert with Mysql Trigger

Question

I am trying to create a simple insert trigger from Products table to ProductPrice table, the sql fails with no error message:

CREATE TRIGGER productTrigger AFTER INSERT ON Products
    FOR EACH ROW 
BEGIN
    INSERT INTO ProductPriceHistory (CURRENT_DATE(), CURRENT_USER(), productCode, productName, productLine, productVendor, quantityInStock, buyPrice)
    VALUES (productCode, productName, productLine, productVendor, quantityInStock, buyPrice);
END

Joachim Isaksson · Accepted Answer · 2012-02-19 00:17:23Z

2

To reference values from the newly inserted line, use the NEW keyword, and use the function calls as values, not as column names;

CREATE TRIGGER productTrigger AFTER INSERT ON Products
    FOR EACH ROW 
BEGIN
    INSERT INTO ProductPriceHistory (log_date, log_user, productCode, 
        productName, productLine, productVendor, quantityInStock, buyPrice)
    VALUES (CURRENT_DATE(), CURRENT_USER(), NEW.productCode, NEW.productName, 
        NEW.productLine, NEW.productVendor, NEW.quantityInStock, NEW.buyPrice);
END

answered Feb 19, 2012 at 0:17

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Warz Over a year ago

i ended up finding this in the documentation. Thanks for the catch, how can i add two triggers such that i insert when their is a new record in the Products and when a column changes?

Joachim Isaksson Over a year ago

@Warz You add the exact same trigger with a new name and replace AFTER INSERT with AFTER UPDATE and you should get that effect. Afaik there's no shortcut to add the same trigger for both events except defining it twice.

Warz Over a year ago

I tried that and mysql gives me-- error : This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

Joachim Isaksson Over a year ago

@Warz Sounds like it thinks you're trying to define two triggers for AFTER INSERT so you should double check your typing :) I could without problems define them as above with MySQL 5.5. If you're using an older version, I can't really say for sure, but it would seem a strange thing to not support if they support triggers at all.

Warz Over a year ago

If i simply change the trigger name and use AFTER UPDATE, this updates on every column regardless of a change or not. I am trying to write a trigger that updates the ProductPriceHistory table only when a particular column (quantity for example) changes?

mu is too short · Accepted Answer · 2012-02-19 00:15:16Z

0

Try moving your function calls into the VALUES:

INSERT INTO ProductPriceHistory (date_column_name, user_column_name, productCode, productName, productLine, productVendor, quantityInStock, buyPrice)
VALUES (current_date(), current_user(), new.productCode, new.productName, new.productLine, new.productVendor, new.quantityInStock, new.buyPrice);

I made up the date_column_name and user_column_name names, you'll have to supply the real ones. The parenthesized list the follows the table name should contain column names, not column values. And you might need to prefix the column names in VALUES with new. to tell MySQL to extract those from the newly inserted row (which is called new in a trigger).

answered Feb 19, 2012 at 0:15

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Collectives™ on Stack Overflow

Creating Insert with Mysql Trigger

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related