Trigger i procedura

Pozdrav,

poceo sam (napokon) nesto raditi u SQL-u (server 2005) i trigerri su mi se cinili kao dobro rijesenje za rijesavanje nekih stvari, ali onda ...
Citam na raznoraznim stranicama da ljudi izbjegavaju trigere i neki cak odlaze tako daleko da izjavljuju da se "dive" ljudima koji ih koriste??

E tu sam sad ostao u dilemi sta i kako???

1. Zasto triggeri "ne valjaju" - koliko u biti oni usporavaju cijeli proces INSERT, DELETE, UPDATE? (ovo mislim na primjeru iste stvari za trigger i procedure - dakle npr. brisanje ili insert 10 000 redova sa trigerrom i procedurom)?

2. Imam triger koji ide otprilike ovako:



E sad - kako bi to islo da se napravi procedura? Ovdje šifru dobijem iz "inserted.sifra from inserted", a kako bi je dobio za proceduru, a da je ne moram uvijek slati iz programa :) ako je moguce.

Unaprijed hvala na izdvojenom trudu za odgovor :)

Nekada davno, baze podataka nisu znale da uspostave referencijalni integritet. Nije bilo Foreign Key i trebalo je nekako uspostaviti kontrolu nad podacima.

Trigeri su se koristili za stavri tipa 'ne moze da se unese red u tabelu Dete ako ne psotoji red u tabeli Roditelj'. Danas to radi FOREIGN KEY constraint. Takodje, radile su se stvari tipa 'kad obrisem red iz tabele Roditelj , hocu da se automatski obrisu svi odgovarajuci redovi u tabeli Dete'. Danas to takodje radi FOREIGN KEY, kad mu se doda 'ON DELETE CASCADE' ili 'ON UPDATE CASCADE'. Znaci, otpala je potreba da se pisu trigeri koji ce to da rade.

Teorija kaze da u dobro projektovanoj bazi (normalizacija), nema potrebe za trigerima. To je teorija. U praksi, baze cesto nisu potpuno normalizovane, bilo namerno ili nenamerno. Na primer, banka ima dve tabele Account i AccountTransactions. Po teoriji, balans (stanje) na racumu (Account) se ne cuva u bazi, nego se racuna kad zatreba. U praksi, desava se da je broj transakcija toliko veliki da izracunavanje balansa postaje sporo, ili bar tako ljudi veruju. Zbog toga neki vole da u tabeli Account imaju kolonu Balance koja se azurira kad god se unese red u AccountTransactions. U AccountTransactions se stavi triger koji radi UPDATE za tabelu Account. Isti triger nmoze da proveri da li nova transakcija nece mozda da anrusi pravilo d abalans ne sme da id u minus, ili da ide u minus previse ('dozvoljeni minus'). Deo ovoga moze da se postigne i bez trigera (kontrola balansa), a vodjenje stanja stalno je diskutabilan zahtev. Obicno se stanje vodi konstantno upravo da bi se proverilo pri svakoj transakciji da se mnmalni balans ne narusi. ako to vec mozemo da postignemo na drugi nacin, zasto bismo uopste i vodili stanje stalno?

Ovde imas ideju i primer kako se umesto trigera mogu upotrebiti CHECK CONSTRAINTS:

http://www.baze-podataka.net/2...-funkcija-u-check-constraints/

Zavisno od toga koliko ti je baza ne-normalizovana, namerno ili nenamerno, imaces vecu ili manju potrebu z atrigerima.

Ima situacija kad su trigeri zaista potrebni. Na primer, cuvanje istorije. Pre nego se desi promena (UPDATE, DELETE) u tabeli, redovi koji se menjaju/brisu salju se u tabelu Istorija. Na samoj tabeli IStorija ne zelimo nikakve promene, ni UPADTE ni DELETE. To nam omogucuje triger FOR UPADTE, DELETE na tabeli Istorija, koji ima smo jednu anredbu - ROLLBACK.

Drugi slucaj je 'zakljucavanje' redova u tabelama. Na primer, imas u tabeli redove iz vise godina. Onda zelis da se mogu menjati redovi samo iz tekuce godine, da sve prethodne godine budu 'zakljucane'. Onda moze da se napise triger koji ce da ROLLBACK sve promene za zakljucanim redovima.

