[ sanjal @ 12.09.2010. 01:45 ] @
Postavila bi jedno verovatno prosto pitanje za vecinu ljudi koji se profesionalno bave developingom i administriranjem baza podataka.Naime, spreman ispit na fakultetu, radimo sa trigerima, procedurama, kursorima itd... E sada, trenutno radim sa kursorima i imam dosta nejasnoca oko pojma samog kursora. Citam da je rezultat iskaza SELECT skup redova, da taj skup predstavlja celinu, a ne zbirku pojedinacnih zapisa. I naravno, da bi se ti podaci mogli obradjivati potrebno je pristupiti svakom pojedinacnom zapisu. Pitanje je sledece:

1. Kakva je razlika izmedju iskaza napisanog u kursoru i standarnog SQL iskaza "SELECT * FROM tabela" ?
2. Zar pojedinacnom zapisu ne moze da se pridje preko iskaza 'SELECT nesto FROM tabela WHERE nesto = nesto and nesto = * '' ?
3. Ako moze, pa cemu onda kursori?
4. I kako da prepoznam situaciju u kojoj cu da koristim kursore u kombinaciji sa trigerima i procedurama?
-- cetvrta tacka, pored prve mi je jako bitna


Izvinjavam se svima na verovato dosadnom i prilicno opsirnom pitanju. MOj problem je sto ima ispit za 20 tak dana i sto nemam dovoljno vremena da se mucim oko pronalazenja odgovora. Asistenti i profesori su dosta neazurni, netolerantni i drski tako da sam konsultacije davno odbacila. A Kada se dodje na ispit nastaje problem. Jako malo ljudi prodje. Pomagajte please. Ako moze i kroz neki primercic :)
[ Zox @ 12.09.2010. 13:34 ] @
Prosto je, tvoja knjiga iz koje ucis je napravila to da zvuci komplikovano :)

Ako poznajes bilo koju tehniku programiranja i rada sa DB, znas za LOOP strukture.
Npr
select * from table where neki filter
DO WHILE NOT rs.EOF
.... ovde sada sa svakim recordom iz dobijenog recordseta radis nesto sta ti treba
NEXT

e, u TSQL programiranju cursor je ustvari LOOP kroz neki recordset koji je definisan SELECT statementom. Ne zaboravi da ti upravo komandom za deklarisanje cursora pozivas select statement, npr:
declare cur_nesto cursor for
select from table where..

Inace, ja ne vidim bas neku zavisnu vezu izmedju cursora i trigera i procedura. Cursor moze da bude koriscen apsolutno nezavisno od trigera i procedura, zavisno od toga sta hoces da radis sa svakim recordom u okviru cursora.
Cursor ti u stvari pruza vecu fleksibilnost da sa svakim pojedinacnim recordom iz recordseta radis razlicite stvar, proveravas neke uslove pa zavisno od toga update-ujes, brises, insertujes, pozivas procedure, itd..

Kako bi npr sa standardnim select statementom uradila sledece.
select * from tblUsers where gender is not null
onda, zavisno od pola, ubaci taj record u tblMen ili tblWomen

Sa obicnim select to ne mozes (mozes ali iz 2 puta, prvo da selektujes sve gde je gender "man" i da to insertuejs pa onda jos jednom sa "women") ali sa cursorom itekako mozes. U okviru cursora ides kroz svaki record, proveravas pol i zavisno od pola insertujes u odgovarajucu tablelu. Tek prvi primer koji mi je pao na pamet.

Drugim recima, a vezano za tvoja pitanje 2 tj 3: Da, u pravu si SELECT statementom ti mozes da pridjes nekom zapisu. Ali sa cursorom ti mozes sa tim zapisom da radis sta hoces.

Jos jednom, funkcije i procedure nemaju nikakve direktne veze sa cursorom, tj rad nezavisno jedan od drugog, ali se uglavnom koriste u kombinaciji sa cursorom da bi se olaksalo t-sql programiranje. Cak stavise, trigeri nemaju bas nikakve direktne veze sa cursorom.
[ sule99 @ 13.09.2010. 11:06 ] @
Ako će ti ovo pomoći, to je slično ko da pitaš čemu služi FOR petlja u programiranju... E tome istome služi kursor u bazama. ;)
to je najjednostavniji odgovor, ako razumiješ barem malo programiranje.

pozdrav
[ Zidar @ 13.09.2010. 14:18 ] @
Ovo su tvoja dva najbolja pitanja:
3. Ako moze, pa cemu onda kursori?
4. I kako da prepoznam situaciju u kojoj cu da koristim kursore u kombinaciji sa trigerima i procedurama?

U svakom slucaju, ne treba da brines. To sto ti nije jasno cemu uopste kursori govori da najverovatnije razmisljas na ispravan nacin, a to je da se u SQL operacije rade nad skupovima podataka, a ne proceduralno. Kursori su ostatk iz proceduralnog doba, kad za mnoge slucajeve i nije bilo nikakvog drugog resenja. AKo te ne mrzi, citajk dalje pa ce verovatno biti za nijansu jasnije kada koristiti a kada ne i zasto je to tako.

