Trigger vratolomije

Na tabeli A imam TRIGGER Insert,

Kada upisujem 1 slog u tabelu sve je ok

INSERT INTO a
(polje1, polje2)
SELECT TOP 1 poljeA, poljeB FROM B

medjutim kad probam da upisem ceo set

INSERT INTO a
(polje1, polje2)
SELECT poljeA, poljeB FROM B

daje mi poruku o gresci

Msg 512, Level 16, State 1, ..............
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

u cemu je caka

Trigger ti nije dobro napisan (napisan je da podrzi dodavanje samo jednog reda i zato puca kada dodajes vise redova).
Za neko bolje objasnjenje ces morati pokazati kod trigera.

Hvala na odgovoru i ja sam zakljucio isto, ali ne mogu da otkrijem gde sam
uslovio trigger da radi samo sa jednim slogom.

Trigger je malo komplikovaniji ima i izmenu drugih tabela i dodavanaje novih slogova,
trenutno pokusavam da dodam vise slogova sa OSD = '30', gde ima azuriranje
samo jedne tabele,
kod je sledeci:


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [ti_oskar]

ON [dbo].[Oskar]

FOR INSERT

AS

BEGIN

DECLARE @ErrNo AS INTEGER,

@ErrMsg AS VARCHAR(256),

@pId AS BIGINT,

@Oznaka AS CHAR(1),

@Osd AS CHAR(2),

@bDok AS BIGINT,

@dDok AS DATETIME,

@Pred AS INT,

@InvBroj AS VARCHAR(13),

@Oz AS CHAR(1),

@OrgJed AS VARCHAR(12),

@OrgTro AS VARCHAR(12)

/* Prenos pocetnog stanja ... */

IF (SELECT OsD FROM Inserted) = '00'

BEGIN

INSERT INTO KumulOs

(gKumul,Pred, InvBroj, dProm, NabDug, NabPot, IspDug, IspPot, Vektr, Ucinak, Valuta, NabDevDug, NabDevPot, IspDevDug, IspDevPot)

SELECT YEAR(I.dDok) AS gAktiv, I.Pred, I.Invbroj, I.dDok, I.NabDug,I.NabPot, I.IspDug, I.IspPot, I.Vek, I.Ucinak, I.Valuta, I.NabDevDug, I.NabDevPot, I.IspdevDug, I.IspDevPot FROM Inserted I

END

/* Preuzimanje pocetnog stanja ... */

IF (SELECT OsD FROM Inserted) = '01'

BEGIN

/* Provera postojanja orgjed i orgtro .... */

IF (SELECT count(O.Orgjed) FROM OrgStr O JOIN INSERTED I ON O.Pred = I.Pred AND O.Orgjed = I.Orgjed) = 0

BEGIN

SET @ErrNo = 30001

SET @ErrMsg = 'Organizaciona jedinica ne postoji ili nije uneta'

GOTO error

END

IF (SELECT count(O.Orgjed) FROM OrgStr O JOIN INSERTED I ON O.Pred = I.Pred AND O.Orgjed = I.OrgTro) = 0

BEGIN

SET @ErrNo = 30001

SET @ErrMsg = 'Mesto troska ne postoji ili nije uneto'

GOTO error

END

/* Stavi oznamku A za aktivno sredstvo u MatOs-u ...*/

UPDATE M

SET M.FLEG = 'A'

FROM MATOS M JOIN INSERTED I ON M.Invbroj = I.Invbroj AND M.Pred = I.Pred

/* Dodaj slog za aktivno sredstvo u AktMatOs-u ....*/

INSERT INTO AktMatOS

(Pred, Invbroj, OrgJed, OrgTro, Lokacija, Regbr, DAktiv, NabVred, IspVred, Valuta, NabDev,IspDev,gNab,gAktiv,OsSifra)

SELECT Pred, Invbroj, OrgJed, OrgTro, Lok, Regbr, Ddok, NabDug, IspPot, Valuta, NabDevDug,IspDevPot,gNab,gAktiv,OsSifra FROM inserted

/* Treba kreirati slog u KumulOs-u jer svako sredstvo ima kumulativne promene za svaku godinu u KumulOs ...*/

