6238 Databaser Agenda/Stored procedures og Triggers

Stored procedure

Med stored procedure kan man automatiserer nogle processer. Det minder meget om metoder og funktioner i andre programmeringssprog.

Der er igen en lille forskel på MS-Sql og MySql.

Her er f.eks. vist hvordan vi indsætter Saelger og telefon nr i en og samme arbejdsgang.

MS-Sql

CREATE PROCEDURE InsertSaelger @navn NVARCHAR(20), @email NVARCHAR(20), @startDato DATETIME,  @saelgerNr INT, @telefonNr NVARCHAR(8)
AS
  IF NOT EXISTS (SELECT Navn FROM Saelger WHERE Saelger.MedarbNr = @saelgerNr) 
  BEGIN
    INSERT INTO	Saelger(Navn,Email,StartDato,MedarbNr)VALUES(@navn,@email,@startDato,@saelgerNr);
  END
  INSERT INTO TelefonNr(Nr,Saelger) VALUES (@telefonNr,@saelgerNr);
GO

Bemærk hvordan alle variable navne har @ foran sig og at scope ikke er markeret med {} som vi kender fra C# men i stedet med Begin End som i Basic.

Proceduren kaldes således.

EXECUTE InsertSaelger 'Jens', 'jens@mail.dk', '2001-1-1',4,'67891234';

MySql MySql er lidt speciel da den vil forsøge at afvikle koden hver gang den ser karakteren ; Den karakter kaldes en DELIMITER

Løsningen er at lave dens DELIMITER om til noget andet en ; i viste tilfælde // I slutningen af proceduren laves den tilbage til ;

Der ud over anvendes ikke @ foran variable navne, de parametrene der overføres skal stå i parantes og IF skal efterfølges af THEN og afsluttes med END IF.

DELIMITER //
CREATE PROCEDURE InsertSaelger 
(IN navn NVARCHAR(20), IN email NVARCHAR(20), IN startDato DATETIME,  IN saelgerNr INT, IN telefonNr NVARCHAR(8))
BEGIN
  IF NOT EXISTS (SELECT Navn FROM Saelger WHERE Saelger.MedarbNr = saelgerNr) THEN
    INSERT INTO	Saelger(Navn,Email,StartDato,MedarbNr)VALUES(navn,email,startDato,saelgerNr);
  END IF;
  INSERT INTO TelefonNr(Nr,Saelger) VALUES (@telefonNr,@saelgerNr);
END //
DELIMITER ;

Når proceduren kaldes anvendes CALL ikke EXECUTE.

Call InsertSaelger ('Jens', 'jens@mail.dk', '2001-1-1',4,'67891234');

Opgave

Lav på samme måde en stored procedure der kan indsætte en vare og købesaf i en arbejdsgang.

Løs også følgende

En database til registrering af ægteskaber mellem to personer.

Lav tabellerne Person og Aegteskab.

Lav nu en StoredProcedure der sikre at en person ikke kan blive gift hvis personen allerede er gift med en anden.

Triggers

Med triggers kan man udføre forskellige procedure når der ændres i forskellige tabeller.

CREATE TRIGGER tr_Saelger_DELETE ON Saelger INSTEAD OF DELETE 
AS
	DECLARE @Nr int
	SET @Nr = (SELECT MedarbNr FROM deleted)
	UPDATE Kunde SET Saelger = 1 WHERE Saelger = @Nr 
	UPDATE Bil SET Saelger = 1 WHERE Saelger = @Nr
	UPDATE TelefonNr SET Saelger = 1 WHERE Saelger = @Nr
	DELETE FROM Saelger WHERE MedarbNr = @Nr
go

Go
DELETE FROM Saelger WHERE MedarbNr = 2
GO

Den viste trigger fanger DELETE på Saelger tabellen. Normalt kan det ikke lykkes på grund af constraints da både kunde, bil og telefon peger på Saelger.

Koden her fanger DELETE og ændre først de berørte records til Saelger 1, hvorefter sælgeren slettes.

På samme måde som INSTEAD OF DELETE findes naturligvis også triggere for INSERT, UPDATE etc.

For MySQL ser det samme således ud. Bemærk at vi ikke erstatter DELETE men i stedet udfører en BEFORE DELETE

DELIMITER //
CREATE TRIGGER tr_Saelger_DELETE BEFORE DELETE ON Saelger FOR EACH ROW 
BEGIN
    DECLARE Nr int DEFAULT 0;
    SET Nr = OLD.MedarbNr;
    UPDATE Kunde SET Saelger = 1 WHERE Saelger = Nr; 
    UPDATE Bil SET Saelger = 1 WHERE Saelger = Nr;
    UPDATE TelefonNr SET Saelger = 1 WHERE Saelger = Nr;
END //
DELIMITER ;

DELETE FROM saelger WHERE MedarbNr = 2;