Ako nemas vremena, nema veze, postavi jojs pitanaj pa cemo razjasniti samu tehniku pisanja kursora, imai boljih knjiga iz kojih mozemo da procitamo i napisemo odgovor na forumu.

Q: 3. Ako moze, pa cemu onda kursori?
A: Zbog istorije. Nekada nije bas sve moglo da se uradi bez kursora, pa su se kursori koristili naveliko. Danas, gotovo sve, prakticno sve moze da s euradi bez kursora i obicno je efikasnije. stoga se danas kursori izbegavaju ako ikako moze. medjutim, ne mgu UVEK da se izbegnu i nije UVEk resenje bez kursora bolje engo resenje sa kursorom. U pocetku ti se cini da su kursori neizbezni, medjutim vremenom naucis SQL bolje i bolje i ostane ti sve manje i manje situacija gde ti kursor zaista treba. To dolazi sa iskustvom. ZOx ti je dao primer gde se moze koristiti kursor, i odmah objasnio kako s eto isto moze postici obicnimSQL-om. Ono sto ti nije reko jeste kako da ocenis sta je 'bolje'. Pa, probas i vidis.
Citat:
Kako bi npr sa standardnim select statementom uradila sledece.
select * from tblUsers where gender is not null
onda, zavisno od pola, ubaci taj record u tblMen ili tblWomen

Sa obicnim select to ne mozes (mozes ali iz 2 puta, prvo da selektujes sve gde je gender "man" i da to insertuejs pa onda jos jednom sa "women")


Q: 4. I kako da prepoznam situaciju u kojoj cu da koristim kursore u kombinaciji sa trigerima i procedurama?
A: To dodje sa iskustvom. jedna od situacija gde kursori bolej rade jeste izracunavanje kumulativnih vrednosti, 'running totals'. Na primer, svakog dana dolaze ljudi an forum i to se belezi u neku tabelu u nekoj bazi. tako, u ponedeljak je zabelezeno 20 poseta, u utorak 15, u sredu 18, u cetvrtak 25, u petak 12, u subotu 56. 'Running total' trazi da se za svaki dan izracuna koliko je bilo poseta od pocetka nedele do tog dana, ukljucjuci i taj dan. Bilo bi
Ponedeljak = 20
Utorak = 20+15 = 35
sreda = 20+15+18= 53
Cetvrtak = 20+15+18+25=78
Petak = 20+15+18+25+12=90
Subota = 20+15+18+25+12+56=46

U SQL bez kursora bi napisala kveri sa subkverijem (correlated subquery) gde bi za svaki red izracunala zbir svih prethodnih redova, ovako kao sam ja uradio u primeru. Subkveri za svaki sledeci red bi imao da sabira sev ispred nejga, vidis kako s ebroj sabiraka povecava. Korsor ti omogucuje da imas varijablu u kojoj pamtis prethodni zbor i samo dodajes jednu vrednost. Ovako nekako:

Ponedeljak = 20, Varijabla = 20
Utorak = 20+15 = 35, Varijabla = 35
sreda = 35+18= 53, Varijabla = 53
Cetvrtak = 53+25=78, Varijabla = 78
Petak = Varijabla +12=90, VArijabla = 90
Subota = Varijabla +56=46

Kursor dakle izvrsava onoiko sabiranja koliko ima redova, dok obican SQL izvrsava 1+2+3+4+5+..+ n sabiranja, ili n*(n+1)/2 sabiranja.

Zasto onda ne koristimo kursore uvek? Ako je broj racnskih operacija priblisno isti u kursoru i u obicinom SQL izrazu, ispostavlja se da se obican SQL izraz izvrsava visestruko brze, zbog internog nacina koriscenja memorije. Visestruko znaci 100 purta brze ili vise. Na primeru sa 100 redova razlika se ne oseti, ali na primeru sa 100,000 redova i vise, i te kako se oseti razlika. Sigurno si cula za price gde se neki izvestaj otvara po dva sata, ili da se neka operacija na SQL sistemu izvrsava celu noc. Onda dodje neki carobnjak i to se sve odradi za par minuta. Obicno se ovakvo drasticno redukovanje vremena izvrsavanja dobije ako s ekursor zameni SQL izrazom (pod uslovom da je baza lepo rpojektovana, relacije, kljucevi indeksi i ostalo). Ovo u zagradi je jako vazno i obicno ako nesto en valja, ond amnogo toga ne valja. Ako se sitem oslanja na kursore, obicno ni sama baza nije uradjena kako valja, ne postoji odrzavanje i slicno, pa jedan problem maskira drugi i tesko je videti sta se u stvari desava.

generalno, osim za velike running totals, za kursorima absolutno nema potrebe. Naravno, ako nikako drugacije en umes da resis problem, ikakvo resenje je bolje nego nikakvo, pa je i kursor dobar.


U svakom slucaju, ne treba da brines. To sto ti nije jasno cemu uopste kursori govori da najverovatnije razmisljas na ispravan nacin, a to je da se u SQL operacije rade nad skupovima podataka, a ne proceduralno. Kursori su ostatk iz proceduralnog doba, kad za mnoge slucajeve i nije bilo nikakvog drugog resenja.

