CODEWITHSUNDEEP
mysqltriggers
user3524375
user3524375

Reputation: 86

MYSQL Update Trigger get updated row ID

I have a table in MySQL which I am updating. I would like the trigger to get the updated row ID so as to do some work with it. How can I do it? I can do this in SQL Server, but I am new to MySQL and couldn't find anything online.

UPDATE...

So I can't share the code, but this is basically want I want to do.

Table: 
**id** **product** **price**
1,     Coke,         2.05 USD 
2,     Sprite,       2.00 USD
3,     7Up,          2.10 USD

I want to update id 2, "Sprite" price to 2.50 USD. Now I will do this normally, but I need to run a Trigger that will update an Audit table. The Audit table will need to register the id of the product. So basically I need to know that the updated row's id was 2...

is there anything similar in sql?

Upvotes: 1

Views: 14146

Answers (1)

citizen404
citizen404

Reputation: 1515

You can refer to the id column using either NEW.id or OLD.id as long as the id column will not change.

Generally spoken, with the alias NEW you can get the current value of the updated column and with OLD you obtain the value that the column had before the update was performed.

CREATE TRIGGER au_product_table
    AFTER UPDATE ON product_table
    FOR EACH ROW
    UPDATE audit
        SET product_id = NEW.id
        WHERE your_condition;

Upvotes: 8

Related Questions