CODEWITHSUNDEEP
sql-servertriggers
Matt
Matt

Reputation: 1059

SQL Server instead of insert triggers - is there an easy way to modify a single column?

Given that SQL Server does not allow modification of the logical INSERTED and DELETED tables in a trigger, is there an easy way to change the value of a single column and not have to re-write the entire insert statement?

For example, the table to which I am applying the trigger has 20 columns. I want to modify the value from the INSERTED table for one column (per row) and then insert that row into the table. Can I do so without writing an insert statement with 19 columns plus the one column whose value I'm modifying?

Upvotes: 5

Views: 1775

Answers (2)

Jeremy
Jeremy

Reputation: 4838

To add more detail to BradCs answer:

The syntax for an update with a join is a little wonky

Update table
  set columnA = inserted.columnA / 10
from table
inner join inserted on table.id = inserted.id

Upvotes: 3

BradC
BradC

Reputation: 39946

Yes, just use an "after insert" trigger, instead of a "instead-of insert" trigger.

That way the row has already been inserted into the table, and all you have to do is issue a (carefully crafted) update statement, usually by inner-joining to the INSERTED table on the primary key field.

Upvotes: 7

Related Questions