Select Into and Trigger on Destination table....

[Rog]

I am running SQL Server 2000

Will trigger happen on each insert when doing a select
into?

From what I am finding out it doesn't. I just tried this
and in a trigger on the destination table get a record
count > 1 in the inserted table when doing a select into
and the select into has more than one reocrd.

I thought the inserted table would only have one record?

Why is this happening? How can I work around this?

Thanks,

Rog

[Vern Rabe]

Rog:

Your conclusions are correct. SQL Server's triggers are
statement level, not row level. Best solution is to code
your triggers to support > 1 rows in inserted/deleted.

Vern

>-----Original Message-----
>I am running SQL Server 2000
>
>Will trigger happen on each insert when doing a select
>into?
>
>From what I am finding out it doesn't. I just tried this
>and in a trigger on the destination table get a record
>count > 1 in the inserted table when doing a select into
>and the select into has more than one reocrd.
>
>I thought the inserted table would only have one record?
>
>Why is this happening? How can I work around this?
>
>Thanks,
>
>Rog
>
>.
>

[David Portas]

I assume you mean INSERT... SELECT rather than SELECT INTO? You can't have a
trigger on the target table of SELECT INTO because the table doesn't exist
until the statement is executed.

The trigger will fire once for each INSERT statement. If multiple rows are
inserted with a single INSERT statement then the trigger code is executed
once and the Inserted table contains all the rows inserted. This behaviour
is by design.

In what way is this a problem to you? Whatever your trigger code is doing it
should be possible to write statements which operate on the whole Inserted
table. If you need help post your DDL and trigger code.

--
David Portas
------------
Please reply only to the newsgroup
--

"Rog" <rogoflap@yahoo.com> wrote in message
news:022a01c347f3$48075150$a601280a@phx.gbl...

> I am running SQL Server 2000
>
> Will trigger happen on each insert when doing a select
> into?
>
> From what I am finding out it doesn't. I just tried this
> and in a trigger on the destination table get a record
> count > 1 in the inserted table when doing a select into
> and the select into has more than one reocrd.
>
> I thought the inserted table would only have one record?
>
> Why is this happening? How can I work around this?
>
> Thanks,
>
> Rog
>

[Shirkant]

Its true i also face the same problem when i do the Bulk Upload thru BCP...

I need to use this BCP and parallely insert records in some other table with further calculations...

How do i handle this