Blekinge Tekniska Högskola
Göran Gustafsson
2010-09-23
LABORATION I DATABASTEKNIK: LAGRADE PROCEDURER OCH TRIGGERS
Bakgrund
Det finns i databashanterare möjlighet att lägga programkod i själva databasen.
Fördelar:
påverkar prestanda positivt
förenklar användningen av databasen (enklare sql-satser i klientprogrammet).
Nackdelar:
det kan bli svårare att flytta en databas till en annan databashanterare
vid användning av databasen är det inte uppenbart vad som händer med data.
Databasen
I en databas för att registrera order finns tre tabeller; en med artiklar, en med order och en med orderrader.
För varje order kan flera orderrader finnas. Varje artikel kan naturligtvis ingå i många order.
Affärsregel
Varje order får vara på max 1000 kr. Detta ska kontrolleras av en lagrad procedur. I databasen lagras den totala
ordersumman, trots att det bryter mot normaliseringsreglerna. Anledningen att lagra värdet är prestandaskäl.
För att se till att värdet alltid är korrekt, ska en trigger användas.
Uppgift 1
Skapa tabeller med data med det givna skriptet UDF_DDL.sql.
Uppgift 2
Skapa en lagrad procedur som kontrollerar att en orderrad inte kan läggas till, om det skulle medföra att
ordersumman (totalPrice) blir större än 1000 kr. Denna procedur ska användas vid inläggning av poster i
OrderLine. Den lagrade proceduren ska ta fyra argument, tre in och en ut.
Inparametrar:
Artikelnummer
Ordernummer
Antal
Utparameter:
Ett heltal som indikerar om orderraden accepterades (värde 1) eller blockerades p.g.a. för hög
ordersumma (värde 0).
(forts)
Blekinge Tekniska Högskola
Göran Gustafsson
2010-09-23
Den kontroll som proceduren ska göra är alltså att kontrollera att antal*pris + ordersumma (totalPrice) blir
högst 1000. Om det är mindre än 1000, läggs posten in i OrderLine och utparametern sätts till 1. Om
ordersumman blir för hög, sätts utparametern till 0.
Antag att proceduren heter spAddOrderline, en användning kan då se ut så här:
DECLARE @@result int
EXEC spAddOrderline 20,1001,10, @@result OUTPUT
PRINT @@result -- bara för teständamål
Uppgift 3
Skapa en trigger som uppdaterar ordersumman (totalPrice) i tabellen CustomOrder då en orderrad läggs
in i OrderLine. Triggern ska ta artikelnummer (artNr) och mängd (amount) från den inlagda raden, ta reda
på priset för artikeln, multiplicera pris och mängd samt öka ordersumman med detta belopp.
Uppgift 4
Skapa en funktion som returnerar det totala ordervärdet. Funktionen ska helt enkelt returnera summan av
alla inneliggande order (summan av totalPrice).
Antag att funktionen heter fTotalOrderValue , en användning kan då se ut så här:
DECLARE @@svar FLOAT
SET @@svar = dbo.fTotalOrderValue()
PRINT 'Ordervärde: ' + STR(@@svar,10,2)
-- bara för teständamål
Uppgift 5
Lägg till två parametrar i funktionen för att kunna ange en nedre och en övre gräns för ordernumren.
Funktionen returnerar summan av alla inneliggande order där ordernumret ligger mellan de två
parametervärdena.
LYCKA TILL!