How to change a column value in AFTER INSERT trigger ?

Question

Hello,

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 !

Answer 1

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

Answer 2

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.