Insert trigger to populate other columns in same row

[mike@barrodale.com]

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_functio n_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]

Re: Insert trigger to populate other columns in same row

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]

Re: Insert trigger to populate other columns in same row

On 22 Sep 2006 13:11:12 -0700, mike@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_o f_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