20 dana je vise nego dovoljno da naucis ono sto ti treba za ispit o kursorima. Ako imas problema, javi se na forum, pogledacemo u nekim boljim knjigama neko bolje objasnjenje i bices OK. Posle ispita, slobodno zaboravi na kursore.

[ nemanjal @ 15.09.2010. 16:16 ] @
Imam jedan primer sa ispita. Navodno je resenje tacno (valjda ga je asistenkinja uradila), ali meni nece da radi.

Transakcije koje šalterski radnik unese preko odgovarajuće ekranske forme se evidentiraju u dnevniku transakcije. Proceduralnim mehanizmima baza podataka bi trebalo obezbediti da se za svaku novu transakciju koja se tiče nekog internog računa ista automatski upiše i u karticu računa. Da li je račun naveden u transakciji interni ili eksterni (otvoren kod druge banke) može se proveriti upitom nad tabelom računa. Ukoliko u toj tabeli postoji račun sa navedenim brojem u pitanju je interni račun. Za transakcije tipa uplata (‘U’) iznos transakcije se u kartici prikazuje kao iznos duguje, a za isplate (tip transakcije ‘I’) kao iznos potražuje. Novo stanje računa se dobija uvećanjem ili smanjenjem prethodnog stanja računa.

Atachovala sam i ER model baze.

kREIRANJE BAZE PODATAKA

CREATE DATABASE transakcije
GO
use transakcije
CREATE TABLE dnevnikTransakcije
(
ID_rad integer not null ,
DatTransak Datetime not null,
RedniBroj INT IDENTITY not null,
vremetransak datetime null,
sifVrsteTransak integer not null,
brRacuna char(16) null,
iznos decimal(12,2) null,
napomena varchar(100)
)
go

CREATE TABLE racun
(
brRacuna char(16) not null,
datOtvaranja datetime null,
BrZahtevateva integer null,
IdOrganJedinice int null,
sifKlijenta integer null,
IDtretmanaKlijenta integer null,
SIFnacinaIzvrs integer null,
limit integer null,
status char(1) null,
datDefStatusa date null
)
go

CREATE TABLE vrstaTransakcije
(
sifVrsteTransak integer not null,
nazVrsteTransak varchar(40),
tipTransak char(1)
)
GO

CREATE TABLE KarticaRacuna
(
brRacuna char(16) not null,
RedniBroj integer NOT null,
DatTransak datetime not null,
vremetransak datetime null,
sifVrsteTransak integer not null,
iznosDugovanja decimal(12,2) null,
iznosPotrazivanja decimal(12,2) null,
stanje decimal (12,2) null
)
USE transakcije
GO



CREATE TRIGGER transakcijatriger
ON dnevnikTransakcije
AFTER INSERT
AS
DECLARE @datTransak date,
@vremetransak datetime,
@sifVrsteTransak integer,
@brRacuna char(16),
@iznos dec(12,2),
@RedniBroj integer,
@stanje dec(12,2)
*******************************************************************
*********************************************************************

resenje koje nece da mi radi:
------------------
------------------

DECLARE transakcija_cursor CURSOR
FOR
SELECT dattransak, vremetransak, sifVrsteTransak, brRacuna, iznos
FROM Inserted
OPEN transakcija_cursor
FETCH transakcija_cursor
INTO @dattransak, @vremetransak, @sifVrsteTransak, @brRacuna, @iznos
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM racun WHERE brracuna = @brracuna)
BEGIN
select @RedniBroj =max(rednibroj)
from dnevniktransakcije
where rednibroj = @rednibroj
--
SELECT @STANJE = stanje
FROM karticaRacuna
WHERE brRacuna = @brRacuna

IF (SELECT tiptransak FROM vrstaTransakcije WHERE sifVrsteTransak = @sifVrsteTransak) = 'U'
begin
INSERT karticaRacuna
VALUES (@brRacuna, @redniBroj+1, @datTransak, @vremeTransak, @SifVrsteTransak, @iznos, 0 , @stanje+@iznos)
END
ELSE
BEGIN
INSERT karticaRacuna
VALUES (@brRacuna, @redniBroj+1, @datTransak, @vremeTransak, @SifVrsteTransak, 0, @IZNOS, @stanje-@iznos)
END
END
END
FETCH transakcija_cursor
INTO @dattransak, @vremetransak, @sifVrsteTransak, @brRacuna, @iznos
CLOSE transakcija_cursor
DEALLOCATE transakcija_cursor


---------------------------------------
tabela VRSTE_TRANSAKCIJA mi je ispunjena

Uabcila sam jedan red u tabelu "RACUN", A nako toga sam pokusala da pokrenem triger pokusajem insertovanja tabele DNEVNIK_TRANSAKCIJA:
INSERT INTO dnevnikTransakcije
VALUES
(1,'9-9-2010', getdate(), 2, '840-1045666-13', 18200, 'uplata')


-- medjutim, pokretanjem naredbe EXECUTE dobijam sledecu poruku