Trece je popunjaanje kolona 'UserUpdated'. Na UPDATE se napise triger koji kolonu UserUpdated popuni sa recimo UseName()

Dalje od toga, nema neke potrebe za trigerima. Naravno, ako znate kako to drugacije moze da se uradi. Ko ne ume - mora da pise triger. Ko ima cekic, sve mu izgleda kao ekser.

Problem sa trigerima je sto su mnogo tezi za pisanje, i sporo se izvrsavaju. Naoko je lako napisati triger, ali nije. Opasno je verovati da se triger lako pise zato sto negde pise daje 'triger samo stred procedura koja se izvrsava na nivou tabele'. A mi smo programeri, umemo da pisemo kod => umemo d apisemo i stored procedure (zista ) pa sledi da umemo da pisemo i trigere. A brzina? Ma samo neka radi, za brzinu cemo da dodamo RAM i da optuzimo hardver. Tako mali Djokica zamislja stvari.

Eto recimo, triger koji si ti napisao u ima jednu opasnu manu - radi samo za jedan red. Ako se uradi neka operacija nad vise redova, tvoj trigre ce da krasira. Zasto? Ova linija

predpostavlja da je u tabeli inserted tacno jedan red. To je veoma nerealna pretpostavka. Zasto bi se procesirao jedan po jedan red? Kad bi umeo da napravis JOIN izmedju INSERTED i KNJIGE, mogao bi da procesiras i vise redova. Posto nije lako naterati i testirati triger, pametni ljudi to izbegavaju koliko god mogu. Vidis, SQL nije namenjen za proceduralno razmisljanje (jeden po jedan red), nego se akcije desavaju nad skupom redova. Tek tada SQL postaje mocno oruzje. Bez toga, SQL je samo obicno skladiste podataka, neefikasno uz to.

Zasto su trigeri ipak popularni? Zato sto danas malo ko zaista uci SQL. Ljudi uce programiranje, .NET, C##, objektna zavrzlama, Java itd, koje je jedan proceduralni proces. Onda programerske vestine pokusaju da prenesu u SQL. Otud trigeri, otud kursori. Ljudima se to cini poznato i lakse im je da napisu triger ili kursor, nego da uce SQL, koji ima potpuno drugaciju logiku od proceduranog programiranja. Nije retko da se u triger stavi kursor i redovi procesiraju jedan po jedan, u nekoj petlji. Absolutni horor. Zamislite brzinu izvrsavanja i probleme sa zakljucavanjem tabela i redova. SQL se koristi tamo gde se biti na stotine korisnika i bezbroj transakcija. Ako svaki korisnik podnese transakciju, a onda triger to sve mora da procesira jedno po jedno, ode mast u propast, a korisnici cekaju li cekaju.

Dakle, glavni razlog za koriscenje trigera naveliko je nenzanje, koga ima dve vrste. Vrsta 1: naucio sam pre 20 godina da psiem trigere i to mogu brzo da uradim bez gresaka, pa zasto bih se mucio da naucim nesto drugo? Vrsta 2: ja i ne zanm SQL, ali znam da programiram, pa zasto to znanje ne bih iskoristio, nemam vremena da ucim SQL. Vrsta 1 je manje opasna, jer covek nesto zna, process na kraju karejva radi, samo je to znanje manje efikasno od neceg drugog. Vrsta 2 je mnogo cesca nazalost, i mnogo opasnija. Ne znas, a ne znas da ne znas.

Nadam se da je sada malo jasnije.

Uf - a sta reci na ovo osim - skidam kapu :)
I da sam platio nebi dobio iscrpniji odgovor.

Jos jednom hvala na odgovoru :)

@zidar

ali ako imaš recimo baze u replikaciji, na master-u ide unos, update i delete .

na 1.slave-u se nalaze trigeri s kojima se recimo pripremaju podaci za izvještaje, pošto bi query jako dugo trajao.

pa se postavlja pitanje, da li trigeri na slave-u ili složeni query-i na slave-u?

ja sam uvijek rađe za trigere na slave-u jer onda imaš uvijek gotove podatke za izvještaje, a ovako bi svaki put trebao čekati da ti se izvrši query.

a recimo na drugom slave-u jednostavi query-i, za pretraživanje i sl.

