DAT 2800

1. DAT 2800 Databaser I Prosedyrer / Triggere

2. Eksempel på innhold i en database Database Views Procedures Rules Tables Datatypes Indexes Defaults Triggers

3. STORED COMMAND STORED COMMAND: Kompilert lagret SQL-kommando Eks fra SQLTalk: STORE SC_01 SELECT SNrID, Navn, PNr FROM Selger EXECUTE SC_01 SELECT … FROM SYSCOMMANDS WHERE NAME = ’…’ ERASE SC_01

4. PROCEDURE - Hva er en prosedyre ? En SQLBase prosedyre er et sett av: - SQL statements - SAL statementssom er tilegnet et navn, kompilert og vanligvis lagret i SQLBase.

5. PROCEDURE - Static / Dynamic En SQLBase prosedyre kan være: - Static Lagres ved parsing / prekompilering før eksekvering. - Dynamic Inneholder dynamisk SQL som parses/kompileres ved eksekvering og må ikke nødvendigvis lagres før eksekvering.

6. PROCEDURE - Ulike prosedyre-implementeringer - Stored procedure Kompileres og lagres i databasen for seinere eksekvering. Kan være statisk eller dynamisk. Triggere og Events kan defineres i lagrede prosedyrer. - Non-stored procedure Kompileres for umiddelbar eksekvering. - Inline procedure Benyttes vanligvis i triggere. INLINE clause i CREATE TRIGGER kommando.

7. PROCEDURE - Hvorfor prosedyrer ? - Forenkling av applikasjon ved overføring av prosessering til server - Reduserer nettverkstrafikk - Større sikkerhetsfleksibilitet - Bedring av runtime gjenfinningshastighet (performance) - Sentralisert database-administrasjon - Kan eksekveres fra ulike front-ends: SQLTalk, Team Developer, Web Developer, SQL/API, ...

8. PROCEDURE - Format PROCEDURE: ProcedureName Parameters … Local Variables ... Actions On Procedure Startup ... On Procedure Execute ... On Procedure Fetch ... On Procedure Close ... Utføres når følgende er oppfylt: 1. Kallende program kompilerer prosedyren. 2. Kallende program eksekverer prosedyren første gang. 1. Kallende program eksekverer prosedyren. 2. Procedure Startup er prosessert. 1. Kallende program utfører en Fetch kommando. Må finnes hvis multiple rader skal hentes. Benytt Return 0 ved Fetch Ok, 1 ved Fetch avsluttet. 1. Ved Disconnect kommando eller ved kompilering av ny kommando til samme SqlHandle. Viktig med innrykk. Kan benytte Begin / End. Hele Action-delen utføres hvis ingen On ProcedureState finnes.

9. PROCEDURE - Eksempel PROCEDURE: Selger Parameters Receive Number : nSNrID Receive String : sNavn Number : nPNr Local Variables Sql Handle : hSql String : sSelect Number : nInd Actions On Procedure Startup Call SqlConnect (hSql) Set sSelect = ‘SELECT SNrID, Navn INTO :nSNrID, :sNavn FROM Selger WHERE PNr = :nPNr’ Call SqlPrepare (hSql, sSelect) On Procedure Execute Call SqlExecute (hSql) On Procedure Fetch If NOT SqlFetchNext (hSql, nInd) Return 1 Else Return 0 On Procedure Close Call SqlDisconnect (hSql)

10. PROCEDURE - STORE Lagring av prosedyrer: STORE prName PROCEDURE prNameP STATIC/DYNAMIC ... prName og prNameP vil vanligvis (men må ikke) være like. DYNAMIC er default. bOk = SqlStore ( hSql, strName, strSqlCommand )

11. PROCEDURE - STATIC Static prosedyrer: Kompileres, optimaliseres (bestemmelse av applikasjonsplan) og lagres i databasen. Både statementene og applikasjonsplanen lagres i systemkatalogene. Static prosedyrer må lagres før eksekvering. Følgende krav må være oppfylt for Static prosedyrer: - Må ikke inneholde DDL statement (CREATE, ALTER, DROP, …) - Statementene er strenger som ikke inneholder variabler bortsett fra Bind- eller INTO -variabler.

12. PROCEDURE - Trekk ved Dynamic / Static prosedyre Trekk Dynamic Static Require storing to execute ? No Yes Parese / Precompile procedure logic ? Yes Yes Parse SQL at store time ? No Yes Precompile SQL at store time ? No Yes Dynamic SQL support ? Yes No SQL performance Slower Faster Use for Triggers and Events No Yes Performance of Dynamic procedures can be enhanced by retrieving previously stored SQL procedures/commands (SqlRetrieve as opposed to SqlPrepare).

13. PROCEDURE - Datatyper Datatype Default Kommentar Boolean FALSE Sql Handle Ingen Kun lokale variable Data/Time null String null string Også long string data Number 0