INSERT INTO KumulOs

(gKumul,Pred, InvBroj, dProm, NabDug, IspPot, Vektr, Ucinak, Valuta, NabDevDug, IspDevPot)

SELECT YEAR(I.dDok) AS gAktiv, I.Pred, I.Invbroj, I.dDok, I.NabDug, I.IspPot, I.Vek, M.Ucinak, I.Valuta, I.NabDevDug, I.IspDevPot FROM Inserted I INNER JOIN MatOs M

ON M.Pred = I.Pred AND M.InvBroj = I.InvBroj

END

/* Aktiviranje ...*/

IF (SELECT OsD FROM Inserted) = '05'

BEGIN

/* Nedozvoljeno je menjati inventarski broj kod sloga koji ima neknjizene pripreme ....*/

IF (SELECT COUNT(InvBroj) FROM PriOs WHERE Prios.Pred = (SELECT Pred FROM Inserted) AND

Prios.InvBroj = (SELECT InvBroj FROM Inserted) AND

Prios.vNal IS NULL ) <> 0

BEGIN

SET @ErrNo = 30002

SET @ErrMsg = 'Postoje stavke u pripremi koje nisu knjizene' + char(10) +

' AKTIVIRANJE NIJE DOZVOLJENO'

GOTO error

END

/* Provera postojanja orgjed i orgtro .... */

IF (SELECT count(O.Orgjed) FROM OrgStr O JOIN INSERTED I ON O.Pred = I.Pred AND O.Orgjed = I.Orgjed) = 0

BEGIN

SET @ErrNo = 30001

SET @ErrMsg = 'Organizaciona jedinica ne postoji ili nije uneta'

GOTO error

END

IF (SELECT count(O.Orgjed) FROM OrgStr O JOIN INSERTED I ON O.Pred = I.Pred AND O.Orgjed = I.OrgTro) = 0

BEGIN

SET @ErrNo = 30001

SET @ErrMsg = 'Mesto troska ne postoji ili nije uneto'

GOTO error

END

/* Stavi oznamku A za aktivno sredstvo u MatOs-u ...*/

UPDATE M

SET M.FLEG = 'A'

FROM MATOS M JOIN INSERTED I ON M.Invbroj = I.Invbroj AND M.Pred = I.Pred

/* Dodaj slog za aktivno sredstvo u AktMatOs-u ....*/

INSERT INTO AktMatOS

(Pred, Invbroj, OrgJed, OrgTro, Lokacija, Regbr, DAktiv, NabVred, IspVred, Valuta, NabDev,IspDev,gNab,gAktiv)

SELECT Pred, Invbroj, OrgJed, OrgTro, Lok, Regbr, Ddok, NabDug, IspPot, Valuta, NabDevDug,IspDevPot,year(ddok),year(ddok) FROM inserted

/* Treba kreirati slog u KumulOs-u jer svako sredstvo ima kumulativne promene za svaku godinu u KumulOs ...*/

INSERT INTO KumulOs

(gKumul,Pred, InvBroj, dProm, NabDug, IspPot, Vektr, Ucinak, Valuta, NabDevDug, IspDevPot)

SELECT YEAR(I.dDok) AS gAktiv, I.Pred, I.Invbroj, I.dDok, I.NabDug, I.IspPot, M.VekTr, M.Ucinak, I.Valuta, I.NabDevdug, I.IspDevPot FROM Inserted I INNER JOIN MatOs M

ON M.Pred = I.Pred AND M.InvBroj = I.InvBroj

/* Update Prios .....*/

SET @Oznaka = 'A' /* u koju se menja ....*/

SET @Osd = (SELECT Osd FROM Inserted)

SET @bDok = (SELECT bDok FROM Inserted)

SET @dDok = (SELECT dDok FROM Inserted)

SET @Pred = (SELECT Pred FROM Inserted)

SET @InvBroj = (SELECT invbroj FROM Inserted)

SET @Oz = 'P' /* koja se menja .... */

EXEC Prios_Update @Oznaka, @Osd, @bDok, @dDok, @Pred, @InvBroj, @Oz WITH RECOMPILE

END

/* Strono aktiviranje */

IF (SELECT OsD FROM Inserted) = '06'