Msg 515, Level 16, State 2, Procedure transakcijatriger, Line 42
Cannot insert the value NULL into column 'RedniBroj', table 'transakcije.dbo.KarticaRacuna'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Kao sto se i vidi iz prilozenog, javlja mi da se unutar INSERT ISKAZA , ne moze uneti kolona sa vrednoscu NULL, iako sam za tabelu DNEVNIK_TRANSAKCIJA devinisala tip podatka int i njemu pridruzila IDENTITY da bi se sam kreirao redni broj. Nemam pojma sta da radim . Otprilike bi me ovakav zadatak cekao na ispitu. Da li neko meze da mi pomogne, i da mi ukaze na greske, a naravno, ne menjajuci previse sintaksu.









[Ovu poruku je menjao nemanjal dana 15.09.2010. u 17:28 GMT+1]

[Ovu poruku je menjao nemanjal dana 15.09.2010. u 17:30 GMT+1]
[ Zidar @ 16.09.2010. 16:58 ] @
Citat:
Msg 515, Level 16, State 2, Procedure transakcijatriger, Line 42
Cannot insert the value NULL into column 'RedniBroj', table 'transakcije.dbo.KarticaRacuna'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Ako pazljivo procitas poruku, problem je kad INSERT radis u tabelu KarticaRacuna.

Ovaj deo uzrokuje gresku:
Code:

INSERT karticaRacuna
VALUES (@brRacuna, @redniBroj+1, @datTransak, @vremeTransak, @SifVrsteTransak, 0, @IZNOS, @stanje-@iznos)


Kaze da pokusavas da insertujes NULL u kolonu RedniBroj. To opet znaci da ti je @redniBroj+1, = NULL, a to je moguca samo ako nisi nigde dodeli vrednost varijabli @RedniBroj. To si pokusao da uradis ovde:

select @RedniBroj =max(rednibroj)
from dnevniktransakcije
where rednibroj = @rednibroj

Posto pre toga nisi nigde dodelio vrednost varijabli @RedniBroj, toj varijabli je ponovo dodeljena vrednost NULL koja se kasnije prenela u INSERT izraz...

select @RedniBroj =max(rednibroj)
from dnevniktransakcije
where rednibroj = NULL

a ovo ne vraca ni jedan red, pa cela operacija ostavlja NULL u varijabli @RedniBroj .

Tu ti je greska.

Usput, da li je cilj zadatka da se napise triger koji koristi kursor, ili jednostavno da se napise triger, kako god znas i umes?

Kursori su generalno losa ideja, a pogotovo u trigerima. A u ovom slucaju je mnogo lakse napisati triger bez upotrebe kursora.

[ Zidar @ 16.09.2010. 17:04 ] @
Kad malo bolje pogledam zadatak, pocinjem da razume uzroke ekonomske krize..... Decu us koli uce absolutno pogresne stvari.

Ako ovako uce decu u skoli, ovaj svet zasigurno ide u srednji vek. Prvo, primer koji vam je assitent dao je primer kao baza podataka ne bi smela da izgleda. Ocigledno je neko bukvalno prekopirao 'paper based' knjigovodstvo u bazu podataka. Drugo, nacin na koji vam je zadato da pisete triger je potpuno pogresan. Ako vec mora triger, onda bi trebalo izbeci kursore kao kugu. Ne bi bilo nista lose da se kursori potpuno izbace iz nsatvnog planaa i programa, ima toliko drugih korisnih stvari da se nauci.

Prvo cu da objasnim sta ne valja sa bazom podataka, a posle cu da ti pokazem kako da napises triger, ako hoces. Ovo prvo, da bi naucio nesto korisno, a ono drugo da bi naucio kako se pise triger, makar na nebuloznom primeru.

U papirnom knjigovodstvu postoji dbevnik transakcija i kartica racuna. U bazama podataka dovoljan je dnevnik transakcija. Lista transakcija za svaki racun se moze uvek dobiti iz tabele TRansakicje jednostavnim kverijam:
Code:

SLECT
*
FROM Transakcije
WHERE brRacuna  = <neki racun>
ORDER BY VremeTRansak, SifraVrsteTranskcije


Stanje na racunu se uvek moze dobiti na slican nacin:
Code:

SELECT 
brRacuna
, UkupnoDugovanje = SUM(CASE 
                            WHEN SifraVrsteTranskcije = 'Duguje' THEN iznos 
                            ELSE 0 
                        END)
, UkupnoPotrzaivanje =  SUM(CASE 
                                WHEN SifraVrsteTranskcije = 'Potrazuje' THEN iznos 
                                ELSE 0 
                            END)
, STanje = SUM(CASE WHEN SifraVrsteTranskcije = 'Duguje' THEN iznos ELSE 0 END) 
            - SUM(CASE WHEN SifraVrsteTranskcije = 'Potrazuje' THEN iznos ELSE 0 END)
FROM Transakcije
WHERE brRacune = <neki racun>
GROUP BY brRacuna


Ako hoces da vidis stanje za sve racune, izbaci WHERE. Znaci, sve iamo sto imamo u tabeli Karticaracuna. Cemu onda sluzi tabela KarticaRacuna?

