Insert trigger to populate other columns in same row

[mike]

I'm looking for an efficient way to populate derived columns when I
insert data into a table in SQL Server. In Informix and PostgreSQL
this is easily done using the "for each row..." syntax, but all I've
been able to come up with for SQL Server is the following:

create table testtrigger(id integer unique, b integer, c integer)
go

create trigger testtrigger_ins on testtrigger
for insert as
update testtrigger set c = (select ...some_function_of_b... from
testtrigger t1,inserted t2
where t1.id = t2.id)
where id in (select id from inserted);
go

where id is testrigger's unique id field, and c is a field derived from
b.

This seems terribly inefficient since each insert results in an extra
select and update. And if the table is large and unindexed (which it
could be if we are bulk loading) then I would imagine this would be
very slow.

Are there any better ways of doing this?

Many thanks,...
Mike Dunham-Wilkie

[undercups]

CREATE table ><>>Look at computered columns is your answer . BTW no
data is stored for this type of column.

e.g. CREATE TABLE mytable
(
low int,
high int,
myavg AS (low + high)/2
Duncan

[Hugo Kornelis]

On 22 Sep 2006 13:11:12 -0700, mi**@barrodale.com wrote:

>I'm looking for an efficient way to populate derived columns when I
insert data into a table in SQL Server. In Informix and PostgreSQL
this is easily done using the "for each row..." syntax, but all I've
been able to come up with for SQL Server is the following:

(snip)

>This seems terribly inefficient since each insert results in an extra
select and update. And if the table is large and unindexed (which it
could be if we are bulk loading) then I would imagine this would be
very slow.

Are there any better ways of doing this?

Hi Mike,

Since SQL Server is optimized for set-based operations, you'll probably
find the speed of these operations to be quite adequate in most cases.

Here's an alternate syntax of the UPDATE statement that might result in
even faster operation. Note, though, that this syntax has some quirks,
especially if there's not a guaranteed one to one mapping of rows in the
update target and the source of the data.

UPDATE t
SET c = some_function_of_b
FROM testtrigger AS t
INNER JOIN inserted AS i
ON i.id = t.id

But only use this if you have a solid reason for being unable to use a
computed column, as Duncan ("undercups") demonstrates in his reply!

--
Hugo Kornelis, SQL Server MVP