Christophe Bovigny

Christophe Bovigny
Baris Ulucinar
Fribourg, le 20 décembre 2004
Übung 5
Prozeduren, Triggers und Transaktionen
1.
CREATE VIEW v_wine_produzent (wine, producer)
As
SELECT wine.name, producer.name
FROM wine, producer
WHERE wine.producer_id = producer.id
2.
CREATE VIEW
v_degustator_rating (degustator, wine, taste)
As
SELECT
degustator.name, wine.name, taste.rating
FROM degustator, wine, taste
WHERE
degustator.id = taste.degustator_id and
wine.id = taste.wine_id
3.
CREATE PROCEDURE
change_name @degustator_name_old varchar (30),
@degustator_name_new varchar (30)
AS
IF EXISTS
(SELECT name
FROM degustator
WHERE name = @degustator_name_old)
BEGIN
UPDATE
degustator
SET
name = @degustator_name_new
FROM degustator
WHERE
name = @degustator_name_old
PRINT
'Der Degustator '+'@degustator_name_old'+' wurde in
'+'@degustator_name_new'+'umbenannt.'
END
ELSE
PRINT 'Der Degustator '+'degustator_name_old'+' existiert nicht.'
4.
CREATE PROCEDURE
wine_rating @wine_name varchar (30)
AS
IF EXISTS
(SELECT rating
FROM taste,wine
WHERE taste.wine_id = wine.id and wine.name = @wine_name)
BEGIN
DECLARE
@avg varchar(99), @category varchar(30)
SET
@avg =(SELECT avg(rating)
FROM taste,wine
WHERE taste.wine_id = wine.id
and wine.name = @wine_name)
SET
@category =(SELECT category
FROM wine
WHERE wine.name = @wine_name)
PRINT 'LE VIN: '+@wine_name+', sa categorie: '+@category+
', son evaluation '+@avg
END
ELSE
PRINT 'LE VIN '+@wine_name+' na pas ete evalue'
5.
CREATE TRIGGER ref_integ_foreign
ON mitarbeiter
FOR UPDATE, INSERT
AS
DECLARE @new_inserted integer;
SELECT @new_inserted =(SELECT abt_nr
FROM inserted);
IF EXISTS (SELECT abt_nr
FROM abteilung
WHERE @new_inserted = abt_nr)
BEGIN
PRINT 'Ok, go on ...';
COMMIT TRANSACTION;
END
ELSE
BEGIN
PRINT 'Referential integrity on -abteilung- not respected ...';
PRINT 'Transaction canceled !';
ROLLBACK TRANSACTION
END
-- ref integrity control for the primary key of abteilung
CREATE TRIGGER ref_integ_prim_ab
ON abteilung
FOR UPDATE, INSERT
AS
DECLARE @new_inserted integer;
SELECT @new_inserted =(SELECT abt_nr
FROM inserted);
IF (SELECT COUNT(abt_nr)
FROM abteilung
WHERE @new_inserted = abt_nr) > 1
BEGIN
PRINT 'Referential integrity on primary key not respected ... (ABT)';
PRINT 'Transaction cancelled !';
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
PRINT 'Oki doki, go on ... (PK)(ABT)';
COMMIT TRANSACTION;
END
6.
TRIGGER :
CREATE TRIGGER journalise
ON Mitarbeiter
FOR INSERT, UPDATE, DELETE
AS
DECLARE @rollbacked int
DECLARE @affected_id int
SET @rollbacked = 0
-- check if entry salary < 100000
IF EXISTS (SELECT MA_Gehalt FROM inserted)
BEGIN
IF NOT EXISTS (SELECT MA_Gehalt FROM deleted)
BEGIN
DECLARE @salary_check integer
SELECT @salary_check = (SELECT MA_Gehalt FROM inserted)
-- we are in an INSERT operation, now check if it is < 100000
IF (@salary_check > 100000)
BEGIN
PRINT 'Salary is too high to start...'
ROLLBACK TRANSACTION
SET @rollbacked = 1
END
END
END
IF UPDATE(Mitarbeiter_Einstellungsdatum)
BEGIN
IF EXISTS (SELECT Mitarbeiter_Einstellungsdatum FROM deleted)
BEGIN
PRINT 'Not possible to change this date.'
ROLLBACK TRANSACTION
END
END
IF UPDATE(MA_GEHALT)
BEGIN
DECLARE @new_salary integer
DECLARE @old_salary integer
SELECT @old_salary = (SELECT MA_Gehalt FROM deleted)
SELECT @new_salary = (SELECT MA_Gehalt FROM inserted)
IF(@new_salary > @old_salary * 1.06)
BEGIN
PRINT 'Salary can not raised from more than 6%.'
ROLLBACK TRANSACTION
END
END
-- Journalize only if the transaction occured (i.e. rollbacked != 1)
IF (@rollbacked != 1)
BEGIN
SELECT @affected_id = (SELECT Mitarbeiter_id FROM INSERTED)
IF UPDATE(Mitarbeiter_Name)
BEGIN
DECLARE @old1 char (20)
SELECT @old1 = (SELECT Mitarbeiter_Name FROM INSERTED)
-- insert into log table
INSERT INTO Mitarbeiter_log (Benutzer, Zeit, Affected_id,
Mitarbeiter_Name) VALUES (user_name(), getdate(), @affected_id, @old1)
END
IF UPDATE(Mitarbeiter_Vorname)
BEGIN
DECLARE @old2 char (20)
SELECT @old2 = (SELECT Mitarbeiter_Vorname FROM
INSERTED)
-- insert into log table
INSERT INTO Mitarbeiter_log (Benutzer, Zeit, Affected_id,
Mitarbeiter_Vorname) VALUES (user_name(), getdate(), @affected_id, @old2)
END
IF UPDATE(Mitarbeiter_Geburtstag)
BEGIN
DECLARE @old3 datetime
SELECT @old3 = (SELECT Mitarbeiter_Geburtstag FROM
INSERTED)
-- insert into log table
INSERT INTO Mitarbeiter_log (Benutzer, Zeit, Affected_id,
Mitarbeiter_Geburtstag) VALUES (user_name(), getdate(), @affected_id, @old3)
END
IF UPDATE(Mitarbeiter_Einstellungsdatum)
BEGIN
DECLARE @old4 datetime
SELECT @old4 = (SELECT Mitarbeiter_Einstellungsdatum FROM
INSERTED)
-- insert into log table
INSERT INTO Mitarbeiter_log (Benutzer, Zeit, Affected_id,
Mitarbeiter_Einstellungsdatum) VALUES (user_name(), getdate(), @affected_id,
@old4)
END
IF UPDATE(MA_Adresse)
BEGIN
DECLARE @old5 varchar (50)
SELECT @old5 = (SELECT MA_Adresse FROM INSERTED)
-- insert into log table
INSERT INTO Mitarbeiter_log (Benutzer, Zeit, Affected_id,
MA_Adresse) VALUES (user_name(), getdate(), @affected_id, @old5)
END
IF UPDATE(MA_PLZ)
BEGIN
DECLARE @old6 varchar (10)
SELECT @old6 = (SELECT MA_PLZ FROM INSERTED)
-- insert into log table
INSERT INTO Mitarbeiter_log (Benutzer, Zeit, Affected_id,
MA_PLZ) VALUES (user_name(), getdate(), @affected_id, @old6)
END
IF UPDATE(MA_Ort)
BEGIN
DECLARE @old7 varchar (50)
SELECT @old7 = (SELECT MA_Ort FROM INSERTED)
-- insert into log table
INSERT INTO Mitarbeiter_log (Benutzer, Zeit, Affected_id,
MA_Ort) VALUES (user_name(), getdate(), @affected_id, @old7)
END
IF UPDATE(MA_GEHALT)
BEGIN
DECLARE @old8 int
SELECT @old8 = (SELECT MA_GEHALT FROM INSERTED)
-- insert into log table
INSERT INTO Mitarbeiter_log (Benutzer, Zeit, Affected_id,
MA_GEHALT) VALUES (user_name(), getdate(), @affected_id, @old8)
END
IF UPDATE(Bemerkung)
BEGIN
DECLARE @old9 varchar (100)
SELECT @old9 = (SELECT Bemerkung FROM INSERTED)
-- insert into log table
INSERT INTO Mitarbeiter_log (Benutzer, Zeit, Affected_id,
Bemerkung) VALUES (user_name(), getdate(), @affected_id, @old9)
END
PRINT 'Operations logged in Mitarbeiter_log'
END
TABLES:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Mitarbeiter]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Mitarbeiter]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Mitarbeiter_log]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[Mitarbeiter_log]
GO
CREATE TABLE [dbo].[Mitarbeiter] (
[Mitarbeiter_ID] [int] IDENTITY (40, 1) NOT NULL ,
[Mitarbeiter_Name] [char] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Mitarbeiter_Vorname] [char] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Mitarbeiter_Geburtstag] [datetime] NULL ,
[Mitarbeiter_Einstellungsdatum] [datetime] NULL ,
[MA_Adresse] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MA_PLZ] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MA_Ort] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MA_Gehalt] [int] NULL ,
[Bemerkung] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Mitarbeiter_log] (
[log_id] [int] IDENTITY (1, 1) NOT NULL ,
[affected_id] [int] NOT NULL ,
[Benutzer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Zeit] [datetime] NOT NULL ,
[Mitarbeiter_Einstellungsdatum] [datetime] NULL ,
[Mitarbeiter_Name] [char] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Mitarbeiter_Vorname] [char] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Mitarbeiter_Geburtstag] [datetime] NULL ,
[MA_Adresse] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MA_PLZ] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MA_Ort] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MA_Gehalt] [int] NULL ,
[Bemerkung] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
7.
DEADLOCKS
Transaction 1
BEGIN Transaction
UPDATE seller
SET country = ‘Swiss’
WHERE seller.id = 10
UPDATE sell
SET sell.price = 56
WHERE sell.seller_id = 5
COMMIT Transaction
Transaction 2
BEGIN Transaction
UPDATE sell
SET sell.quantity = 15
WHERE sell.seller_id = 10
UPDATE seller
SET name = ‘Boy’
WHERE seller.id = 5
COMMIT Transaction
Note : Lancer les 2 transactions exactement en même temps !
Une solution pour éviter un deadlock dans un tel cas est de rendre les transactions plus
atomiques, c'est-à-dire de les rendre plus petites :
Transaction 1
BEGIN Transaction
UPDATE seller
SET country = ‘Swiss’
WHERE seller.id = 10
COMMIT Transaction
Transaction 1’
BEGIN Transaction
UPDATE sell
SET sell.price = 56
WHERE sell.seller_id = 5
COMMIT Transaction
Transaction 2
BEGIN Transaction
UPDATE sell
SET sell.quantity = 15
WHERE sell.seller_id = 10
COMMIT Transaction
Tansaction 2’
BEGIN Transaction
UPDATE seller
SET name = ‘Boy’
WHERE seller.id = 5
COMMIT Transaction
De cette manière, la transaction 1 libère la ligne seller.id = 10 directement après avoir effectué
l’opération d’update. Ceci étant, la transaction 2 pourra l’employer.
Les deux opérations de la transaction 1 ne sont pas liées, il n’y a donc pas de raison de les
effectuer dans une seule et unique transaction.
Cette solution fonctionne tant que (et uniquement lorsque) les opérations au sein d’une
transaction sont atomisables et indépendantes.
KETTENBLOCKIERUNG
Transaction 1
BEGIN Transaction
UPDATE seller
SET country = ‘German’
WHERE seller.id = 10
UPDATE sell
SET sell.price = 56
WHERE sell.seller_id = 5
… (operations prenant un certain temps)
COMMIT Transaction
Transaction 2
BEGIN Transaction
UPDATE sell
SET sell.price = 100
WHERE sell.seller_id = 5
UPDATE seller
SET country = ‘Swiss’
WHERE seller.id = 10
COMMIT Transaction
Transaction 3
BEGIN Transaction
UPDATE wine
SET name = ‘Côtes du rhônes’
WHERE wine.id = 9
UPDATE sell
SET sell.price = 200
WHERE sell.seller_id = 5
COMMIT Transaction