Znaci bespotrebno se cuvaju podaci o transakciji na dva mesta, u tabeli Transakcije i tabeli KartciaRacuna. I treba ti naravno triger da prebaci podatke iz tabeleTRansakcije u tabelu kartcicaRacuna, da bi podaci bili sinhronizovani. U KarticaRacuna se naravno nepotrebno pamti i stanje posle svake transakcije, to je izracunati podataka, a izracunati podaci se ne cuvaju u tabelama, oni se izracunavaju u kverijima. Da ne duzimo, dizajn baze ne valja.

Da predjemo na triger. U dobro projektovanim bazama, potreba za trigerima je minimalna. Triger ti treba kad je baza de-normalizovana, da bi se odrzavali podaci na dva mesta - kao sto je ovde slucaj. OK, imamo denormalizovanu bazu pa nam treba triger. Ako se vec pise triger, treba pokusati da se napise bez kursora. Kursor u trigeru ubija performanse baze podataka. Zato se izbegava.

Sad, pitanje je sta je cilj tvog zadatka:

a) napisati triger koji odrzava tabele Transakcije i KarticaRacuna u sinhronizaciji, napisati triger na bilo koji nacin
ili
b) napisati triger, ali tako da se koristi kursor

Da li zelis a a) ili b) ili oba. ja bih radije da ti pokazem a), a ko bas mora, onda i b).

Odgovori, pa da pisemo triger.
[ nemanjal @ 16.09.2010. 21:12 ] @
Moze pod b), odnosno triger sa kursorom. Zidar, a tebi puno hvala na razumevanju i strpljenju. Ovo mi je jako bitno, jer bez toga ne mogu da polozim ispit.

[Ovu poruku je menjao nemanjal dana 16.09.2010. u 22:59 GMT+1]

[Ovu poruku je menjao nemanjal dana 16.09.2010. u 23:00 GMT+1]
[ Zidar @ 17.09.2010. 20:39 ] @
Evo, ali samo da bi polozio ispit.

Kreiranje tabela:
Code:

IF Object_ID('vrstaTransakcije') IS NOT NULL DROP TABLE vrstaTransakcije
GO
CREATE TABLE vrstaTransakcije
(
sifVrsteTransak integer not null PRIMARY KEY,
nazVrsteTransak varchar(40) UNIQUE,
tipTransak char(1) UNIQUE
)
GO
INSERT INTO vrstaTransakcije(sifVrsteTransak,nazVrsteTransak,tipTransak)
                    VALUES    (    1,                'Duguje',    'X');
INSERT INTO vrstaTransakcije(sifVrsteTransak,nazVrsteTransak,tipTransak)
                    VALUES    (    2,                'Potrazuje',    'Y');

SELECT * FROM vrstaTransakcije
GO

IF Object_ID('dnevnikTransakcije') IS NOT NULL DROP TABLE dnevnikTransakcije
GO
CREATE TABLE dnevnikTransakcije
(
ID_rad integer not null ,
RedniBroj INT IDENTITY not null PRIMARY KEY, -- broj transakcije
DatTransak Datetime not null,
--vremetransak datetime not null,
sifVrsteTransak integer not null, -- definse gde ide Iznos
brRacuna varchar(16) not null, -- ovom racunu se pristupa
iznos decimal(12,2) not null, -- iznos ide u Dugovanje ili Potrazivanje
napomena varchar(100) null,
CONSTRAINT FK_dnevnikTransakcije_VrstaTransakcije 
    FOREIGN KEY (sifVrsteTransak) REFERENCES vrstaTransakcije (sifVrsteTransak),
CONSTRAINT CK_dnevnikTransakcije_Iznos CHECK (iznos>0)
)
GO

-- Kreirajmo tabelu KarticaRacuna
IF Object_ID('KarticaRacuna') IS NOT NULL DROP TABLE KarticaRacuna
GO
CREATE TABLE KarticaRacuna
(
-- Ovo su kolone koje se prepisuju iz tabela dnevnikTransakcije: 
RedniBroj integer NOT null PRIMARY KEY,    
DatTransak datetime not null,
sifVrsteTransak integer not null,
brRacuna varchar(16) not null,

-- Kolona dnevnikTransakcije.Iznos se prepisuje u jednu od ove dve:
iznosDugovanja decimal(12,2) null,
iznosPotrazivanja decimal(12,2) null,

-- Stanje menjamo zavisno od dnevnikTransakcije.Iznos 
stanje decimal (12,2) null,
CONSTRAINT CK_DugPot_nisu_oba_NULL 
    CHECK (NOT(iznosDugovanja IS NULL AND iznosPotrazivanja IS NULL)),
CONSTRAINT CK_DugPot_nisu_oba_NOT_NULL 
    CHECK (NOT(iznosDugovanja IS NOT NULL AND iznosPotrazivanja IS NOT NULL))
-- nedostaje FOREIGN KEY na dnevnikTransakcije, to uradi za vezbu :-)
)
--USE transakcije
GO



Evo ga triger koji prepisuje redove iz tabele DnevnikTransakcije u tabelu karticaRacuna.
Triger poziva stored proceduru u kojoj se nalazi kursor koji izracunava stanje.

Code:

