CODEWITHSUNDEEP
sql-serversql-server-2008
aSystemOverload
aSystemOverload

Reputation: 3104

What happens first: calculated persisted fields or ON INSERT TRIGGER updates

I have a table with persisted calculated fields. It has a trigger that updates some fields based upon a number of fields including the persisted fields.

Which is updated first, the persisted fields or the trigger'd update?

Upvotes: 1

Views: 124

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294407

The persisted computed value is computed before the actual insert. The trigger happens after the insert. Therefore persisted computed value(s) will always be available in the trigger.

Same is true for non-persisted computed values, but the mechanism there is different: any access to the non-persisted computed values will computed the value on-the fly, including access from the trigger itself. Therefore, again, the value will be available in the trigger.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

It took maybe 5 minutes to knock this up. Does it answer your question?

create table T (
    ID int not null,
    Val1 varchar(10) not null,
    Val2 as SUBSTRING(Val1,1,5) persisted
)
go
create table T2 (
    Action char(1) not null,
    Val1 varchar(10) not null,
    Val2 varchar(5) not null
)
go
create trigger T_T_I
on T
instead of insert
as
    insert into T (ID,Val1) select ID,Val1 from inserted
    insert into T2 (Action,Val1,Val2) select 'I',Val1,Val2 from inserted
go
create trigger T_T_A
on T
after insert
as
    insert into T2 (Action,Val1,Val2) select 'A',Val1,Val2 from inserted
go
insert into T(ID,Val1) values (1,'abcdefghi')
go
select * from T2

Results:

Action Val1       Val2
------ ---------- -----
A      abcdefghi  abcde
I      abcdefghi  abcde

That is, the computed column is always available from the inserted pseudo-table, which is all you should really care about.

Upvotes: 3

Related Questions