sparc Sladjan Parc
Član broj: 65760 Poruke: 134 91.150.127.*
|
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
|