Seite4

[ 1 ] [ 2 ] [ 3 ] [ 4 ] [ 5 ] [ 6 ] [ 7 ] [ 8 ] [ 9 ]

Auch dass der Object Type "Indexes" nicht angewählt ist, d.h. auch auf "Nothing" steht, soll uns nicht weiter beunruhigen. Im Script, das xCase zum SQL Server sendet, sind die Indices nämlich bereits enthalten. Drücken Sie auf "Generate Script" und das Script unsere Datenbank zu erstellen wird von xCase generiert.

CREATE TABLE Parent
(
ParentID INT NOT NULL,
ParentDescr CHAR(40) NULL,
ParentValue NUMERIC(6,2) NULL,
CONSTRAINT PK_Parent PRIMARY KEY NONCLUSTERED
(ParentID)

)
go

CREATE TABLE Child
(
ChildID INT NOT NULL,
ParentID INT NOT NULL,
ChildDesc CHAR(40) NULL,
ChildType CHAR(1) NULL,
ItemID INT NOT NULL,
Quantity NUMERIC(6,0) NULL,
CONSTRAINT PK_Child PRIMARY KEY NONCLUSTERED
(ChildID)
)
go

CREATE NONCLUSTERED INDEX Child_Parent ON Child (ParentID)

CREATE NONCLUSTERED INDEX Child_Item ON Child (ItemID)

CREATE TABLE Item
(
ItemID INT NOT NULL,
ItemDescr CHAR(40) NULL,
ItemPrice NUMERIC(6,2) NULL,
ItemAvailability CHAR(1) NULL,
CONSTRAINT PK_Item PRIMARY KEY NONCLUSTERED
(ItemID)
)
go

ALTER TABLE Child
ADD CONSTRAINT Parent_Child FOREIGN KEY
( ParentID )
REFERENCES Parent
( ParentID )

ALTER TABLE Child
ADD CONSTRAINT Item_Child FOREIGN KEY
( ItemID )
REFERENCES Item
( ItemID )

/* Update Trigger 'T_U_Parent' for Table 'Parent' */

CREATE TRIGGER T_U_Parent ON Parent FOR UPDATE AS

BEGIN
DECLARE
@row_count INT,
@null_row_count INT,
@error_number INT,
@error_message VARCHAR(255)

SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN

/* The Primary Key of 'Parent' cannot be modified if children exist in 'Child' */

IF UPDATE(ParentID)
BEGIN
IF EXISTS
( SELECT 1
FROM Child c, inserted i, deleted d
WHERE c.ParentID = d.ParentID
AND (i.ParentID != d.ParentID)
)

BEGIN
SELECT @error_number=30004,
@error_message='Children exist in "Child". Cannot modify Primary Key in "Parent".'
GOTO error
END
END
RETURN

/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END

/* Delete Trigger 'T_D_Parent' for Table 'Parent' */

CREATE TRIGGER T_D_Parent ON Parent FOR DELETE AS

BEGIN
DECLARE
@row_count INT,
@error_number INT,
@error_message VARCHAR(255)

SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN

/* Parent in 'Parent' cannot be deleted if children exist in 'Child' */

IF EXISTS (
SELECT 1
FROM Child c, deleted d
WHERE c.ParentID = d.ParentID
)
BEGIN
SELECT @error_number=30005,
@error_message='Children exist in "Child". Cannot delete parent "Parent".'
GOTO error
END
RETURN

/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END
go

/* Insert Trigger 'T_I_Child' for Table 'Child' */

CREATE TRIGGER T_I_Child ON Child FOR INSERT AS

BEGIN
DECLARE
@row_count INT,
@null_row_count INT,
@error_number INT,
@error_message VARCHAR(255)

SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN

/* When inserting a row in child 'Child' ,the Foreign Key must be Null or exist in Parent 'Parent' */