BEGIN

/* Treba AZURIRATI slog u KumulOs-u ...*/

UPDATE Kumulos

SET NabDug = NabDug + (SELECT NabDug FROM Inserted),

IspPot = IspPot + (SELECT IspPot FROM Inserted),

NabDevDug = NabDevDug + (SELECT NabDevDug FROM Inserted),

IspDevPot = IspDevPot + (SELECT IspDevPot FROM Inserted)

WHERE Pred = (SELECT Pred FROM Inserted) AND InvBroj = (SELECT InvBroj FROM Inserted) AND gKumul = YEAR((SELECT dDok FROM Inserted))

/* Stavi oznamku P za pasivno sredstvo u MatOs-u ...*/

UPDATE M

SET M.FLEG = 'P'

FROM MATOS M JOIN INSERTED I ON M.Invbroj = I.Invbroj AND M.Pred = I.Pred

/* Staviti datum pasivizacije u AktMatos ....*/

UPDATE AktMatOs

SET dPasiv = (SELECT dDok FROM Inserted)

WHERE AktMatos.InvBroj = (SELECT Invbroj FROM Inserted) AND AktMatos.Pred = (SELECT pred FROM INSERTED)


END

/* D o g r a d n j a ........*/

IF (SELECT OsD FROM Inserted) = '10'

BEGIN

/* Nedozvoljeno je menjati inventarski broj kod sloga koji ima neknjizene pripreme ....*/

IF (SELECT COUNT(InvBroj) FROM PriOs WHERE Prios.Pred = (SELECT Pred FROM Inserted) AND

Prios.InvBroj = (SELECT InvBroj FROM Inserted) AND

Prios.vNal IS NULL ) <> 0

BEGIN

SET @ErrNo = 30002

SET @ErrMsg = 'Postoje stavke u pripremi koje nisu knjizene' + char(10) +

' AKTIVIRANJE NIJE DOZVOLJENO'

GOTO error

END

/* Treba AZURIRATI slog u KumulOs-u ...*/

UPDATE Kumulos

SET NabDug = NabDug + (SELECT NabDug FROM Inserted),

IspPot = IspPot + (SELECT IspPot FROM Inserted),

NabDevDug = NabDevDug + (SELECT NabDevDug FROM Inserted),

IspDevPot = IspDevPot + (SELECT IspDevPot FROM Inserted)

WHERE Pred = (SELECT Pred FROM Inserted) AND InvBroj = (SELECT InvBroj FROM Inserted) AND gKumul = YEAR((SELECT dDok FROM Inserted))

/* Update Prios .....*/

SET @Oznaka = 'A' /* u koju se menja ....*/

SET @Osd = (SELECT Osd FROM Inserted)

SET @bDok = (SELECT bDok FROM Inserted)

SET @dDok = (SELECT dDok FROM Inserted)

SET @Pred = (SELECT Pred FROM Inserted)

SET @InvBroj = (SELECT invbroj FROM Inserted)

SET @Oz = 'D' /* koja se menja .... */

EXEC Prios_Update @Oznaka, @Osd, @bDok, @dDok, @Pred, @InvBroj, @Oz WITH RECOMPILE

END

/* R e m o n t ........*/

IF (SELECT OsD FROM Inserted) = '15'

BEGIN

/* Nedozvoljeno je menjati inventarski broj kod sloga koji ima neknjizene pripreme ....*/

IF (SELECT COUNT(InvBroj) FROM PriOs WHERE Prios.Pred = (SELECT Pred FROM Inserted) AND

Prios.InvBroj = (SELECT InvBroj FROM Inserted) AND

Prios.vNal IS NULL ) <> 0

BEGIN

SET @ErrNo = 30002

SET @ErrMsg = 'Postoje stavke u pripremi koje nisu knjizene' + char(10) +

' AKTIVIRANJE NIJE DOZVOLJENO'

GOTO error

END

/* Treba AZURIRATI slog u KumulOs-u ...*/

UPDATE Kumulos

SET NabDug = NabDug + (SELECT NabDug FROM Inserted),

IspPot = IspPot + (SELECT IspPot FROM Inserted),

NabDevDug = NabDevDug + (SELECT NabDevDug FROM Inserted),