@Marko: Ukoliko radi dovoljno brzo i pouzadno, OK je. Ti prakticnu za vreme replikacije trigerima punis neku agregatnu tabelu, iz koje se izvlace izvestaji. To moze, pod uslovom da ne usporava repliakciju. Ako usporava, onda nece ici. Ali, uvek moze u zakazano vreme da se odradi procedura koja ce da popuni ili azurira agregatnu tabelu novim podacima, pa si na kraju na istome.

Kad analiziramo moguce resenje, treba svakako gledati dve stvari - efikasnost i pouzdanost. U smislu efikasnosti, bolje je resenje s trigerima, jer se sve odmah desi. Medjutim, ako se desi neki problem na agregatnoj tabeli, pa triger mora da rollback transakciju, sta se onda desava, da li se replikacija prekida? Proizilazi da u smislu pouzdanosti, resenje sa trigerima nosi veci rizik. Rizikujes replikaciju zbog moguce greske na agregatnoj tabeli. Primeti da agregatna tabela uopste nije deo replikacije, ali si je kroz triger de fakto ukljucio u replikaciju. Ukoliko krah replikacije nije kritican za celu operaciju, onda je OK, u suprotnom, treba dobro odvagati rizike u pouzdanosti i dobit na efikasnosti.

Poenta price je da trigeri mogu lako da uticu na ceo proces, a to znaci na vise tabela i onda se stvari veoma komplikuju. Cak i ako je relativno lako napisati konkretan triger. Tu i jeste problem, pisanje trigera se nauci, ali se lako previdi uticaj na ceo sistem, ako ga ima.

U svakom slucaju, trigeri nisu zlo sami po sebi. To je kao vatra i voda, dobre sluge ali zli gospodari. Ako umes da ih kontrolises, onda si OK. Stvarni problem je kad ljudi ne razumeju bazu, SQL ili proces, a umeju da pisu kod. Onda dobijes nesto sto naoko radi, a u sustini ne znas sta radi. Kao kad zapalis cigaru na benzinskoj pumpi. Ne izazoves eksploziju odmah, kao na filmu, ali nikad se ne zna. Tako i sa trigerima.

:-)

ima x načina.

što se tiče ovog u replikaciji sa trigerima.treba postaviti pravilo da se ništa ne smije brisati iz baze, ako se briše onda se briše i iz agregatne tabele preko trigera.


drugi način je dwh i da ga puniš na dnevnoj bazi.

original tabele da imaju datum upisa.
a dwh da ima datum upisa i datum za koji neki podatak vrijedi.
pa onda iz skladišta podataka vučemo query-e koji filtriramo po datumu i punimo agregatne tabele, također na dnevnoj bazi.

Koji je najlaksi nacin da se nauce trigeri i procedire. Neophodno mi je to da naucim u sto kracem vremenskom roku zbog polaganja ispita. Ako neko ima dobru dokumenaciju neka mi posalje na mail ili ako dobro poznaje materiju i raspolozen je da mi pomogne neka mi se javi ma mail [email protected] Unaprijed zahvalna!

U vezi situacija kad su trigeri zaista potrebni
'Drugi slucaj je 'zakljucavanje' redova u tabelama. Na primer, imas u tabeli redove iz vise godina. Onda zelis da se mogu menjati redovi samo iz tekuce godine, da sve prethodne godine budu 'zakljucane'. Onda moze da se napise triger koji ce da ROLLBACK sve promene za zakljucanim redovima.'