IF UPDATE(ParentID)
BEGIN
SELECT @null_row_count =
(
SELECT COUNT(*)
FROM inserted
WHERE ParentID is null
)
IF @null_row_count != @row_count
IF (
SELECT COUNT(*)
FROM Parent p, inserted i
WHERE p.ParentID = i.ParentID
)
!= @row_count - @null_row_count
BEGIN
SELECT @error_number=30001,
@error_message='Cannot insert child in "Child" as its Foreign Key does not exist in "Parent".' GOTO error
END
END

/* When inserting a row in child 'Child' ,the Foreign Key must be Null or exist in Parent 'Item' */

IF UPDATE(ItemID)
BEGIN
SELECT @null_row_count =
(
SELECT COUNT(*)
FROM inserted
WHERE ItemID is null
)
IF @null_row_count != @row_count
IF (
SELECT COUNT(*)
FROM Item p, inserted i
WHERE p.ItemID = i.ItemID
)
!= @row_count - @null_row_count
BEGIN
SELECT @error_number=30001,
@error_message='Cannot insert child in "Child" as its Foreign Key does not exist in "Item".'
GOTO error
END
END
RETURN

/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END
go

/* Update Trigger 'T_U_Item' for Table 'Item' */

CREATE TRIGGER T_U_Item ON Item FOR UPDATE AS

BEGIN
DECLARE
@row_count INT,
@null_row_count INT,
@error_number INT,
@error_message VARCHAR(255)

SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN

/* The Primary Key of 'Item' cannot be modified if children exist in 'Child' */

IF UPDATE(ItemID)
BEGIN IF EXISTS (
SELECT 1
FROM Child c, inserted i, deleted d
WHERE c.ItemID = d.ItemID
AND (i.ItemID != d.ItemID)
)
BEGIN
SELECT @error_number=30004,
@error_message='Children exist in "Child". Cannot modify Primary Key in "Item".'
GOTO error
END
END
RETURN

/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END
go

/* Delete Trigger 'T_D_Item' for Table 'Item' */

CREATE TRIGGER T_D_Item ON Item FOR DELETE AS
BEGIN
DECLARE
@row_count INT,
@error_number INT,
@error_message VARCHAR(255)
SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN

/* Parent in 'Item' cannot be deleted if children exist in 'Child' */

IF EXISTS (
SELECT 1
FROM Child c, deleted d
WHERE c.ItemID = d.ItemID
)
BEGIN
SELECT @error_number=30005,
@error_message='Children exist in "Child". Cannot delete parent "Item".'
GOTO error
END
RETURN

/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END
go

Wie Sie sehen, ist dieses Script tatsächlich vollständig, neben den Tabellen, Indices und Constraints hat xCase sogar die Update und Delete Triggers erstellt, um sicherzustellen, dass nicht Primärschlüssel in der "Parent" oder "Item" Tabelle verändert werden können, wenn gleichzeitig abhängige Daten dazu vorhanden sind.

Applizieren von Strukturänderungen

xCase unterstützt einen grundsätzlich auch darin, seine Strukturänderungen auf der Datenbank zu applizieren. Hierzu verwendet xCase den Ansatz, eine neue Tabelle mit der angepassten Struktur zu erstellen, in diese Tabelle die Daten aus der alten Tabelle einzufügen, die alte Tabelle zu löschen und schliesslich die neu erstellte Tabelle in den ursprünglichen Namen umzubenennen. Hier ein Beispiel:

CREATE TABLE TEMP_Item
(
ItemID INT NOT NULL,
ItemDescr CHAR(40) NULL,
ItemPrice NUMERIC(6,2) NULL,
ItemAvailability CHAR(1) NULL,
test CHAR(10) NULL
)
go

INSERT INTO TEMP_Item (ItemID,ItemDescr,ItemPrice,ItemAvailability)
SELECT ITEMID,ITEMDESCR,ITEMPRICE,ITEMAVAILABILITY
FROM Item
go
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'Item' AND type = 'U')
DROP TABLE Item
go
EXECUTE sp_rename 'TEMP_Item', 'Item', 'OBJECT'

