CODEWITHSUNDEEP
mysql
Amadeus45
Amadeus45

Reputation: 1228

How to change a column value in AFTER INSERT trigger?

I have a trigger AFTER INSERT inside which I must update the value of a column of the row that has been just inserted. I obviously cannot use :

SET new.column = value;

I've tried to do a manual update in the trigger but it's also not allowed.

Is there any simple way to work this out ?

Many thanks !

Upvotes: 0

Views: 6994

Answers (2)

Raj More
Raj More

Reputation: 48024

Since the INSERT is already done by the time the AFTER is fired, I think you will have to write T-SQL to change the value based on your primary key.

If you want to change it before it gets inserted, you may want to consider moving to a BEFORE trigger instead.

Upvotes: 1

Robert Christie
Robert Christie

Reputation: 20685

An AFTER trigger is typically used to update something other than the row being updated. For example, if you wanted to log the fact that an update had been made, an AFTER trigger is ideal.

To change the value of a column as it is being inserted, you need to use a before trigger. For example

CREATE TRIGGER modify_column BEFORE INSERT ON mytable SET @column = value;

Where value is a query, pre defined value or NEW.column

Upvotes: 4

Related Questions