Da bih izbegla trigger-e u ovoj situaciji, bas kao sto je u primeru sa godinom, pocela sam da primenjujem particioniranje tabela (cak i kad te tabele nisu velike, S jedne strane imam podelu tih redova prema godinama, s druge strane sve godine osim tekuce su u filegrupi ('Arhiva') koja je ReadOnly.

A kazu da je namena particioniranja bas za replikaciju i velike tabele, i da na particioniranoj tabeli query manje traje, tako da ako je moguce particionirati prema onome sto je uslov za spremanje izvestaja...

Moje razmisljanje u vezi zakljucavanja redova gde se mogu brisati i menjati zapisi samo od strane korisnika koji ih je uneo, iako se isto moze definisati na nivou procedure, da zbog preglednosti u Studio Managmentu, gde su trigeri za datu tabelu u njenom 'folderu', dok stored procedura treba da je dovoljno asocijativna da se odnosi na obican Delete ili Update konkretne tabele i treba se traziti u mnostvu stored procedura; a i zbog eventualnog ukidanja datog pravila, mesto definisanja i menjanja, komentarisanja istog u proceduri, dovoljno je Disable i Enable triggera.

Tako da najbolje je kad bi sve nekako bilo na nivou tabele, i dosta se ka tome stremi, a do tada resenja su kombinacija i jednog i drugog, vec prema potrebi, znanju i ukusu.

Particionisanje nije prevencija za slucajno brisanje, ni aktivnog seta ni pasivnog seta. U najoptimalnijem slucaju na kraju godine ces morati da skines readonly sa arhiva filegrupe da bi prebacila tada gotovu aktivnu godinu u arhivu i tad kad se rade najintenzivnij batch operacije baza ti je pod rizikom. Tako da ces opet morati da imas trigere na svim particijama. Imaj isto u vidu da je particija optimalno resenje ako i samo ako query-ji ukljucuju where klauzulu po particionisanom kljucu (tako da optimizer moze deterministicki da iskljuci arhiva particiju kad "odmota" view). Ukoliko to nije slucaj, particionisane tabele funkcionisu sporije od jedinstvenih (svaki index ili table scan mora da se radi duplo).


A sto se tice ostatka posta, ne kontam jel pitas nesto ili kazujes nesto?

---

Ne bih da polemisem, al kod mene je prevencija, znaci ne mogu da brisem, menjam arhivne zapise particionisane prema godini. A to sto cu morati 'da skinem readonly sa arhiva filegrupe da bi prebacila tada gotovu aktivnu godinu u arhivu', mislim da se to radi jednom, i da se bira trenutak. Zar necu morati i uslov trigera da menjam kad tekucu godinu prebacujem u arhivu? Cak je potrebno i Constraint na koloni godina promeniti. Slazem se jednostavnije je raditi promene na trigeru, al ja govorim i o situaciji ako vec neko ima potrebu bilo o horizontalnoj ili vertikalnoj particiji tabele, kako je moguce izbeci trigere.
A da nije prevencija za slucajno brisenje aktivnog seta (koji nije u Read Only FileGroups), da neko na primer izbrise zapise koje nije uneo, ipak je potreban triger(il stored procedura) slazem se, al to je vec druga vrsta uslova (cije jedino resenje je triger?).
Normalno da imam u vidu da je particija optimalno resenje ako i samo ako query-ji ukljucuju where klauzulu po particionisanom kljucu (tako da optimizer moze deterministicki da iskljuci arhiva particiju kad "odmota" view), to valjda podrazumeva i Partition Index.
Mozda bi nova tema mogla biti Partitioned Tables i iskustva koja imamo sa istima.
Sto se tice ostatka posta samo kazujem, :) ako je dozvoljeno.
Inace da ono sto procitam na ovom sajtu cesto nalazim za zgodno i shodno primeniti ili cak kao ideju prihvatiti. Eto primer poslednje, za calc polje iz teme izracunavanje-vrednosti-pomocu-triggera. Tako da nekad i ne trazeci odgovor na konkretan problem, mozemo iz tudjih problema primeniti.

Postoji termin koji se zove "trigger-happy DBA". Veoma je zahebano raditi sa trigger-happy DBA ljudima. Oni obicno i dolaze na fiks ideje da trpaju trigere kao mirodjiuju u svaku corbu ne libeci se cak ni od monstruoznosti tipa dinamickih kurzora u trigerima. Ako se ne drze pod kontrolom mogu ozbiljno da upropaste performanse bazi.

Prevenciju gubis onog trenutka kad skines readonly atribut, promena godine, narocitu tako u adhoc bazama i okruzenjima posle ukljucuje DBAove koji manuelno kopiraju podatke, sanse da ce u tom trenutku nesto sprziti su velike, cime ponistavas sav taj minuli period dok je arhiva bila zakljucana. Prici particionisanju tabele SAMO da bi sprecila brisanje je LOS pristup, to je sve sto ja kazem. Particionisanje ima potpuno drugaciji rezon iza sebe i potpuno drugacije okolnosti (high volume OLTP + history) i potpuno drugacije odrzavanje (koje vec znas sta ukljucuje), mozemo otvarati novu temu i ne moramo ali za ovu tvoju primenu nije. Samo zato sto readonly filegroup + partitioning moze da se koristi za arhiviranje ne znaci da je uvek idealno resenje. Za tebe ovde, kao i za 99% ostalih primena gde baze ne ukljucju stotine miliona transakcija godisnje, idealno resenje je ref integrity, history-lock polje i index na datetime polju. Sje*avati performanse baza iz licnih strahova da ces nesto obrisati NIJE dobro.