--- Ovaj triger prepisuje sadrzaj 'inserted' u tabelu KarticaRacuna:
IF Object_ID('trg_dnevnikTransakcije_upis_u_Karticuracuna') IS NOT NULL 
                DROP TRIGGER trg_dnevnikTransakcije_upis_u_Karticuracuna
GO
CREATE TRIGGER trg_dnevnikTransakcije_upis_u_Karticuracuna
ON dnevnikTransakcije FOR INSERT
AS
-- Ovaj triger prepisuje sadrzaj 'inserted' u tabelu KarticaRacuna
-- Posle koristimo kursor da uradimo update za stanje u karticaRacuna.
-- Kursor se nalazi u stored proceduri, koju triger poziva
PRINT 'Trigger: trg_dnevnikTransakcije_upis_u_Karticuracuna'
-- Ovo je mnogo jednostavnije nego kursor:
INSERT INTO KarticaRacuna 
        (RedniBroj ,DatTransak ,sifVrsteTransak ,brRacuna, iznosDugovanja ,iznosPotrazivanja )
SELECT  RedniBroj ,DatTransak ,sifVrsteTransak ,brRacuna 
        ,iznosDugovanja = CASE WHEN sifVrsteTransak = 1 THEN iznos ELSE NULL END 
        ,iznosPotrazivanja = CASE WHEN sifVrsteTransak = 2 THEN iznos ELSE NULL END -- potrazivanje
FROM inserted 
;
-- Pokazimo sta je sada u tabeli Kartica_Racuna:
--SELECT * FROM KarticaRacuna

-- Kursor cemo iskoristiti da izracunamo stanje u tabeli Kartica_racuna
-- Kursor smo stavili u stored proceduru, da bi triger bio citljiviji
-- i da bi mogli kod za hkursor na miru da testirami i popravljamo greske
-- Ko hoce, moze kod iz kursora da prebaci u triger, ali to nije preporucljivo

-- Azuriramo stanje:
EXECUTE Azuriranje_KarticaRacuna_Stanje

KRAJ_TRIGERA:
GO


Evo je stored procedura koja koristi kursor da izracuna stanje:
Code:

CREATE PROCEDURE Azuriranje_KarticaRacuna_Stanje (@Debug int = 0)
AS
/*    Ova procedure sadrzi kursor koji izracunava stanje na kartici racuna.

    Ceo kod je mogao biti uguran u trigger, ali bi triger bio veoma necitljiv.
    Nijedan kod ne proradi iz prve. Procedure se mnogo lakse testiraju i greske 
    se mnogo lakse ispravljaju u procedurama nego u trigerima.

    Na kraju, ceo ovaj posao je nepotreban jer pocetni dizejn tabela ne valja.
    Stanje je nepotrebno izracunavati posle svakog unosa, jer to lako moze da uradi 
    kveri onda kada zatreba. Ako je kveri spor, moze se napisati stored procedura
    koja koristi kursor, jer je za velike brojeve redova kursor brzi od kverija
    za neke slucajeve (izracunavanje stanja na primer)

    U svakom slucaju, tnje en treba upisivati u kolonu i odrzavati trigerom.
    Ako ima mnogo unosa u tabelu, kursor se stalno aktivira i tabela ce biti zakljucana
    vise nego otkljucana.

    Poziv procedure: 
        - iz trigera: EXECUTE Azuriranje_KarticaRacuna_Stanje
        - iz SQL editore, kad testiras: EXECUTE Azuriranje_KarticaRacuna_Stanje @Debug=1

*/
-- U ove varijable cemo upisati vrednosti iz kolona u tekucem redu kursora
DECLARE @RedniBroj integer ,    
        @DatTransak datetime ,
        @sifVrsteTransak integer ,
        @brRacuna varchar(16),
        @iznosDugovanja decimal(12,2) ,
        @iznosPotrazivanja decimal(12,2) ,
        @stanje decimal (12,2)  
;

-- Ove ce nam trebati za proracun
DECLARE @Current_brRacuna varchar(16), @Current_Stanje decimal(12,2)

-- sad kursor
DECLARE MyCursor CURSOR
FOR
    SELECT RedniBroj, DatTransak, sifVrsteTransak
        , brRacuna, iznosDugovanja, iznosPotrazivanja, stanje 
    FROM KarticaRacuna
--    WHERE Stanje IS NULL
    ORDER BY brRacuna, DatTransak

-- ovo je vazno, inicijalizujemo stanje
SET @Current_Stanje = 0