14. TRIGGER En trigger aktiverer en stored eller inline prosedyre som databasen automatisk eksekverer når en applikasjon forsøker å endre (INSERT / UPDATE / DELETE) data i en tabell. Forsøk på å endre data i en tabell aktiverer en eller flere triggere som er knyttet til denne kommandoen og denne tabellen. Tr_Insert_01 INSERT Tabell DELETE Tr_Delete_01 UPDATE Tr_Update_01 Tr_Delete_02

15. CREATE TRIGGER CREATE TRIGGER trigger name BEFORE AFTER UPDATE ON table name OF trigger column name ORDER sequence name DELETE INSERT REFERENCING OLD old value table name AS NEW new values table name AS NEW new value table name AS OLD old values table name AS (EXECUTE stored procedure name ( )) parameters INLINE ( ) procedure tekst parameters FOR EACH STATEMENT ROW

16. Trigger-plassering i DML Eksekverings-modell 1. Kontroller antall bind data 2. Validiter verdier som er del av statementet (dvs ikke bound data) Nullverdi-, og datatype-sjekking 3. Utfør sikkerhetskontroll 4. Hvis trigger er definert, eksekver BEFORE STATEMENT trigger 5. Loop for hver rad som berøres av SQL-statementet: - Validiter verdier hvis bound in (Nullverdi-, datatype- og lengde-sjekking) - Eksekver BEFORE ROW trigger - Utfør kontroll for duplikate verdier - Utfør referanse integritet kontroll - Eksekver INSERT/UPDATE/DELETE DML - Eksekver AFTER ROW trigger 6. Eksekver AFTER STATEMENT trigger

17. TRIGGER - Before Insert CREATE TABLE t1 (c1 int); CREATE TRIGGER tg1 BEFORE INSERT ON t1 (EXECUTE INLINE (c1) PROCEDURE p1 STATIC Parameters Receive Number: n Actions Set n = 5 ) FOR EACH ROW; INSERT INTO t1 VALUES (0); Endrer kolonne-verdien i c1 fra 0 til 5

18. TRIGGER - Before Insert CREATE TABLE t1 (c1 int, c2 int, c3 int); CREATE TRIGGER tg1 BEFORE INSERT ON t1 (EXECUTE INLINE (c1,c2,c3) PROCEDURE p1 STATIC Parameters Number: n1 Number: n2 Receive Number: n3 Actions Set n3 = n1 + n2 ) FOR EACH ROW; INSERT INTO t1 (c1,c2) VALUES (5,7); Setter kolonne-verdien i c3 til 5 + 7 = 12