IspDevPot = IspDevPot + (SELECT IspDevPot FROM Inserted)

WHERE Pred = (SELECT Pred FROM Inserted) AND InvBroj = (SELECT InvBroj FROM Inserted) AND gKumul = YEAR((SELECT dDok FROM Inserted))

/* Update Prios .....*/

SET @Oznaka = 'A' /* u koju se menja ....*/

SET @Osd = (SELECT Osd FROM Inserted)

SET @bDok = (SELECT bDok FROM Inserted)

SET @dDok = (SELECT dDok FROM Inserted)

SET @Pred = (SELECT Pred FROM Inserted)

SET @InvBroj = (SELECT invbroj FROM Inserted)

SET @Oz = 'R' /* koja se menja .... */

EXEC Prios_Update @Oznaka, @Osd, @bDok, @dDok, @Pred, @InvBroj, @Oz WITH RECOMPILE

END

/* P r o c e n a ........*/

IF (SELECT OsD FROM Inserted) = '20'

BEGIN

/* Treba AZURIRATI slog u KumulOs-u ...*/

UPDATE Kumulos

SET NabDug = NabDug + (SELECT NabDug FROM Inserted),

IspPot = IspPot + (SELECT IspPot FROM Inserted),

NabDevDug = NabDevDug + (SELECT NabDevDug FROM Inserted),

IspDevPot = IspDevPot + (SELECT IspDevPot FROM Inserted),

VekPre = CAST(VekTr AS DECIMAL(18,2)),

VekTr = CAST((SELECT Vek FROM Inserted) AS DECIMAL(18,2))

WHERE Pred = (SELECT Pred FROM Inserted) AND InvBroj = (SELECT InvBroj FROM Inserted) AND gKumul = YEAR((SELECT dDok FROM Inserted))

END

/* A m o r t i z a c i j a ........*/

IF (SELECT OsD FROM Inserted) = '30'

BEGIN

/* Treba AZURIRATI slog u KumulOs-u ...*/

UPDATE Kumulos

SET IspPot = IspPot + (SELECT IspPot FROM Inserted),

IspDevPot = IspDevPot + (SELECT IspDevPot FROM Inserted)

WHERE Pred = (SELECT Pred FROM Inserted) AND InvBroj = (SELECT InvBroj FROM Inserted) AND gKumul = YEAR((SELECT dDok FROM Inserted))

END

/* R a s h o d ....... */

IF (SELECT OsD FROM Inserted) = '35'

BEGIN

/* Stavi oznamku P za pasivno sredstvo u MatOs-u ...*/

UPDATE M

SET M.FLEG = 'P'

FROM MATOS M JOIN INSERTED I ON M.Invbroj = I.Invbroj AND M.Pred = I.Pred

/* Staviti datum pasivizacije u AktMatos ....*/

UPDATE AktMatOs

SET dPasiv = (SELECT dDok FROM Inserted)

WHERE AktMatos.InvBroj = (SELECT Invbroj FROM Inserted) AND AktMatos.Pred = (SELECT pred FROM INSERTED)

/* Treba AZURIRATI slog u KumulOs-u ...*/

UPDATE Kumulos

SET NabPot = NabPot + (SELECT NabPot FROM Inserted),

IspDug = IspDug + (SELECT IspDug FROM Inserted),

NabDevPot = NabDevPot + (SELECT NabDevPot FROM Inserted),

IspDevDug = IspDevDug + (SELECT IspDevDug FROM Inserted)

WHERE Pred = (SELECT Pred FROM Inserted) AND InvBroj = (SELECT InvBroj FROM Inserted) AND gKumul = YEAR((SELECT dDok FROM Inserted))

END

/* Otidjenje / Prodaja ....... */

IF (SELECT OsD FROM Inserted) = '45'

BEGIN

/* Stavi oznamku P za pasivno sredstvo u MatOs-u ...*/

UPDATE M

SET M.FLEG = 'P'

FROM MATOS M JOIN INSERTED I ON M.Invbroj = I.Invbroj AND M.Pred = I.Pred

/* Staviti datum pasivizacije u AktMatos ....*/

UPDATE AktMatOs