-- Za svaki red u kursoru izracunamo stanje kao SUM(Duguje_ - SUM(Potrazuje) do tog datuma
SET NOCOUNT ON
OPEN MyCursor

-- Ucitaj prvi rekord iz kursora:
FETCH MyCursor INTO     @RedniBroj  ,    
                        @DatTransak  ,
                        @sifVrsteTransak  ,
                        @brRacuna ,
                        @iznosDugovanja  ,
                        @iznosPotrazivanja  ,
                        @stanje 
;

                          
WHILE @@FETCH_STATUS = 0 
    BEGIN
------- zapamtimo za koji racun cemo da racunamo stanje
        SET @Current_brRacuna =  @brRacuna

------- Ovde se radi izracunavanje i azuriranje stanja
        SET @Current_Stanje = @Current_Stanje + COALESCE (@iznosPotrazivanja,0) - COALESCE(@iznosDugovanja,0)
        UPDATE KarticaRacuna SET Stanje = @Current_Stanje
        WHERE RedniBroj = @RedniBroj

-- Ovo je zbog testiranja, moze da se izbaci ceo SELECT
IF @Debug = 1
        SELECT 'Current racun i stanje pre proracuna'
                , [@Current_brRacuna] = @Current_brRacuna
                , [@Current_Stanje] = @Current_Stanje
                , RedniBroj = @RedniBroj
                , DatTransak = @DatTransak
                , Duguje = @iznosDugovanja 
                , Potrazuje = @iznosPotrazivanja  
                , Stanje = @stanje
;

---- Ucitaj sledeci rekord iz kursora
        FETCH MyCursor INTO @RedniBroj  ,    
                        @DatTransak  ,
                        @sifVrsteTransak  ,
                        @brRacuna ,
                        @iznosDugovanja  ,
                        @iznosPotrazivanja  ,
                        @stanje 

-- Kad se promeni racun, stanje treba vratiti na nulu
        IF @Current_brRacuna <>  @brRacuna SET @Current_Stanje = 0
;                         
    END

CLOSE MyCursor
DEALLOCATE MyCursor
SET NOCOUNT OFF
;
GO



Evo da demonstriramo da sve radi kako treba:
Code:

DELETE KarticaRacuna
;
DELETE dnevnikTransakcije
;

-- Da unesemo nesto
INSERT INTO dnevnikTransakcije (ID_rad, DatTransak, sifVrsteTransak, brRacuna,iznos, napomena )
SELECT Id_Rad = 1,    DatTransak = GetDAte(),    sifVrsteTransak = 1
        ,brRacuna = 'R001',    iznos = 1120.35, NApomena = 'test pre trigera'
UNION
SELECT Id_Rad = 1,    DatTransak = GetDAte(),    sifVrsteTransak = 2
        ,brRacuna = 'R002',    iznos = 1120.35, NApomena = 'test pre trigera'
;

SELECT * FROM dnevnikTransakcije
;
SELECT * FROM KarticaRacuna ORDER BY brRacuna, RedniBroj

INSERT INTO dnevnikTransakcije (ID_rad, DatTransak, sifVrsteTransak, brRacuna,iznos, napomena )
SELECT Id_Rad = 1,    DatTransak = GetDAte(),    sifVrsteTransak = 1
        ,brRacuna = 'R001',    iznos = 2120.35, NApomena = 'test pre trigera'
UNION
SELECT Id_Rad = 1,    DatTransak = GetDAte(),    sifVrsteTransak = 2
        ,brRacuna = 'R002',    iznos = 2120.35, NApomena = 'test pre trigera'
;

INSERT INTO dnevnikTransakcije (ID_rad, DatTransak, sifVrsteTransak, brRacuna,iznos, napomena )
SELECT Id_Rad = 1,    DatTransak = GetDAte(),    sifVrsteTransak = 1
        ,brRacuna = 'R001',    iznos = 3120.35, NApomena = 'test pre trigera'
UNION
SELECT Id_Rad = 1,    DatTransak = GetDAte(),    sifVrsteTransak = 2
        ,brRacuna = 'R002',    iznos = 3120.35, NApomena = 'test pre trigera'
;

--EXECUTE Azuriranje_KarticaRacuna_Stanje





[ nemanjal @ 18.09.2010. 16:31 ] @
Svaka ti cast. Nisam imao internet juce pa nisam odgovarao na poruku. Primer je super. u prvom trenutku mi se cinilo da je malo komplikovano, ali mi je sada jasnije. Trenutno ga malo vezbam sa jos jednim primerom. Hvala ti puno zidar.

Nego imam jos jedan mali problem. Imam zadatak u kojem se trazi da se prilikom Update kolone "datum_razrocenja date" okrene triger koji ce izracunati novo stanje na racunu. U tabeli "racun", samo mi poslednja kolona nije ispunjena, i ona treba da se azurira iskazom Update, a na taj dogadjaj ce da opali trigr i izracuna stanje u tabeli "RACUN_PROMET".
Prilikom pokretanja naredbe azuriranja "update "tabela" SET datum=getdate WHERE racun= ..'' trebalo bi da se aktivira se triger, izracunaju neke vrednosti i prepisu u novu tabelu.E sada, kada ja pokrenem tu naredbu, dobijam poruku o gresci:


Msg 8115, Level 16, State 8, Procedure promeniDatumRazrocenja, Line 38
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.


Ggreska ukazuje na na kodnu liniju gde se racuna kamata. Nasao sam gresku. Tip podatka za kamatu je bio @kamata decimal(4,2)
a ja sam ga promenio na @kamata decimal (16,2) i to je to.

Medjutim, naisao sam na drugi problem. Nece da mi isnertije @NovoStanje u kolonu "stanje " tabele RACUN_PROMENA.

Javlja mi ovu gresku:

Msg 515, Level 16, State 2, Procedure promeniDatumRazrocenja, Line 43
Cannot insert the value NULL into column 'stanje', table 'Racuni_Orocene_Stednje.dbo.racun_Promet'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Evo tabela

CREATE DATABASE Racuni_Orocene_Stednje
go
USE Racuni_Orocene_Stednje
go

CREATE table racun
(
broj_racuna varchar(20) PRIMARY KEY not null,
sifra_klijenta varchar(20) null,
datum_orocenja date not null,
Orocen_iznos money NOT NULL,
sifra_valute char(3) null,
period_orocenja date null,
godisnja_kamatna_stopa decimal(4,2),
datum_razrocenja date
)

go

CREATE TABLE racun_Promet
(
broj_racuna varchar(20) not null,
redni_broj_promene int not null,
datum_promene date not null,
iznos_u_korist money null,
iznos_Na_teret money null,
stanje money not null,
storno money null
)

ALTER TABLE racun_promet
ADD CONSTRAINT PK_racunpromet PRIMARY KEY (broj_racuna, redni_broj_promene)


---- moj kod.... Opet nesto skolski..


USE Racuni_Orocene_Stednje
GO

CREATE TRIGGER promeniDatumRazrocenja ON racun
AFTER UPDATE
AS
DECLARE @broj_racuna varchar(20),
@datum_orocenja date,
@orocen_iznos decimal(16,2),
@sif_klijenta varchar(20),
@sif_valute char(3),
@godisnja_kamatna_stopa decimal(4,2),
@datum_razrocenja date,
@kamata decimal (4,2),
@brDanaOrocenja int,
@novoStanje decimal(16,2),
@stanje decimal(16,2),
@Redni_broj_promene int,
@datum_promene date

SET @NovoStanje = 0

IF UPDATE(DATUM_RAZROCENJA)
BEGIN
DECLARE racunpromet CURSOR
FOR SELECT broj_racuna, datum_orocenja, orocen_iznos, godisnja_kamatna_stopa, datum_razrocenja
FROM inserted
OPEN racunpromet
FETCH racunpromet
INTO @broj_racuna,@datum_orocenja, @orocen_iznos,@godisnja_kamatna_stopa, @datum_razrocenja

WHILE @@FETCH_STATUS = 0
BEGIN
------------------------------------------------------------------------
SELECT @Redni_broj_promene = max(redni_broj_promene)FROM racun_Promet


SELECT @STANJE = stanje FROM racun_promet where BROJ_RACUNA=@BROJ_RACUNA
set @brDanaOrocenja = datediff(dd, @datum_orocenja, @datum_razrocenja)
set @datum_promene = getdate()

set @kamata = @orocen_iznos*@brDanaOrocenja/365 *@godisnja_kamatna_stopa/100
set @novoStanje= @stanje+ @kamata

INSERT INTO racun_Promet
(broj_racuna, redni_broj_promene, datum_promene,Iznos_u_korist, Iznos_na_teret, stanje, storno, vrsta_promene)
values (@broj_racuna, @redni_broj_promene+1, @datum_promene, 0,0, @novoStanje, 0, 154)

FETCH racunpromet INTO @broj_racuna, @datum_orocenja, @orocen_iznos, @godisnja_kamatna_stopa, @datum_razrocenja
END
close racunpromet
deallocate racunpromet
END


---- sada update

update racun
set datum_razrocenja='2010-10-31'
where broj_racuna='840-1045666-13' and datum_orocenja='2010-1-1'

-- Znam da je trenutno sa ovakvim azuriranjem ne potrebam kursor, ali se insistira na tome zbog demonstraicje znanja. Ne zamerajte mi zbog toga. Pozzz




[Ovu poruku je menjao nemanjal dana 18.09.2010. u 17:45 GMT+1]

[Ovu poruku je menjao nemanjal dana 18.09.2010. u 17:56 GMT+1]

[Ovu poruku je menjao nemanjal dana 18.09.2010. u 18:03 GMT+1]

[Ovu poruku je menjao nemanjal dana 18.09.2010. u 18:08 GMT+1]
[ nemanjal @ 19.09.2010. 19:50 ] @
resio sam problem. Zidar mi je u jednom od predhodnim postova objasni o cemu je problem kada se javi takva greska. Ne mogu iz prazne tabele ( u kojoj inace hocu da upisem RACUN_PROMENE) izvucem vrednost "STANJA". Tek naknadnih popunjavanjem nekih kolona u drugoj tabeli u kojoj se evidentiraju promene sam uspeo da popunim polja, a na prethodni poziv azuriranja. Nisam u opste razisljao. U svakom slucaju hvala.
[ MarkoBalkan @ 08.10.2010. 17:57 ] @
od zidara:

Kako bi npr sa standardnim select statementom uradila sledece.
select * from tblUsers where gender is not null
onda, zavisno od pola, ubaci taj record u tblMen ili tblWomen

Sa obicnim select to ne mozes (mozes ali iz 2 puta, prvo da selektujes sve gde je gender "man" i da to insertuejs pa onda jos jednom sa "women")


insert into tabela values(....)
select *,case.... from user where gender is not null