Row-level security ne postoji u SQLu i ako ti je aplikativni nivo iole dobro napisan niko se od korisnika nece na bazu logovati sa dbo/sa niti ce se iko od korisnika logovati na bazu van aplikacije, tako da je bespredmetno i nepotrebno usporavati kompletnu bazu zbog row-ownership provere koju ionako MORAS da uradis na aplikativnom nivou. Postedi SQL server tog maltretiranja i pusti ga da radi ono za sta je namenjen.

---

Ovo vec postaje licno i provlaci se iz neke druge teme. Kao prvo ja uopste nisam "trigger-happy DBA" a nadam se da necu ni postati. Samo pokusav reci da su situacije, misljenja razlicita, ako imamo pravo na njih. Umesto kritike o trpanju triggera, koje inace izbegavam, o losim stranama particioniranja, bilo bi lepo, cuti drugu alternativu za neke situacije (history, pa i ROW level security, pa bas i zbog situacija da aplikativno nivo moze biti iole lose napisano, ili ga treba promeniti, na nivou baze) i ne radi se o licnim strahovima da cu ili da ce neko, greskom obrisati.
Opet kazem sta ako prilazite particioniranju iz drugih pobuda, i ako vam je tabela izmegju ostalog history tabela, dal tada mozete da iskoristite isto za eliminisanje
triggera.
Praksa se ne sastoji iz idealnih resenja. Znaci onemoguciti brisanje, izmene, prethodnih zapisa treba obezbediti na aplikativnom nivou, jer nije moguce na nivou referencijalnog integriteta, jedino ako se ne uradi transakcija(replikacija) arhivnih zapisa u dodatnu tabelu Arhiva ( a ta ista mora imati triger za sprecavanje DELETE, UPDATE pa i INSERT posle izvrsene transakcija).

trigeri su zgodni , ako se koriste na pravilan način.
recimo kod nas svaka godina ide u novu bazu kod knjigovodstvenih aplikacija.
ako se koriste neke druge aplikacije onda nema potrebe otvarati novu godinu, ali isto tako zabranjuje se brisanje i update svim korisnicima.
ako treba napraviti arhivu, admin uvijek ima full prava.
ne vidim u čemu je problem.
a ako se podaci arhiviraju kod knjigovodstvenih aplikacija,ostavi se prijašnja godina smao za čitanje, napravi se backup i počinje se razmišljati o data warehouse-u.

a ako je baza spora za x korisnika, onda ili nije optimizirana ili nije advekvatan hardware ili je aplikacija loše napisana.
ako je sve napravljeno kako treba, onda to i radi kako spada.

To sto kod Vas svaka knjigovodstvena baza ide u novu godinu, je Vasa odluka.
Predpostavlja, da to znaci da i u okviru jedne godine na aplikativnom nivou zakljucavate, eventualne mesecne slozene naloge; onemogucavate izmene nekim drugim aplikacijama iz kojih je mozda moguce automatsko knizenje prema podacima iz jedne tabele, u drugu tabelu. Nakon mesecnih izvestaja, na aplikativnom nivou onemogucavata izmene bilo kog tipa, jer ne zelite da neki novi izvestaj (prema eventualnom novom stanju u bazi) se razlikuje od vec produciranog, koji ste ocenili kao tacan, ma koliko i na koji nacin mogli da sacuvate prethodno producirani report.
A i predpostavljam da ste na aplikativnom nivou sinhronizovani i koordinisani , da se podaci iz Blagajne ili Materijalnog pogresno ne proknjize u finansijama, da Vasi korisnci te greske koriguje i storniraju na vreme. Finansisko storniranje je jos jedan razlog da na nivou tabele nema Delete, Update nego samo Insert, ako se to drukcije ne odluci.
Nema problema.

to što svaka godina mora biti u novoj bazi, to je po zakonu i svaki knjigovodstveni program mora zadovoljiti ovaj uvjet.
naravno da je jednostavnije sve držati u jednoj bazi.
ili šifarnike u jednoj schemi, a ostale podatke u drugoj schemi.
recimo ovo je zgododno na postgresql-u .