SET dPasiv = (SELECT dDok FROM Inserted)

WHERE AktMatos.InvBroj = (SELECT Invbroj FROM Inserted) AND AktMatos.Pred = (SELECT pred FROM INSERTED)

/* Treba AZURIRATI slog u KumulOs-u ...*/

UPDATE Kumulos

SET NabPot = NabPot + (SELECT NabPot FROM Inserted),

IspDug = IspDug + (SELECT IspDug FROM Inserted),

NabDevPot = NabDevPot + (SELECT NabDevPot FROM Inserted),

IspDevDug = IspDevDug + (SELECT IspDevDug FROM Inserted)

WHERE Pred = (SELECT Pred FROM Inserted) AND InvBroj = (SELECT InvBroj FROM Inserted) AND gKumul = YEAR((SELECT dDok FROM Inserted))

END

/* Visak - Manjak */

if (select osd from inserted) = '50'

begin

if (select opis from inserted) = 'visak'

begin

UPDATE Kumulos

SET NabPot = NabPot + (SELECT NabPot FROM Inserted),

IspDug = IspDug + (SELECT IspDug FROM Inserted),

NabDevPot = NabDevPot + (SELECT NabDevPot FROM Inserted),

IspDevDug = IspDevDug + (SELECT IspDevDug FROM Inserted)

WHERE Pred = (SELECT Pred FROM Inserted) AND InvBroj = (SELECT InvBroj FROM Inserted) AND gKumul = YEAR((SELECT dDok FROM Inserted))

end

if (select opis from inserted) = 'manjak'

begin

UPDATE M

SET M.FLEG = 'P'

FROM MATOS M JOIN INSERTED I ON M.Invbroj = I.Invbroj AND M.Pred = I.Pred

UPDATE AktMatOs

SET dPasiv = (SELECT dDok FROM Inserted)

WHERE AktMatos.InvBroj = (SELECT Invbroj FROM Inserted) AND AktMatos.Pred = (SELECT pred FROM INSERTED)

UPDATE Kumulos

SET NabPot = NabPot + (SELECT NabPot FROM Inserted),

IspDug = IspDug + (SELECT IspDug FROM Inserted),

NabDevPot = NabDevPot + (SELECT NabDevPot FROM Inserted),

IspDevDug = IspDevDug + (SELECT IspDevDug FROM Inserted)

WHERE Pred = (SELECT Pred FROM Inserted) AND InvBroj = (SELECT InvBroj FROM Inserted) AND gKumul = YEAR((SELECT dDok FROM Inserted))

end

end





/* Organizaciona jedinica i mesto troska ....... */

IF (SELECT OsD FROM Inserted) IN ('10','15','35','45')

BEGIN

SELECT orgjed = (SELECT Orgjed FROM AktMatOs WHERE AktMatos.Pred = (SELECT Pred FROM Inserted) AND

AktMatos.InvBroj = (SELECT Invbroj FROM Inserted))

SELECT Orgtro = (SELECT Orgjed FROM AktMatOs WHERE AktMatos.Pred = (SELECT Pred FROM Inserted) AND

AktMatos.InvBroj = (SELECT Invbroj FROM Inserted))

END

/* Prenos iz organizacione jedinice u organizacionu jedinicu .... */

IF (SELECT OsD FROM Inserted) = '56'

BEGIN

/* Treba formirati stavku dokumenta 55 ... */

IF (SELECT count(O.Pred) FROM OskarZag O JOIN Inserted I ON O.Pred = I.Pred AND O.OsD = '55' AND O.bDok = I.bDok AND O.dDok = I.dDok ) = 0

BEGIN

INSERT INTO OskarZag

(Pred,Osd,bDok,dDok)

SELECT Pred, '55', bDok, dDok FROM Inserted

END


SELECT @OrgJed = (SELECT Orgjed FROM AktMatOs WHERE AktMatos.Pred = (SELECT Pred FROM Inserted) AND

AktMatos.InvBroj = (SELECT Invbroj FROM Inserted))

SELECT @Orgtro = (SELECT Orgjed FROM AktMatOs WHERE AktMatos.Pred = (SELECT Pred FROM Inserted) AND

AktMatos.InvBroj = (SELECT Invbroj FROM Inserted))