19. TRIGGER - Before Insert CREATE TRIGGER tg1 BEFORE INSERT ON t1 (EXECUTE INLINE ( ) PROCEDURE p1 STATIC Local Variables Number: n2 Actions Set n2 = 7 Call SqlImmediate(‘INSERT INTO t2 VALUES ( :n2)’ ) FOR EACH ROW; INSERT INTO t1 (...) VALUES (...); Innsetter en rad i tabellen t2 før innsetting i tabellen t1. Hvis triggerinsettingen ble utført på t1 istedet for t2, så ville vi få en uendelig rekursjon (max 8 tillatt).

20. TRIGGER - Before Insert CREATE TABLE t1 (c1 int, c2 int, c3 int); CREATE TRIGGER tt1 BEFORE INSERT ON t1 CREATE TABLE t2 (d1 int, d2 int); (EXECUTE p1 (t1.c3,t1.c2)) FOR EACH ROW; STORE p1 Actions PROCEDURE p1 STATIC Call SqlConnect(hSql) Parameters Set sSelect = ‘SELECT t2.d2 INTO :nMin \ Number: n1 WHERE t2.d1 = :n1’ Number: n2 Call SqlPrepare(hSql, sSelect) Local Variables Call SqlExecute(hSql) Sql Handle: hSql Call SqlFetchNext(hSql, nFetch) Number: nFetch Call SqlDisconnect(hSql) Number: nMin If n2 < nMin Return 20000 Else Return 0; t2 d1 d2 101 3000 102 5000 103 9000 INSERT INTO t1 VALUES (1, 2000, 102) vil feile INSERT INTO t1 VALUES (1, 7000, 102) vil gå bra

21. TRIGGER - Before Update CREATE TRIGGER t1_updt BEFORE UPDATE OF c3 ON t1 REFERENCING OLD AS t1_old NEW AS t1_new (EXECUTE p1(t1_new.c3, t1_new.c2)) FOR EACH ROW; UPDATE t1 SET c3 = 103 WHERE c1 = 1 vil feile

22. TRIGGER - Before Delete Egenimplement. ref.int. CREATE TRIGGER t1_delete BEFORE DELETE ON t2 (EXECUTE INLINE (t2.d1) PROCEDURE RI_Rule STATIC Parameters Number: nt2 Local Variables Boolean: bExists Actions Call SqlExists(‘SELECT c3 FROM t1 WHERE c3 = :nt2’, bExists) If bExists Return 20001 Else Return 0 ) FOR EACH ROW; DELETE FROM t2 WHERE d1 = 102 vil feile

23. Trigger - Generelle restriksjoner - Triggere kan ikke benytte ikke-lagrede prosedyrer. STORE-kommandoen må benyttes. - Triggere kan ikke benytte dynamiske prosedyrer. - Aktuelle receive-parametre i EXECUTE kan kun være kolonner. - SQL @ funksjoner kan ikke inngå i parametre i prosedyrer som benyttes av triggere. - Det kan defineres max 16 triggere for hver kombinasjon av Table Event (INSERT, UPDATE, DELETE) Time (BEFORE, AFTER) Frequency (FOR EACH ROW, FOR EACH STATEMENT) Dette gir maksimalt 16 x 3 x 2 x 2 = 192 triggere for hver tabell.

24. Triggere og Prosedyrer Triggere kan kalle lagrede prosedyrer som igjen kan la SQLBase kalle andre triggere. Triggere kan nestes til og med 8 nivåer. Nesting utover 8 nivåer returnerer feil som igjen medfører rollback på den kommandoen som aktiverte triggeren.

25. Triggere og Prosedyrer Når triggere benyttes til å sette default- eller beregnede kolonneverdier ved INSERT/UPDATE, gjelder følgende: - Triggeren må være av typen BEFORE … ROW - UPDATE krever REFERENCING … NEW - Modifiseringskolonner må overføres som RECEIVE parametre.

26. Triggere og prosedyrer - Insert/Update/Delete Before/After - Insert / Before Kan benyttes til integritets-kontroll før innsetting samt setting av default/beregnede kolonne-verdier. Parameter-kolonner (men ikke raden) er synlig for trigger. Ved default/beregnede kolonne-verdier må receive-parametre benyttes. - Insert / After Kan benyttes til operasjoner på data som nettopp er innsatt. Både kolonne-parametre og ny rad er synlig for trigger. - Update / Before Kan benyttes til integritets-kontroll før oppdatering samt setting av default/beregnede kolonne-verdier. Med REFERENCING … NEW vil de nye verdiene være synlig for trigger. Ved default/beregnede kolonne-verdier må receive-parametre benyttes. - Update / After Kan benyttes til operasjoner på data som nettopp er endret. Med REFERENCING … OLD vil de gamle verdiene være synlig for trigger. - Delete / Before Kan benyttes til operasjoner basert på snart-slettede data. Både parameter-kolonner og snart-slettede rader er synlig for trigger. - Delete / After Kan benyttes til bekrefting/avslutning av data-sletting. Parameter-kolonner (med ikke slettede rader) er synlig for trigger.

27. Triggere og referanse-integritet Triggere er nyttige ved implementering av referanse-integritet ved erstatning / supplement av standard SQLBase referanse-integritet. Standard SQLBase referanse-integritet: - DELETE RESTRICT SET NULL CASCADE Eksempel på supplement av referanse-integritet: - UPDATE SET NULL CASCADE ...

28. CTD - Prosedyrer(1) PROCEDURE p Parameters Receive Number: nSNrID Receive String: sNavn Number: nPNr ... Prosedyre for å finne SNrID og Navn for alle selgere med gitt PNr Samtlige prosedyre-parametre Prosedyre-navn Receive prosedyre-parametre Call SqlRetrieve ( hSql, ’p’,’ :dfnSNrID, :dfsNavn, :dfnPNr’, ’ :dfnSNrID, :dfsNavn’ ) Call SqlExecute ( hSql ) Call SqlFetchNext ( hSql, nFetch ) Statement i CTD (med datafelter) for å benytte prosedyren p

29. CTD - Prosedyrer(2) PROCEDURE p Parameters Receive Number: nSNrID Receive String: sNavn Number: nPNr ... Prosedyre for å finne SNrID og Navn for alle selgere med gitt PNr Samtlige prosedyre-parametre Receive prosedyre-parametre settes blank Prosedyre-navn Call SqlRetrieve ( hSql, ’p’,’ :dfnSNrID, :dfsNavn, :dfnPNr’, ’’ ) Call SalListPopulate ( hWndItem, hSql, ’’ ) Statement i CTD (med combobokser) for å benytte prosedyren p

30. CTD - Prosedyrer(3) PROCEDURE p Parameters Receive Number: nSNrID Receive String: sNavn Number: nPNr ... Prosedyre for å finne SNrID og Navn for alle selgere med gitt PNr Samtlige prosedyre-parametre Prosedyre-navn Receive prosedyre-parametre Call SqlRetrieve ( hSql, ’p’,’ :dfnSNrID, :dfsNavn, :dfnPNr’, ’ :dfnSNrID, :dfsNavn’ ) Call SalTblPopulate ( tblSelger, hSql,’’,TBL_FillNormal ) Statement i CTD (med TableWindow) for å benytte prosedyren p

31. End