Ausdrücke und berechnete Spalten in INSTEAD OF-Triggern
Die Auswahlliste einer Sicht kann auch andere als nur einfache Ausdrücke, die nur aus einem Spaltennamen bestehen, aufweisen. INSTEAD OF-Trigger für diese Sichten benötigen Logik, um anhand der für die INSERT- und UPDATE-Anweisungen angegebenen Werte ordnungsgemäß zu bestimmen, welche Werte in Spalten der Basistabelle festgelegt werden müssen. Einige Beispiele für solche Ausdrücke finden Sie im Folgenden:
Sichtausdrücke, die keiner Spalte in einer Tabelle zugeordnet sind, wie beispielsweise eine Konstante oder bestimmte Funktionstypen.
Sichtausdrücke, die mehreren Spalten zugeordnet sind, wie beispielsweise komplexe Ausdrücke, die durch Verketten von Zeichenfolgen aus zwei oder mehreren Spalten erstellt werden.
Sichtausdrücke, die den Wert einer einzigen Basistabellenspalte transformieren, wie beispielsweise durch Verweisen auf eine Spalte in einer Funktion.
Diese Punkte gelten auch für Sichtspalten, bei denen es sich um einfache Ausdrücke handelt, die auf eine berechnete Spalte in einer Basistabelle verweisen. Der Ausdruck, der die berechnete Spalte definiert, kann dasselbe Format wie ein komplexerer Ausdruck in der Auswahlliste der Sicht haben.
Sichten können in der Auswahlliste Ausdrücke enthalten, die keinen Basistabellenspalten zugeordnet sind, z. B.:
CREATE VIEW dbo.ExpressionView
AS
SELECT BusinessEntityID, JobTitle, GETDATE() AS TodaysDate
FROM AdventureWorks2008R2.HumanResources.Employee;
Obwohl die TodaysDate-Spalte keiner Tabellenspalte zugeordnet ist, muss SQL Server eine TodaysDate-Spalte in der eingefügten Tabelle erstellen, die an einen für ExpressionView definierten INSTEAD OF-Trigger übergeben wird. Die inserted.TodaysDate-Spalte lässt jedoch NULL zu, weshalb eine INSERT-Anweisung, die auf ExpressionView verweist, keinen Wert für diese Spalte angeben muss. Da dieser Ausdruck keiner Tabellenspalte zugeordnet ist, kann der Trigger alle Werte ignorieren, die von der INSERT-Anweisung in dieser Spalte angegeben werden.
Der gleiche Ansatz sollte für einfache Sichtausdrücke verwendet werden, die auf berechnete Spalten in Basistabellen verweisen, die auch ein Resultset erstellen, das nicht von anderen Spalten abhängt, z. B.:
CREATE TABLE dbo.ComputedExample
(
PrimaryKey int PRIMARY KEY,
ComputedCol AS SUSER_NAME()
);
Einige komplexe Ausdrücke sind mehreren Spalten zugeordnet. Beispiel:
CREATE TABLE dbo.SampleTable
(
PriKey int,
FirstName nvarchar(20),
LastName nvarchar(30)
);
GO
CREATE VIEW dbo.ConcatView
AS
SELECT PriKey, FirstName + ' ' + LastName AS CombinedName
FROM SampleTable;
Der Ausdruck CombinedName in ConcatView besteht aus verketteten Werten aus den FirstName- und LastName-Werten. Wenn ein INSTEAD OF INSERT-Trigger für ConcatView definiert wird, benötigen Sie eine Konvention, wie INSERT-Anweisungen einen Wert für die CombinedName-Spalte angeben, auf deren Grundlage der Trigger ermitteln kann, welcher Teil der Zeichenfolge in die FirstName-Spalte und welcher Teil in die LastName-Spalte eingefügt werden soll. Wenn Sie die Konvention auswählen, dass INSERT-Anweisungen den Wert von CombinedName anhand der Konvention 'first_name;last_name' angeben, kann der folgende Trigger eine INSERT-Anweisung erfolgreich ausführen:
CREATE TRIGGER InsteadSample on dbo.ConcatView
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO dbo.SampleTable
SELECT PriKey,
-- Pull out the first name string.
SUBSTRING(
CombinedName,
1,
(CHARINDEX(';', CombinedName) - 1)
),
-- Pull out the last name string.
SUBSTRING(
CombinedName,
(CHARINDEX(';', CombinedName) + 1),
DATALENGTH(CombinedName) - (CHARINDEX(';', CombinedName) + 1)
)
FROM inserted
END;
Eine ähnliche Logik ist für die Verarbeitung von Sichtspalten erforderlich, bei denen es sich um einfache Ausdrücke handelt, die auf berechnete Spalten mit komplexen Ausdrücken verweisen.
Einige Sichtausdrücke können den Wert einer Basistabellenspalte transformieren, beispielsweise durch Ausführen einer mathematischen Operation oder durch Verwenden der Spalte als Parameter für eine Funktion. In diesem Fall gibt es für die Logik des INSTEAD OF INSERT-Triggers zwei Ansätze:
Eine mögliche Konvention wäre, dass alle INSERT-Anweisungen den Rohdatenwert für die Basistabelle angeben und die Triggerlogik den Wert von der inserted-Tabelle in die Basistabelle kopiert.
Eine weitere mögliche Konvention wäre, dass alle INSERT-Anweisungen den Wert angeben, der von einer SELECT-Anweisung in der Sicht zurückgegeben werden soll, wobei in diesem Fall die Logik des Triggers den Vorgang umkehren muss. Beispiel:
CREATE TABLE dbo.BaseTable ( PrimaryKey int PRIMARY KEY, ColumnB int, ColumnC decimal(19,3) ); CREATE VIEW dbo.SquareView AS SELECT PrimaryKey, ColumnB, -- Square the value of ColumnC SQUARE(ColumnC) AS SquareC FROM BaseTable; CREATE TRIGGER SquareTrigger ON dbo.SquareView INSTEAD OF INSERT AS BEGIN INSERT INTO dbo.BaseTable SELECT PrimaryKey, ColumnB, -- Perform logical inverse of function in view. SQRT(SquareC) FROM inserted END;
Für einige Ausdrücke, wie z. B. komplexe Ausdrücke, die mathematische Operationen wie Addition und Subtraktion verwenden, können Benutzer möglicherweise keinen Wert angeben, mit dem der Trigger eindeutig Werte für die Zielbasis-Tabellenspalten erstellen kann. Wenn z. B. die Auswahlliste einer Sicht den IntColA + IntColB AS AddedColumns-Ausdruck enthält, was bedeutet dann der Wert 10 in inserted.AddedColumns? Ist 10 das Ergebnis von 3 + 7, 2 + 8 oder 5 + 5? Es gibt keine Möglichkeit, um ausschließlich anhand des Wertes von inserted.AddedColumns darauf zu schließen, welche Werte in IntColA und IntColB platziert werden sollten.
In diesen Fällen kann der Trigger für die Verwendung alternativer Informationsquellen codiert werden, um die in den Basistabellenspalten festzulegenden Werte zu bestimmen. Für Sichten mit INSTEAD OF-Triggern muss die Auswahlliste der Sicht ausreichend Informationen enthalten, um Werte für alle Spalten ungleich NULL in den Basistabellen zu erstellen, die vom Trigger geändert wurden. Nicht alle Daten müssen direkt aus der inserted-Tabelle stammen. In einigen Fällen können die Werte in der inserted-Tabelle Schlüsselwerte sein, mit denen der Trigger die relevanten Daten aus anderen Basistabellen abruft.