/* Treba formirati stavku za oskar ....*/

INSERT INTO Oskar

(Pred, OsD, bDok, dDok, InvBroj, Orgjed, OrgTro, RegBr, NabDug, NabPot, IspDug, IspPot, Valuta, NabDevDug, NabDevPot, IspDevDug, IspDevPot, OsSifra, Lok )

SELECT Pred, '55', bDok, dDok, InvBroj, @OrgJed, @OrgTro, Regbr, - NabDug, - NabPot, - IspDug, - IspPot, Valuta, - NabDevDug, - NabDevPot, - IspDevDug, - IspDevPot, OsSifra, Lok FROM Inserted

/* treba promeniti OrgJed i OrgTro u AktMatos-u ....*/

UPDATE AktMatOs

SET OrgJed = (SELECT OrgJed FROM Inserted),

OrgTro = (SELECT OrgTro FROM Inserted),

RegBr = (SELECT regBr FROM Inserted),

OsSifra = (SELECT OsSifra FROM Inserted),

Lokacija = (SELECT Lok FROM Inserted)

WHERE AktMatos.InvBroj = (SELECT Invbroj FROM Inserted) AND AktMatos.Pred = (SELECT pred FROM INSERTED)

END

RETURN

error:

RAISERROR @ErrNo @ErrMsg

ROLLBACK TRANSACTION

END

Prvo da kazem WOW, veoma veliki triger :)
Nisam ga pogledao celog ali ti mogu reci da ti je greska vec u prvom redu, posle deklaracija:



Gresku koju si naveo u prvom postu ti i sama kaze da ne mozes porediti koristeci subquerije ako oni vracaju vise vrednosti. Ovaj IF ce ti raditi kada insertujes jedan red jer ce tada ovaj select vratiti uvek jednu vrednost i onda mozes da je poredis sa '00', ako unosis vise taj select ti vraca vise vrednosti te je poredjenje besmisleno.
Dorada tvog tigera za rad sa vise redova bi bila jako komplikovana i ako ne promenis pristup problemu najverovatnije da bi morao koristiti kursore sto nije bas preporucljivo u trigerima (mnogi kazu izbegavati ih bilo gde ako mozes).
Po meni bi mozda laksi nacin bio da triger ostavis da radi samo sa jednim redom (mogao bi i na pocetku trigera da proveris count(*) from inserted i ako je veci od jedan zabranis unos) a da inserte razbijas na red po red.

Ja do sada nisam vidjao ovako kompleksne trigere i mislim da bi ovo mozda trebalo ipak da se radi u nekoj stored proceduri.

i dole ima gresaka koliko hoces, npr ni jedan od ovih redova ti nece proci ako unosis vise od 1 reda:

SET @Osd = (SELECT Osd FROM Inserted)
SET @bDok = (SELECT bDok FROM Inserted)
SET @dDok = (SELECT dDok FROM Inserted)
SET @Pred = (SELECT Pred FROM Inserted)
SET @InvBroj = (SELECT invbroj FROM Inserted)
....


UPDATE Kumulos
SET NabDug = NabDug + (SELECT NabDug FROM Inserted),
IspPot = IspPot + (SELECT IspPot FROM Inserted),
NabDevDug = NabDevDug + (SELECT NabDevDug FROM Inserted),
IspDevPot = IspDevPot + (SELECT IspDevPot FROM Inserted)
.....

Slazem se donekle DarkMan-om, skloni ti komletno ovaj triger i napisi jednu solidnu stored proc i pokreci je rucno, po potrebi ili preko noci.... I ako uspijes da ovo stavis u trigger, udar na performanse ce biti znatan, no sve to zavisi od toga kako i kad ovo pokreces, a ako bas mora u triger, imaces da se igras dosta, pomocne tabele i kursori eventualno... :))

