Hello all!

I have a insert trigger that should sent the responding column or columns to
a stored procedure on another server. My guess is that only one row of data
is transferred to the stored procedure. Of course i want all returning rows
to be transferred!
Trigger Code is:

CREATE TRIGGER INS_045004 ON [dbo].[ttdsls045004]
FOR INSERT
AS

set nocount on

declare @Status as varchar(30)
declare @Auftragsnummer as int
declare @Auftragsposition as int




BEGIN DISTRIBUTED TRANSACTION


SELECT @Auftragsnummer = t_orno, @Auftragsposition = t_pono,
@Status = CASE

WHEN t_ssls = 1 THEN 'Auftragsbestätigung gedruckt'

WHEN t_ssls = 8 THEN 'Auftrag im Vertrieb'

WHEN t_ssls = 3 THEN 'Auftrag am Lager'

WHEN t_ssls = 4 THEN 'Lieferschein gedruckt'

WHEN t_ssls = 6 THEN 'Rechnung gedruckt'

WHEN t_ssls = 7 THEN 'Auftrag abgeschlossen'

ELSE 'Keine Statusinformationen verfügbar'

END
FROM inserted


exec [172.16.0.100].b2bdata.dbo.psetStatus_Vertr
@STATUS,
@AUFTRAGSNUMMER,
@AUFTRAGSPOSITION




COMMIT TRANSACTION





The Code of the stored procedure:

CREATE PROCEDURE psetStatus_Vertr
(
@STATUS as varchar (30),
@AUFTRAGSNUMMER as int,
@AUFTRAGSPOSITION as int
)

AS


update STATUS set STATUS_VERTR = @STATUS
where AUFTRAGSNUMMER = @AUFTRAGSNUMMER and AUFTRAGSPOSITION =
@AUFTRAGSPOSITION
GO



Any would be appreciated! Thanks in advance!

Hi
Yes ,as your trigger does not handle multi-inserted rows.
In you case you will have to loop thru the inserted table and send the
data


"krostpupe" <krostpupe@discussions.microsoft.com> wrote in message
news:A565A6F6-948C-4FE2-B0FB-1A558025104B@microsoft.com...
> Hello all!
>
> I have a insert trigger that should sent the responding column or columns
> to
> a stored procedure on another server. My guess is that only one row of
> data
> is transferred to the stored procedure. Of course i want all returning
> rows
> to be transferred!
> Trigger Code is:
>
> CREATE TRIGGER INS_045004 ON [dbo].[ttdsls045004]
> FOR INSERT
> AS
>
> set nocount on
>
> declare @Status as varchar(30)
> declare @Auftragsnummer as int
> declare @Auftragsposition as int
>
>
>
>
> BEGIN DISTRIBUTED TRANSACTION
>
>
> SELECT @Auftragsnummer = t_orno, @Auftragsposition = t_pono,
> @Status = CASE
>
> WHEN t_ssls = 1 THEN 'Auftragsbest?tigung gedruckt'
>
> WHEN t_ssls = 8 THEN 'Auftrag im Vertrieb'
>
> WHEN t_ssls = 3 THEN 'Auftrag am Lager'
>
> WHEN t_ssls = 4 THEN 'Lieferschein gedruckt'
>
> WHEN t_ssls = 6 THEN 'Rechnung gedruckt'
>
> WHEN t_ssls = 7 THEN 'Auftrag abgeschlossen'
>
> ELSE 'Keine Statusinformationen verf?gbar'
>
> END
> FROM inserted
>
>
> exec [172.16.0.100].b2bdata.dbo.psetStatus_Vertr
> @STATUS,
> @AUFTRAGSNUMMER,
> @AUFTRAGSPOSITION
>
>
>
>
> COMMIT TRANSACTION
>
>
>
>
>
> The Code of the stored procedure:
>
> CREATE PROCEDURE psetStatus_Vertr
> (
> @STATUS as varchar (30),
> @AUFTRAGSNUMMER as int,
> @AUFTRAGSPOSITION as int
> )
>
> AS
>
>
> update STATUS set STATUS_VERTR = @STATUS
> where AUFTRAGSNUMMER = @AUFTRAGSNUMMER and AUFTRAGSPOSITION =
> @AUFTRAGSPOSITION
> GO
>
>
>
> Any would be appreciated! Thanks in advance!
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

Thank you for your fast reply!

Unfortunatly i don´t know exactly what you mean! How can i loop through the
inserted table?

"Uri Dimant" wrote:

> Hi
> Yes ,as your trigger does not handle multi-inserted rows.
> In you case you will have to loop thru the inserted table and send the
> data
>
>
> "krostpupe" <krostpupe@discussions.microsoft.com> wrote in message
> news:A565A6F6-948C-4FE2-B0FB-1A558025104B@microsoft.com...
> > Hello all!
> >
> > I have a insert trigger that should sent the responding column or columns
> > to
> > a stored procedure on another server. My guess is that only one row of
> > data
> > is transferred to the stored procedure. Of course i want all returning
> > rows
> > to be transferred!
> > Trigger Code is:
> >
> > CREATE TRIGGER INS_045004 ON [dbo].[ttdsls045004]
> > FOR INSERT
> > AS
> >
> > set nocount on
> >
> > declare @Status as varchar(30)
> > declare @Auftragsnummer as int
> > declare @Auftragsposition as int
> >
> >
> >
> >
> > BEGIN DISTRIBUTED TRANSACTION
> >
> >
> > SELECT @Auftragsnummer = t_orno, @Auftragsposition = t_pono,
> > @Status = CASE
> >
> > WHEN t_ssls = 1 THEN 'Auftragsbest?tigung gedruckt'
> >
> > WHEN t_ssls = 8 THEN 'Auftrag im Vertrieb'
> >
> > WHEN t_ssls = 3 THEN 'Auftrag am Lager'
> >
> > WHEN t_ssls = 4 THEN 'Lieferschein gedruckt'
> >
> > WHEN t_ssls = 6 THEN 'Rechnung gedruckt'
> >
> > WHEN t_ssls = 7 THEN 'Auftrag abgeschlossen'
> >
> > ELSE 'Keine Statusinformationen verf?gbar'
> >
> > END
> > FROM inserted
> >
> >
> > exec [172.16.0.100].b2bdata.dbo.psetStatus_Vertr
> > @STATUS,
> > @AUFTRAGSNUMMER,
> > @AUFTRAGSPOSITION
> >
> >
> >
> >
> > COMMIT TRANSACTION
> >
> >
> >
> >
> >
> > The Code of the stored procedure:
> >
> > CREATE PROCEDURE psetStatus_Vertr
> > (
> > @STATUS as varchar (30),
> > @AUFTRAGSNUMMER as int,
> > @AUFTRAGSPOSITION as int
> > )
> >
> > AS
> >
> >
> > update STATUS set STATUS_VERTR = @STATUS
> > where AUFTRAGSNUMMER = @AUFTRAGSNUMMER and AUFTRAGSPOSITION =
> > @AUFTRAGSPOSITION
> > GO
> >
> >
> >
> > Any would be appreciated! Thanks in advance!
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>

Lookup 'cursor' in the BOL



"krostpupe" <krostpupe@discussions.microsoft.com> wrote in message
news:B466D1D9-AD56-417C-AD7E-B086DF68F499@microsoft.com...
> Thank you for your fast reply!
>
> Unfortunatly i don´t know exactly what you mean! How can i loop through
> the
> inserted table?
>
> "Uri Dimant" wrote:
>
>> Hi
>> Yes ,as your trigger does not handle multi-inserted rows.
>> In you case you will have to loop thru the inserted table and send the
>> data
>>
>>
>> "krostpupe" <krostpupe@discussions.microsoft.com> wrote in message
>> news:A565A6F6-948C-4FE2-B0FB-1A558025104B@microsoft.com...
>> > Hello all!
>> >
>> > I have a insert trigger that should sent the responding column or
>> > columns
>> > to
>> > a stored procedure on another server. My guess is that only one row of
>> > data
>> > is transferred to the stored procedure. Of course i want all returning
>> > rows
>> > to be transferred!
>> > Trigger Code is:
>> >
>> > CREATE TRIGGER INS_045004 ON [dbo].[ttdsls045004]
>> > FOR INSERT
>> > AS
>> >
>> > set nocount on
>> >
>> > declare @Status as varchar(30)
>> > declare @Auftragsnummer as int
>> > declare @Auftragsposition as int
>> >
>> >
>> >
>> >
>> > BEGIN DISTRIBUTED TRANSACTION
>> >
>> >
>> > SELECT @Auftragsnummer = t_orno, @Auftragsposition = t_pono,
>> > @Status = CASE
>> >
>> > WHEN t_ssls = 1 THEN 'Auftragsbest?tigung gedruckt'
>> >
>> > WHEN t_ssls = 8 THEN 'Auftrag im Vertrieb'
>> >
>> > WHEN t_ssls = 3 THEN 'Auftrag am Lager'
>> >
>> > WHEN t_ssls = 4 THEN 'Lieferschein gedruckt'
>> >
>> > WHEN t_ssls = 6 THEN 'Rechnung gedruckt'
>> >
>> > WHEN t_ssls = 7 THEN 'Auftrag abgeschlossen'
>> >
>> > ELSE 'Keine Statusinformationen verf?gbar'
>> >
>> > END
>> > FROM inserted
>> >
>> >
>> > exec [172.16.0.100].b2bdata.dbo.psetStatus_Vertr
>> > @STATUS,
>> > @AUFTRAGSNUMMER,
>> > @AUFTRAGSPOSITION
>> >
>> >
>> >
>> >
>> > COMMIT TRANSACTION
>> >
>> >
>> >
>> >
>> >
>> > The Code of the stored procedure:
>> >
>> > CREATE PROCEDURE psetStatus_Vertr
>> > (
>> > @STATUS as varchar (30),
>> > @AUFTRAGSNUMMER as int,
>> > @AUFTRAGSPOSITION as int
>> > )
>> >
>> > AS
>> >
>> >
>> > update STATUS set STATUS_VERTR = @STATUS
>> > where AUFTRAGSNUMMER = @AUFTRAGSNUMMER and AUFTRAGSPOSITION =
>> > @AUFTRAGSPOSITION
>> > GO
>> >
>> >
>> >
>> > Any would be appreciated! Thanks in advance!
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>>