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.

Insert statement

Hi
When I try to insert a row in table(without any index) why does it display
READS count as 1 in profiler trace for every insert statement executed
against tht table?
It also dispalys WRITE count but that is expected but why READ?
Table structure is normal only two int and char columns without any default
or identity defined.
Thanks in advance.
ManuProfiler includes some reading meta-data like verifying permissions and translating object name to
object id. My guess is that this is what you see...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:1E3FC2FF-ED8B-4430-9508-5D84F9654BC0@.microsoft.com...
> Hi
> When I try to insert a row in table(without any index) why does it display
> READS count as 1 in profiler trace for every insert statement executed
> against tht table?
> It also dispalys WRITE count but that is expected but why READ?
> Table structure is normal only two int and char columns without any default
> or identity defined.
> Thanks in advance.
> Manu
>
>|||Hmmm. Perhaps it has to READ the page so that it can put the data in it
before the page WRITEs back.
The reads and writes are, by the way logical reads and writes because the
actual physical reads and writes are managed by caching and buffers.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:1E3FC2FF-ED8B-4430-9508-5D84F9654BC0@.microsoft.com...
> Hi
> When I try to insert a row in table(without any index) why does it display
> READS count as 1 in profiler trace for every insert statement executed
> against tht table?
> It also dispalys WRITE count but that is expected but why READ?
> Table structure is normal only two int and char columns without any
> default
> or identity defined.
> Thanks in advance.
> Manu
>
>