Hvala DarkMan-u i Koce-u na odgovoru i ideji da izolujem jedan po jedan upis,
najcesca situacija je da se upisuje jedan po jedan slog,
ovaj trigger koristim da odrazavam tabele stanja i druge prometne
tabele u aplikaciji
Ocigledno treba menjati filozofiju masovnog upisa.
Ja naime na SQL server dolazim sa Progress v9 baze gde se
trigger aktivira na svaki slog koji se upisuje
Ne znam da li bi pomoglo da se upis svakog sloga izoluje kao jedna
transakcija, nije bitno za performanse baze jer se
ceo posao kroz vb za 9000 slogova obavi za 45 minuta, kada vb
salje parametre stored proceduri posao se obavi za 5 min,
a kada se ukloni triger posao se obavi za 3 sekinde

Problem je kod izrade web aplikacije, jer ceo posao treba da se
posalje sql serveru koji treba da vrati samo poruku da je posao zavrsen.

Ima te li neku ideju.

Prethodnu temu koriscenje recordseta koji vraca stored procedura sam resio veoma efikasno
preko WITH cte (......) common_table_expression

saljem kod mozda nekom pomogne, ja sam izgubio dva dana na trazenju resenja


ALTER PROCEDURE [dbo].[OsnovAmortPoj]
-- Add the parameters for the stored procedure here
@DoDatuma nvarchar(10),
@Pred int,
@bDok bigint,
@dDok nvarchar(10),
@InvBroj nvarchar(13)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- SET NOCOUNT Off;

-- Insert statements for procedure here
WITH Osn1_cte ( Pred, InvBroj, MesAmo, NabVred, IspVred, SadVred)
AS
(
SELECT top 100 percent Pred, InvBroj, CASE WHEN osd = '00' THEN month(ddok) ELSE month(ddok) + 1 END AS mesAmo,
SUM(NabDug - NabPot) AS NabVred, SUM(IspPot - IspDug) AS IspVred,
SUM((NabDug - NabPot) - (IspPot - IspDug)) AS SadVred
FROM dbo.Oskar
WHERE (OsD <> '30') AND
(month(dDok) <= month(CONVERT(DATETIME, @dodatuma, 102))) AND
pred = @pred AND invbroj = @InvBroj
GROUP BY Pred, InvBroj, CASE WHEN osd = '00' THEN month(ddok) ELSE month(ddok) + 1 END
ORDER BY Pred, InvBroj, mesAmo
),

Osn2_cte(Pred, InvBroj, MesAmo, SadVred, Kumul, VekTr, Fleg, MesDo, likvOst)
AS
(
SELECT top 100 percent t1.Pred, t1.InvBroj, t1.MesAmo, t1.SadVred,
(select sum(sadvred) as expr1
from Osn1_cte as t2
where t2.pred = t1.pred and
t2.invbroj = t1.invbroj and
t2.mesamo <= t1.mesamo) as Kumul,
kumulos.vektr, Matos.Fleg,
isnull((select top 1 t3.mesamo as expr2
from Osn1_cte as t3
where t3.pred = t1.pred and
t3.invbroj = t1.invbroj and
t3.mesamo > t1.mesamo) - 1,
month(CONVERT(DATETIME, @dodatuma, 102))) as MesDo,
kumulos.LikvOst
from Osn1_cte as t1 INNER JOIN KumulOs ON Kumulos.pred = t1.pred AND
Kumulos.invbroj = t1.invbroj
INNER JOIN MatOs ON Matos.pred = t1.pred AND
Matos.Invbroj = t1.invbroj
order by t1.pred, t1.invbroj, t1.mesamo
),
Osn3_cte (pred, InvBroj, Amort)
AS
(
select t3.Pred, t3.InvBroj,
SUM(dbo.IznAmort(t3.Kumul, t3.MesAmo, t3.MesDo, t3.VekTR, t3.LikvOst)) as Amort
from osn2_cte as t3
GROUP BY t3.Pred, t3.InvBroj
--ORDER BY t3.Pred, t3.InvBroj
)



insert oskar
(Pred, OsD, bDok, dDok, InvBroj, IspPot, Valuta)
SELECT Pred, '30', @bDok, CONVERT(DATETIME, @dDok, 102), InvBroj, Amort, 'EUR'
from Osn3_cte as T4


Ovaj Insert ima problem sa pomenutim trigerom,
imate li ideju kako da prepravim ovaj insert

Jedino sto mi pada na pamet je pomocu kursora:

Hvala na odgovoru, mislim da je ovo resenje.