Um diese Schritte jedoch durchführen zu können, müssen zunächst alle Referenzen zur alten Tabelle entfernt werden und anschliessend wieder erstellt werden. Obwohl xCase das prinzipiell auch versucht zu tun, gelingt es je nach Komplexität und Abfoge der Abhängigkeiten nicht immer auf Anhieb. Das liegt u.a. auch daran, dass xCase die Tabellen nicht in einer logischen Abhängigkeit abarbeitet, sondern stur alphabetisch, was natürlich füher oder später zu Problemen führt.

Wird im SQL Server nicht mit dem 6.5 Kompatibilitätsmodus gearbeitet, ist es manchmal viel einfacher bei simplen Strukturänderungen direkt die T-SQL Scripts abzusetzen:

alter table adresse add einzug char(1)

um eine Spalte hinzuzufügen, bzw.

alter table bank drop column einzug

um eine Spalte zu entfernen. Bei Feldtypenänderungen, wie eine einfache Stellenerweiterung, kann auch mit dem Alter Table Script operiert werden.

WICHTIG: Denken Sie daran, dass xCase die Triggers nicht von sich aus aktualisiert. Sie müssen die Triggers explizit neu generieren lassen, damit diese nach umfangreichen Datenstrukturänderungen wieder funktionstüchtig sind.

Constraints, Rules und Triggers

Wir haben in obigem Script gesehen, wie eine Foreign Key Constraint aussieht und wir haben auch gesehen, wie ein Update oder Delete Trigger augebaut ist. Es lohnt sich hier näher darauf einzugehen, damit man in der Praxis genau weiss, wann welches Konstrukt zu verwenden ist.

Constraints sind wenn immer möglich Triggern oder Rules vorzuziehen. Rules sollten nach Möglichkeit gar nicht mehr verwendet werden. Sie sind nur wegen Rückwärtskompatibilität vorhanden.

Constraints sind proaktiv, Triggers hingegen reaktiv. Das heisst, dass Constraints versuchen, etwas sicherzustellen, bevor es in der Datenbank zu einem unerwünschten Zustand kommt, die Triggers hingegen kommen erst dann zum Zug, wenn ein womöglich unerwünschter Zustand bereits eingetreten ist, der dann wieder zurückgestellt werden muss. Das ist natürlich viel aufwendiger, als bei den Constraints, deshalb sollte wenn immer möglich mit Constraints gearbeitet werden.

Constraints

Es gibt folgende Arten von Constraints:

Constraint Syntax Bsp Beschreibung

PRIMARY KEY

CREATE TABLE Child

(
ChildID INT NOT NULL,
ParentID INT NOT NULL,
ChildDesc CHAR(40) NULL,
ChildType CHAR(1) NULL,
ItemID INT NOT NULL,
Quantity NUMERIC(6,0) NULL,
CONSTRAINT PK_Child PRIMARY KEY NONCLUSTERED
(ChildID))

Stell auf Stufe Tabelle sicher, dass in einer Tabelle nicht zweimal derselbe Primärschlüssel vergeben werden kann

FOREIGN KEY

ALTER TABLE Child
ADD CONSTRAINT Parent_Child FOREIGN KEY
( ParentID )
REFERENCES Parent
( ParentID )

Stellt referentiell sicher, dass nicht ein nicht existierender Fremdschüssel (inkl. leer Einträge, nicht .null.) in das entsprechende Feld eingetragen werden kann

UNIQUE

ALTER TABLE Parent
ADD CONSTRAINT my_unique UNIQUE
( ParentDescr )

Stellt sicher, dass es nicht mehrere gleiche Einträge bzgl. dem entsprechenden Feld oder Ausdruck gibt

DEFAULT

ALTER TABLE Child
ADD CONSTRAINT my_default DEFAULT '
Unknown' FOR CHILDDESC

Stellt auf Tabellenebene sicher, dass alle neu angelegten Datensätze einen bestimmten Standardwert für bestimmte Felder erhalten, sofern nichtz explizit ein Wert angegeben wird

CHECK

ALTER TABLE Child
ADD CONSTRAINT my_check CHECK (CHILDTYPE='A' or CHILDTYPE='B')

Stellt sicher, dass nur bestimmte Werte für ein bestimmtes Feld in Frage kommen.

