Trigger

[thobr]

Hallo,

ich möchte folgenden Trigger von folgender Tabelle erstellen:

CREATE TABLE dbo.belegdauer
(    insassen_id int NOT NULL CONSTRAINT fk_insassen_id_ref_belegdauer REFERENCES dbo.insassen (insassen_id),
    raum_id int NOT NULL CONSTRAINT fk_raum_id_ref_belegdauer REFERENCES dbo.raum (raum_id),
    von date NOT NULL,
    bis date
);

Trigger:
Überprüft ob der Insasse in Tabelle "Belegdauer" schon irgendwo anders zugeteilt ist, zu dem gleichen Zeitpunkt.
Dies sollte bei insert und update überprüft werden.
Kann mir da jemand helfen?

Habe nur das Grundgerüst:

ALTER TRIGGER dbo.insassen_belegueberpruefung
ON dbo.belegdauer
FOR INSERT
AS
BEGIN
   SET NOCOUNT ON;
   IF
   BEGIN
       THROW 50101, 'Der Insasse ist bereits zugeteilt', 1;
   END
               
END
GO

Im IF hatte ich schon mehrere Sachen probiert. Bin aber gescheitert

LG

 

[akretschmer]

Tja, Du mußt dann auch prüfen, ob sich Zeiträume überlappen. Das ist schon einiges an Aufwand, und dürfte bei größeren Tabellen relativ zeitintensiv werden.

Falls das ein neue Projekt ist und Du noch die DB tauschen kannst: in PostgreSQL geht das über einen EXCLUSION CONSTRAINT zu definieren.

test=*# create table hotel (zimmer int, belegt daterange, exclude using gist(zimmer with =, belegt with &&));
CREATE TABLE
test=*# insert into hotel values (1, '[2020-01-01,2020-01-20)');
INSERT 0 1
test=*# insert into hotel values (2, '[2020-01-01,2020-01-20)');
INSERT 0 1
test=*# insert into hotel values (2, '[2020-01-10,2020-01-30)');
ERROR:  conflicting key value violates exclusion constraint "hotel_zimmer_belegt_excl"
DETAIL:  Key (zimmer, belegt)=(2, [2020-01-10,2020-01-30)) conflicts with existing key (zimmer, belegt)=(2, [2020-01-01,2020-01-20)).
test=*#

Der Check erfolgt über einen speziellen GIST-Index - und ist damit sehr schnell.

 

[thobr]

Nein komme von der MS SQL leider nicht weg
Ja die Prüfung das die Zeiten nicht überlappen ist nicht so einfach. Aber hättest du einen Ansatz wie du das machen würdest, vl nur mit der Überprüfung ob die neue "von" Zeit nach der letzten vorhanden "bis" Zeit ist?

 

[akretschmer]

ich schätze mal, der @ukulele wird Dir was passendes basteln...

 

[ukulele]

Easy... Also:

a) Ein MSSQL-Trigger feuert nur einmal pro INSERT / UPDATE / DELETE. Das bedeutet ganz konkret das du theoretisch mehrere Datensätze gleichzeitig behandeln musst. Tust du es nicht und es ist mehr als ein Datensatz gleichzeitig in der Tabelle betroffen gibt es entweder eine Fehlermeldung oder dein Trigger greift nur für den einen beliebigen Datensatz.
b) Ich empfehle dir einen INSTEAD OF Trigger, so das "falsche" Datensätze gar nicht erst in die Tabelle kommen.
c) Eventuell geht es mit einem Constraint, vermutlich sogar, aber jetzt hab ich schon einen Trigger geschrieben

ALTER TRIGGER dbo.insassen_belegueberpruefung
ON dbo.belegdauer
INSTEAD OF INSERT, UPDATE
AS
BEGIN
   SET NOCOUNT ON;

INSERT INTO dbo.belegdauer(insassen_id,raum_id,von,bis)
SELECT   i.insassen_id,
       i.raum_id,
       i.von,
       i.bis
FROM   INSERTED i
LEFT JOIN DELETED d
ON       i.insassen_id = d.insassen_id
AND       i.raum_id = d.raum_id
LEFT JOIN dbo.belegdauer b
ON       i.insassen_id = b.insassen_id
AND       i.raum_id = b.raum_id
AND   (   i.von BETWEEN b.von AND isnull(b.bis,'9999-31-12 23:59:59.997')
OR       isnull(i.bis,'9999-31-12 23:59:59.997') BETWEEN b.von AND isnull(b.bis,'9999-31-12 23:59:59.997') )
WHERE   d.insassen_id IS NULL
AND       b.insassen_id IS NULL;

UPDATE   dbo.belegdauer
SET       belegdauer.insassen_id = i.insassen_id,
       belegdauer.raum_id = i.raum_id,
       belegdauer.von = i.von,
       belegdauer.bis = i.bis
FROM   dbo.belegdauer
INNER JOIN INSERTED i
ON       belegdauer.insassen_id = i.insassen_id
AND       belegdauer.raum_id = i.raum_id
INNER JOIN DELETED d
ON       belegdauer.insassen_id = d.insassen_id
AND       belegdauer.raum_id = d.raum_id;
         
END
GO

Eigentlich läßt sich das mit Schema-F machen:
1) Du wählst aus der INSERTED Tabelle alle Datensätze aus die gültig sind und definitiv neu, also nicht in DELETED stehen und fügst sie in die Tabelle ein.
2) Du wählst alle Datensätze aus die nur aktualisiert werden (in DELETED stehen) und aktuallisierst sie sofern die neuen Werte gültig sind.

Das geht bei allen Tabellen, mus halt nur bei neuen Spalten angepasst werden weil INSTEAD OF Trigger.

3) Musst du dir überlegen was du mit dem Rest an Datensätzen machen willst, die Kollisionen haben. Du kannst natürlich eine Meldung zurück geben bei einem Fehler, willst du das dann gar nichts passiert oder sollen irgendwo Log Einträge geschrieben werden etc.