Insert Trigger on every row

Hi,
I want to insert some hundred rows into a table, an
instead of trigger should be executed for every insert.
up to now only the last insert executes the trigger.Steffen
I am not sure what are you trying to do?
Trigger executes a statement against the table .
Just a guess
INSERT INTO Table SELECT * FROM inserted
Note: Number of columns in target and destination tables should be matched.
"Steffen Steinert de Lima" <steffen_steinert_de_lima@.hotmail.com> wrote in
message news:059b01c3d368$9646fcc0$a101280a@.phx.gbl...
> Hi,
> I want to insert some hundred rows into a table, an
> instead of trigger should be executed for every insert.
> up to now only the last insert executes the trigger.|||Hi, here is the trigger
the insert is a normal insert like
select * into stoerung_2day from ...
when i insert the rows from ms-access via odbc the
trigger is executed for every inserted row, in sqlserver
it's only executed for the last row.
**************************************************
CREATE TRIGGER stoerung_more_day ON [dbo].
[stoerung_2day]
instead of INSERT, update
AS
declare @.cnt integer
declare @.id integer
declare @.day_beginn as datetime
declare @.day_ende as datetime
declare @.gueltig as datetime
select @.id = [id],
@.day_beginn = beginn,
@.day_ende = ende
from inserted
set @.cnt = day(@.day_beginn)
set @.gueltig = @.day_beginn
while @.cnt <= day(@.day_ende)
begin
insert into stoerung_2day values (@.id, @.day_beginn,
@.day_ende, @.gueltig)
set @.cnt = @.cnt +1
set @.gueltig = @.gueltig + 1
end
********************************************************
>--Original Message--
>Steffen
>I am not sure what are you trying to do?
>Trigger executes a statement against the table .
>Just a guess
>INSERT INTO Table SELECT * FROM inserted
>Note: Number of columns in target and destination tables
should be matched.
>
>"Steffen Steinert de Lima"
<steffen_steinert_de_lima@.hotmail.com> wrote in
>message news:059b01c3d368$9646fcc0$a101280a@.phx.gbl...
>> Hi,
>> I want to insert some hundred rows into a table, an
>> instead of trigger should be executed for every insert.
>> up to now only the last insert executes the trigger.
>
>.
>|||Steffen
Have you read what BOL says?
Trigger executes a statement against the table
In your case you have multi-insert so you have to cary out in inserted
table .
INSERT INTO Table SELECT * FROM inserted
What is goal of using these statements?
> set @.cnt = @.cnt +1
> set @.gueltig = @.gueltig + 1
"Steffen Steinert de Lima" <steffen_steinert_de_lima@.hotmail.com> wrote in
message news:021601c3d36e$653596e0$a001280a@.phx.gbl...
>
> Hi, here is the trigger
> the insert is a normal insert like
> select * into stoerung_2day from ...
> when i insert the rows from ms-access via odbc the
> trigger is executed for every inserted row, in sqlserver
> it's only executed for the last row.
> **************************************************
> CREATE TRIGGER stoerung_more_day ON [dbo].
> [stoerung_2day]
> instead of INSERT, update
> AS
> declare @.cnt integer
> declare @.id integer
> declare @.day_beginn as datetime
> declare @.day_ende as datetime
> declare @.gueltig as datetime
> select @.id = [id],
> @.day_beginn = beginn,
> @.day_ende = ende
> from inserted
> set @.cnt = day(@.day_beginn)
> set @.gueltig = @.day_beginn
> while @.cnt <= day(@.day_ende)
> begin
> insert into stoerung_2day values (@.id, @.day_beginn,
> @.day_ende, @.gueltig)
> set @.cnt = @.cnt +1
> set @.gueltig = @.gueltig + 1
> end
> ********************************************************
> >--Original Message--
> >Steffen
> >I am not sure what are you trying to do?
> >Trigger executes a statement against the table .
> >
> >Just a guess
> >INSERT INTO Table SELECT * FROM inserted
> >
> >Note: Number of columns in target and destination tables
> should be matched.
> >
> >
> >"Steffen Steinert de Lima"
> <steffen_steinert_de_lima@.hotmail.com> wrote in
> >message news:059b01c3d368$9646fcc0$a101280a@.phx.gbl...
> >> Hi,
> >>
> >> I want to insert some hundred rows into a table, an
> >> instead of trigger should be executed for every insert.
> >> up to now only the last insert executes the trigger.
> >
> >
> >.
> >|||>--Original Message--
>Steffen
>Have you read what BOL says?
where ?
>Trigger executes a statement against the table
>In your case you have multi-insert so you have to cary
out in inserted
>table .
>INSERT INTO Table SELECT * FROM inserted
>What is goal of using these statements?
>> set @.cnt = @.cnt +1
>> set @.gueltig = @.gueltig + 1
the sense of the whole trigger is to multiply the rows if
the from-date and the until-date in the record are not
the same. so i create a new record for every day of the
period.
example: id date_from date_until gueltig cnt
1 05-01-2004 07-01-2004
result: 1 05-01-2004 07-01-2004 05-01-2004 1
1 05-01-2004 07-01-2004 06-01-2004 2
1 05-01-2004 07-01-2004 07-01-2004 3
for inserting one record the trigger works well. perhaps
there's missing a commit after every insert ?
>"Steffen Steinert de Lima"
<steffen_steinert_de_lima@.hotmail.com> wrote in
>message news:021601c3d36e$653596e0$a001280a@.phx.gbl...
>>
>> Hi, here is the trigger
>> the insert is a normal insert like
>> select * into stoerung_2day from ...
>> when i insert the rows from ms-access via odbc the
>> trigger is executed for every inserted row, in
sqlserver
>> it's only executed for the last row.
>> **************************************************
>> CREATE TRIGGER stoerung_more_day ON [dbo].
>> [stoerung_2day]
>> instead of INSERT, update
>> AS
>> declare @.cnt integer
>> declare @.id integer
>> declare @.day_beginn as datetime
>> declare @.day_ende as datetime
>> declare @.gueltig as datetime
>> select @.id = [id],
>> @.day_beginn = beginn,
>> @.day_ende = ende
>> from inserted
>> set @.cnt = day(@.day_beginn)
>> set @.gueltig = @.day_beginn
>> while @.cnt <= day(@.day_ende)
>> begin
>> insert into stoerung_2day values (@.id, @.day_beginn,
>> @.day_ende, @.gueltig)
>> set @.cnt = @.cnt +1
>> set @.gueltig = @.gueltig + 1
>> end
********************************************************
>> >--Original Message--
>> >Steffen
>> >I am not sure what are you trying to do?
>> >Trigger executes a statement against the table .
>> >
>> >Just a guess
>> >INSERT INTO Table SELECT * FROM inserted
>> >
>> >Note: Number of columns in target and destination
tables
>> should be matched.
>> >
>> >
>> >"Steffen Steinert de Lima"
>> <steffen_steinert_de_lima@.hotmail.com> wrote in
>> >message news:059b01c3d368$9646fcc0$a101280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I want to insert some hundred rows into a table, an
>> >> instead of trigger should be executed for every
insert.
>> >> up to now only the last insert executes the trigger.
>> >
>> >
>> >.
>> >
>
>.
>|||Unlike Oracle, SQL-server does not have a trigger which
triggers once for every row. The trigger only 'fires' once for
the complete statement.
So you have to revert to solutions allready offered in the
other anwsers.
Have a nice 2004,
Ben Brugman
"Steffen Steinert de Lima" <steffen_steinert_de_lima@.hotmail.com> wrote in
message news:059b01c3d368$9646fcc0$a101280a@.phx.gbl...
> Hi,
> I want to insert some hundred rows into a table, an
> instead of trigger should be executed for every insert.
> up to now only the last insert executes the trigger.