TIP: Um eine Constraint wieder zu entfernen, können Sie im Query Analyzer folgendes eingeben:

alter table child drop constraint my_check

Triggers

Es gibt folgende Arten von Triggers

Trigger Syntax Bsp Beschreibung

UPDATE

CREATE TRIGGER T_U_Parent ON Parent FOR UPDATE AS

BEGIN
DECLARE
@row_count INT,
@null_row_count INT,
@error_number INT,
@error_message VARCHAR(255)
SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN
/* The Primary Key of 'Parent' cannot be modified if children exist in 'Child' */
IF UPDATE(ParentID)
BEGIN
IF EXISTS (
SELECT 1
FROM Child c, inserted i, deleted d WHERE c.ParentID = d.ParentID
)
BEGIN
SELECT @error_number=30004, @error_message='Children exist in "Child".

Cannot modify Primary Key in "Parent".' GOTO error

END
END
/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END

Wird dann ausgeführt, nachdem ein Update stattgefunden hat.
HINWEIS: Sehr häufig verwendet man die Anweisung UPDATE(FeldName) um im Trigger Code festzustellen, ob ein Feldinhalt verändert wurde.
Detaillierter UPDATE Ablauf:
1. UPDATE Statement wird ausgeführt und ein UPDATE Trigger existiert
2. Das UPDATE Statement wird geloggt als einzelne INSERT und DELETE statements.
3. Der Trigger schiesst los und die Trigger Statements werden abgearbeitet. Hierbei kann der alte Datensatz in der virtuellen Tabelle Namens "deleted", der neu hinugefügte unter dem Namen "inserted" angesprochen werden.

INSERT

CREATE TRIGGER T_I_Parent ON Parent FOR INSERT

AS
BEGIN

…

Wird dann ausgeführt, nachdem ein Insert stattgefunden hat.
Detaillierter INSERT Ablauf:
1. INSERT Statement wird ausgeführt bei einer Tabelle mit einem INSERT Trigger
2. Das INSERT Statement wird geloggt
3. Der INSERT Trigger schiesst los und die Trigger Statements werden abgearbeitet. Hierbei kann der neue Datensatz sowohl in der virtuellen Tabelle Namens "inserted", als auch in der Tabelle selbst angesprochen werden.

DELETE

CREATE TRIGGER T_D_Parent ON Parent FOR
DELETE AS
BEGIN

Wird dann ausgeführt, nachdem ein Delete stattgefunden hat.
Detaillierter DELETE Ablauf:
1. DELETE Statement wird ausgeführt bei einer Tabelle mit einem DELETE Trigger
2. Das DELETE Statement wird geloggt
3. Der DELETE Trigger schiesst los und die Trigger Statements werden abgearbeitet. Hierbei kann der gelöschte Datensatz in der virtuellen Tabelle Namens "deleted" angesprochen werden.

HINWEIS: Triggers sind natürlich auch ausgezeichnet dafür geeignet, Audit Trails zu erstellen. Ich werde in der Session zeigen, wie Audit Trails implementiert werden können und worauf dabei zu achten ist.

SQL Server Handling

SQL Server 7.0 und SQL Server 2000 (8.0) sind im Handling grundsätzlich identisch. SQL Server 2000 hat einige Neuerungen, welche im SQL Server Books Online nachgelesen werden können. Auf eine Auflistung wird an dieser Stelle deshalb verzichtet.

Der Query Analyzer

Der Query Analyzer ist der ständige Begleiter eines jeden Client/Server Entwicklers. Genauso wie der Enterprise Manager und der Profiler gehört er zum täglich verwendeten Hilfsmittel.

Bevor man mit dem Query Analyzer arbeiten kann, muss man sich mit einem SQL Server verbinden, das erfolgt über den "Connect" Dialog. Dieser kann jederzeit aus dem File Menu heraus aufgerufen werden:

Anschliessend erhält man folgenden Dialog, um seine Arbeiten zu erledigen:

[ 1 ] [ 2 ] [ 3 ] [ 4 ] [ 5 ] [ 6 ] [ 7 ] [ 8 ] [ 9 ]