CREATE TRIGGER (T-SQL)

CREATE TRIGGER (TRANSACT-SQL)
Diese Anweisung erstellt einen Trigger, einen besonderen Typ einer gespeicherten Prozedur,
der automatisch ausgeführt wird, wenn ein Benutzer versucht, die angegebene
Datenänderungsanweisung für die angegebene Tabelle auszuführen. Es können mehrere
Trigger für eine angegebene INSERT-, UPDATE- oder DELETE-Anweisung erstellt werden.
Syntax
CREATE TRIGGER trigger_name
ON table
[WITH ENCRYPTION]
{
{ FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS sql_statement […n]
}
|
{ FOR { [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
{
IF UPDATE (column)
[{AND | OR} UPDATE (column)]
[...n]
| IF (COLUMNS_UPDATED() {bitwise_operator}
updated_bitmask)
{ comparison_operator} column_bitmask [...n]
}
sql_statement [ ...n]
}
}
Argumente
trigger_name
Der Name des Triggers. Triggernamen müssen den Regeln für Bezeichner entsprechen
und innerhalb der Datenbank eindeutig sein. Die Angabe des Triggerbesitzernamens
ist optional.
table
Die Tabelle, für die der Trigger ausgeführt wird; sie wird manchmal auch als
Triggertabelle bezeichnet. Die Angabe des Tabellenbesitzernamens ist optional.
Sichten können nicht angegeben werden.
WITH ENCRYPTION
Verschlüsselt die syscomments-Einträge, die den Text der CREATE TRIGGERAnweisung enthalten.
{ [DELETE] [,] [INSERT] [,] [UPDATE] } | { [INSERT] [,] [UPDATE]}
Schlüsselwörter, die angeben, welche Datenänderungsanweisungen den Trigger
aktivieren, wenn sie auf diese Tabelle angewendet werden. Es muss mindestens eine
Option angegeben werden. Die Optionen können in beliebiger Kombination und
Reihenfolge in der Triggerdefinition angegeben werden. Wenn Sie mehrere Optionen
angeben, trennen Sie diese durch Kommas.
WITH APPEND
Gibt an, dass ein weiterer Trigger eines vorhandenen Typs hinzugefügt werden soll.
Diese optionale Klausel muss nur dann verwendet werden, wenn der
Kompatibilitätsgrad kleiner oder gleich 65 ist. Wenn der Kompatibilitätsgrad größer
oder gleich 70 ist, wird die optionale WITH APPEND-Klausel nicht benötigt, um
einen zusätzlichen Trigger eines vorhandenen Typs hinzuzufügen (dies ist das
Standardverhalten von CREATE TRIGGER, wenn der Kompatibilitätsgrad größer
oder gleich 70 ist.) Weitere Informationen finden Sie unter sp_dbcmptlevel.
NOT FOR REPLICATION
Zeigt an, dass der Trigger nicht ausgeführt werden soll, wenn ein Replikationsprozess
die vom Trigger betroffene Tabelle ändert.
AS
Die Aktionen, die der Trigger ausführen soll.
sql_statement
Die Triggerbedingung(en) und aktion(en). Triggerbedingungen enthalten zusätzliche
Kriterien, von denen es abhängt, ob der Versuch, eine INSERT-, DELETE- oder
UPDATE-Anweisung auszuführen, die Triggeraktion(en) auslöst.
Die in den Transact-SQL-Anweisungen festgelegten Triggeraktionen werden
ausgeführt, wenn versucht wird, die Benutzeraktion (UPDATE, INSERT oder
DELETE) durchzuführen.
Trigger dürfen beliebig viele Transact-SQL-Anweisungen jeglicher Art enthalten, mit
Ausnahme von SELECT-Anweisungen. Ein Trigger ist konzipiert, um Daten auf der
Grundlage einer Datenänderungsanweisung zu prüfen oder zu ändern, jedoch nicht um
Daten an den Benutzer zurückzugeben. Die Transact-SQL-Anweisungen in einem
Trigger enthalten oft Sprachkonstrukte zur Ablaufsteuerung. In CREATE TRIGGERAnweisungen werden einige besondere Tabellen verwendet:

deleted und inserted sind logische (konzeptionelle) Tabellen. Sie sind ähnlich
strukturiert wie die Tabelle, für die der Trigger definiert ist (die Tabelle, auf
die sich die Benutzeraktion bezieht), und enthalten die alten bzw. neuen Werte
der Zeilen, die durch die Benutzeraktion geändert werden können. Um
beispielsweise alle Werte in der deleted-Tabelle abzurufen, verwenden Sie:
SELECT *
FROM deleted

n
In einem DELETE-, INSERT- oder UPDATE-Trigger lässt SQL Server keine
text-, ntext- oder image-Spaltenverweise in der inserted- und der deletedTabelle zu, wenn der Kompatibilitätsgrad gleich 70 ist. Auf die text-, ntextund image-Werte in der inserted- und der deleted-Tabelle kann nicht
zugegriffen werden. Um den neuen Wert in einem INSERT- oder UPDATETrigger abzurufen, verknüpfen Sie die inserted-Tabelle mit der ursprünglichen
Aktualisierungstabelle. Wenn der Kompatibilitätsgrad kleiner oder gleich 65
ist, werden NULL-Werte zurückgegeben für die Spalten text, ntext oder
image der inserted- oder deleted-Tabelle, die NULL-Werte zulassen.
Zeichenfolgen der Länge Null werden zurückgegeben, wenn die Spalten keine
NULL-Werte zulassen.
Ein Platzhalter, der anzeigt, dass mehrere Transact-SQL-Anweisungen in den Trigger
aufgenommen werden können. In die IF UPDATE (column)-Anweisung können
mehrere Spalten aufgenommen werden, indem die UPDATE (column)-Klausel
wiederholt wird.
IF UPDATE (column)
Testet, ob eine INSERT- oder UPDATE-Aktion an einer angegebenen Spalte
vorgenommen wurde, und wird nicht mit DELETE-Operationen verwendet. Es
können mehrere Spalten angegeben werden. Da der Tabellenname in der ON-Klausel
angegeben ist, nehmen Sie den Tabellennamen nicht vor dem Spaltennamen in eine IF
UPDATE-Klausel auf. Um mehrere Spalten auf eine INSERT- oder UPDATE-Aktion
zu testen, geben Sie nach der ersten eine separate UPDATE(column)-Klausel an.
Anmerkung Die IF UPDATE (column)-Klausel funktioniert genauso wie eine IF-,
IF…ELSE- oder WHILE-Anweisung und kann den BEGIN…END-Block verwenden.
Weitere Informationen finden Sie unter Sprachkonstrukte zur Ablaufsteuerung.
UPDATE(column) kann überall innerhalb des Triggerrumpfes verwendet werden.
column
Der Name der auf eine INSERT- oder UPDATE-Aktion zu testenden Spalte. Diese
Spalte kann einen beliebigen von SQL Server unterstützten Datentyp haben. Weitere
Informationen finden Sie unter Datentypen.
IF (COLUMNS_UPDATED())
Testet in einem INSERT- oder UPDATE-Trigger, ob die genannte(n) Spalte(n)
eingefügt oder aktualisiert wurde(n). COLUMNS_UPDATED gibt ein varbinaryBitmuster zurück, das anzeigt, welche Spalten in der Tabelle eingefügt oder
aktualisiert wurden.
COLUMNS_UPDATED kann überall innerhalb des Triggerrumpfes verwendet
werden.
bitwise_operator
Der im Vergleich zu verwendende bitweise Operator.
updated_bitmask
Die ganzzahlige Bitmaske der tatsächlich aktualisierten oder eingefügten Spalten. Die
t1-Tabelle enthält beispielsweise die Spalten C1, C2, C3, C4 und C5. Um zu prüfen,
ob die Spalten C2, C3 und C4 aktualisiert wurden (wenn die t1-Tabelle einen
UPDATE-Trigger hat), geben Sie den Wert 14 ein. Um zu prüfen, ob nur die C2Spalte aktualisiert wurde, geben Sie den Wert 2 ein.
comparison_operator
Der Vergleichsoperator. Verwenden Sie das Gleichzeichen (=), um zu prüfen, ob alle
in updated_bitmask angegebenen Spalten tatsächlich aktualisiert wurden. Verwenden
Sie das Größer-als-Symbol (>), um zu prüfen, ob eine oder mehrere der in
updated_bitmask angegebenen Spalten aktualisiert wurden.
column_bitmask
Die ganzzahlige Bitmaske der Spalten, für die zu prüfen ist, ob sie aktualisiert oder
eingefügt wurden.
Hinweise
Trigger werden häufig verwendet, um Geschäftsregeln und Datenintegrität zu erzwingen.
SQL Server bietet deklarative referenzielle Integrität (DRI) durch die Anweisungen für das
Erstellen einer Tabelle (ALTER TABLE und CREATE TABLE); DRI bietet jedoch keine
datenbankübergreifende referenzielle Integrität. Um referenzielle Integrität (Regeln über die
Beziehungen zwischen den Primär- und Fremdschlüsseln von Tabellen) zu erzwingen,
verwenden Sie Primär- und Fremdschlüsseleinschränkungen (die PRIMARY KEY- und
FOREIGN KEY-Schlüsselwörter der ALTER TABLE- und der CREATE TABLEAnweisung). Wenn Einschränkungen für die Triggertabelle vorhanden sind, werden sie vor
der Triggerausführung überprüft. Wenn PRIMARY KEY- oder FOREIGN KEYEinschränkungen verletzt werden, wird der Trigger nicht ausgeführt (ausgelöst).
Anmerkung Ob SQL Server eine leere Zeichenfolge als einzelnes Leerzeichen oder als echte
leere Zeichenfolge interpretiert, hängt von der Einstellung für sp_dbcmptlevel ab. Ist der
Kompatibilitätsgrad kleiner oder gleich 65, interpretiert SQL Server leere Zeichenfolgen als
einzelne Leerzeichen. Ist der Kompatibilitätsgrad gleich 70, interpretiert SQL Server leere
Zeichenfolgen als leere Zeichenfolgen. Weitere Informationen finden Sie unter
sp_dbcmptlevel.
Einschränkungen bei der Verwendung von Triggern
CREATE TRIGGER muss die erste Anweisung in einem Stapel sein und kann sich auf nur
eine Tabelle beziehen.
Ein Trigger kann nur in der aktuellen Datenbank erstellt werden; er darf jedoch auf Objekte
außerhalb der aktuellen Datenbank verweisen.
Wenn der Name des Triggerbesitzers angegeben ist (um den Trigger zu kennzeichnen),
kennzeichnen Sie den Tabellennamen auf die gleiche Weise.
Es ist möglich, in derselben CREATE TRIGGER-Anweisung dieselbe Triggeraktion für
mehrere Benutzeraktionen festzulegen (beispielsweise INSERT und UPDATE).
In einem Trigger kann jede beliebige SET-Anweisung angegeben werden. Die gewählte SETOption bleibt während der Ausführung des Triggers in Kraft und kehrt dann zur vorherigen
Einstellung zurück.
Wenn ein Trigger ausgelöst wird, werden die Ergebnisse, wie bei einer gespeicherten
Prozedur, an die aufrufende Anwendung zurückgegeben. Um zu verhindern, dass Ergebnisse
aufgrund einer Triggerauslösung an eine Anwendung zurückgegeben werden, verwenden Sie
in einem Trigger keine SELECT-Anweisungen, die Ergebnisse zurückgeben, oder
Anweisungen, die Variablenzuweisungen durchführen. Ein Trigger, der SELECTAnweisungen enthält, die Ergebnisse an den Benutzer zurückgeben, oder Anweisungen, die
Variablenzuweisungen durchführen, erfordert eine besondere Bearbeitung; diese
zurückgegebenen Ergebnisse müssten sonst in alle Anwendungen geschrieben werden, in
denen Änderungen an der Triggertabelle zulässig sind. Wenn Variablenzuweisungen in einem
Trigger erfolgen müssen, verwenden Sie eine SET NOCOUNT-Anweisung am Anfang des
Triggers, um die Rückgabe von Resultsets zu verhindern.
Ein Trigger kann nicht für eine Sicht erstellt werden.
Eine TRUNCATE TABLE-Anweisung wird von einem DELETE-Trigger nicht
berücksichtigt. Eine TRUNCATE TABLE-Anweisung wirkt sich zwar genauso aus wie eine
DELETE-Anweisung ohne WHERE-Klausel (durch sie werden alle Zeilen entfernt), sie wird
jedoch nicht protokolliert und kann deshalb keinen Trigger ausführen. Da der
Tabellenbesitzer standardmäßig die Berechtigung für die TRUNCATE TABLE-Anweisung
hat und diese nicht übertragbar ist, ist es nur für den Tabellenbesitzer relevant, dass durch eine
TRUNCATE TABLE-Anweisung ein DELETE-Trigger unbeabsichtigt umgangen werden
kann.
Die WRITETEXT-Anweisung, ob protokolliert oder nicht protokolliert, aktiviert keinen
Trigger.
Die folgenden Transact-SQL-Anweisungen sind in einem Trigger nicht zulässig:
ALTER
ALTER PROCEDURE
DATABASE
ALTER TRIGGER ALTER VIEW
CREATE
CREATE INDEX
DEFAULT
CREATE RULE
CREATE SCHEMA
CREATE TRIGGER CREATE VIEW
DISK INIT
DISK RESIZE
DROP DEFAULT DROP INDEX
DROP RULE
DROP TABLE
DROP VIEW
GRANT
LOAD LOG
RESTORE
DATABASE
REVOKE
RECONFIGURE
TRUNCATE
UPDATE STATISTICS
TABLE
ALTER TABLE
CREATE DATABASE
CREATE
PROCEDURE
CREATE TABLE
DENY
DROP DATABASE
DROP PROCEDURE
DROP TRIGGER
LOAD DATABASE
RESTORE LOG
Anmerkung Da SQL Server keine benutzerdefinierten Trigger in Systemtabellen unterstützt,
sollten Sie keine benutzerdefinierten Trigger für Systemtabellen erstellen.
Mehrere Trigger
SQL Server lässt das Erstellen mehrerer Trigger für jedes Datenänderungsereignis (DELETE,
INSERT oder UPDATE) zu. Wenn zum Beispiel CREATE TRIGGER FOR UPDATE für
eine Tabelle ausgeführt wird, die bereits einen UPDATE-Trigger hat, wird ein zusätzlicher
UPDATE-Trigger erstellt. In früheren Versionen war pro Tabelle nur ein Trigger für jedes
Datenänderungsereignis (INSERT, UPDATE, DELETE) zulässig.
Anmerkung Das Standardverhalten für CREATE TRIGGER (bei einem Kompatibilitätsgrad
von 70) ist das Hinzufügen zusätzlicher Trigger zu vorhandenen Triggern, wenn die
Triggernamen sich unterscheiden. Bei gleichen Triggernamen gibt SQL Server eine
Fehlermeldung zurück. Ist der Kompatibilitätsgrad jedoch kleiner oder gleich 65, ersetzen
neue Trigger, die mit der CREATE TRIGGER-Anweisung erstellt wurden, vorhandene
Trigger desselben Typs, selbst dann, wenn die Triggernamen verschieden sind. Weitere
Informationen finden Sie unter sp_dbcmptlevel.
Rekursive Trigger
SQL Server lässt auch das rekursive Aufrufen von Triggern zu, wenn die recursive triggersEinstellung in sp_dboption aktiviert ist.
Rekursive Trigger ermöglichen zwei Arten von Rekursion:


Indirekte Rekursion
Direkte Rekursion
Bei der indirekten Rekursion aktualisiert eine Anwendung die T1-Tabelle, was den TR1Trigger auslöst, der die T2-Tabelle aktualisiert. Dadurch wird der TR2-Trigger ausgelöst, der
die T1-Tabelle aktualisiert.
Bei der direkten Rekursion aktualisiert die Anwendung die T1-Tabelle, was den TR1-Trigger
auslöst, der die T1-Tabelle aktualisiert. Da die T1-Tabelle aktualisiert wurde, wird der TR1Trigger erneut ausgelöst usw.
Dieses Beispiel verwendet sowohl indirekte als auch direkte Triggerrekursion. Angenommen,
zwei Aktualisierungstrigger, TR1 und TR2, sind für die T1-Tabelle definiert. Der TR1Trigger aktualisiert die T1-Tabelle rekursiv. Eine UPDATE-Anweisung führt TR1 und TR2
je einmal aus. Außerdem löst die Ausführung von TR1 die Ausführung von TR1 (rekursiv)
und TR2 aus. Die inserted- und die deleted-Tabelle für einen gegebenen Trigger enthalten
Zeilen, die nur der UPDATE-Anweisung entsprechen, die den Trigger aufgerufen hat.
Anmerkung Das oben beschriebene Verhalten tritt nur dann ein, wenn die recursive
triggers-Einstellung für sp_dboption aktiviert ist. Es gibt keine vorgeschriebene Reihenfolge
für die Ausführung mehrerer für ein festgelegtes Ereignis definierter Trigger. Jeder Trigger
sollte unabhängig sein.
Wenn einer der Trigger eine ROLLBACK TRANSACTION-Anweisung ausführt, werden
unabhängig von der Schachtelungsebene keine weiteren Trigger ausgeführt.
Geschachtelte Trigger
Trigger können bis zu 32 Ebenen tief geschachtelt werden. Falls ein Trigger eine Tabelle
ändert, für die es einen anderen Trigger gibt, wird der zweite Trigger aktiviert und kann dann
seinerseits einen dritten Trigger aufrufen usw. Wenn ein Trigger in der Kette eine
Endlosschleife auslöst, wird die zulässige Schachtelungsebenenzahl überschritten und der
Trigger abgebrochen. Um geschachtelte Trigger zu deaktivieren, stellen Sie die Option nested
triggers von sp_configure auf 0 (deaktiviert) ein. In der Standardkonfiguration sind
geschachtelte Trigger zulässig. Wenn geschachtelte Trigger deaktiviert sind, sind rekursive
Trigger ebenfalls deaktiviert, unabhängig von der recursive triggers-Einstellung von
sp_dboption.
Verzögerte Namensauflösung
SQL Server lässt zu, dass gespeicherte Transact-SQL-Prozeduren, Trigger und Stapel auf
Tabellen verweisen, die zur Kompilierzeit noch nicht vorhanden sind. Diese Fähigkeit wird
verzögerte Namensauflösung genannt. Wenn gespeicherte Transact-SQL-Prozeduren, Trigger
oder Stapel jedoch auf eine Tabelle verweisen, die in der gespeicherten Prozedur oder dem
Trigger definiert ist, wird nur dann eine Warnung bei der Erstellung ausgegeben, wenn der
(durch Ausführung von sp_dbcmptlevel eingestellte) Kompatibilitätsgrad 65 ist. Zur
Kompilierzeit wird eine Warnung ausgegeben, wenn ein Stapel verwendet wird. Eine
Fehlermeldung wird zur Ausführungszeit zurückgegeben, wenn die Tabelle, auf die verwiesen
wird, nicht vorhanden ist.
Berechtigung
Die CREATE TRIGGER-Berechtigung liegt standardmäßig beim Besitzer der Tabelle, für die
der Trigger definiert ist, oder bei Mitgliedern der festen Datenbankrollen db_owner und
db_ddladmin und ist nicht übertragbar.
Beispiele
A. Verwenden eines Triggers mit einer Erinnerungsmeldung
In diesem Beispiel wird jedes Mal, wenn jemand versucht, Daten in die titles-Tabelle
einzufügen bzw. vorhandene Daten zu ändern, eine Meldung an den Client ausgegeben.
Anmerkung Die Meldung 50009 ist eine benutzerdefinierte Meldung, die in sysmessages
gespeichert ist. Informationen zum Erstellen von benutzerdefinierten Meldungen finden Sie
unter sp_addmessage.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)
GO
B. Verwenden eines Triggers mit einer E-Mail-Erinnerungsnachricht
In diesem Beispiel wird eine E-Mail an die angegebene Person (MaryM) gesandt, wenn die
titles-Tabelle geändert wird.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GO
C. Verwenden eines Geschäftsregeltriggers zwischen employee- und jobs-Tabelle
Da sich CHECK-Einschränkungen nur auf Spalten beziehen können, für die die Spalten- oder
Tabelleneinschränkung definiert wurde, müssen tabellenübergreifende Einschränkungen (in
diesem Fall Geschäftsregeln) als Trigger definiert werden.
Dieses Beispiel erstellt einen Trigger, der beim Einfügen oder Aktualisieren einer
Einstufungsebene für Angestellte überprüft, ob sich die angegebene Einstufungsebene
(job_lvls), von der das Gehalt abhängt, innerhalb des für die jeweilige Position definierten
Bereichs befindet. Um den geeigneten Bereich zu erhalten, muss ein Verweis auf die jobsTabelle angelegt werden.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
SELECT @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
RAISERROR ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
END
D. Verwenden der verzögerten Namensauflösung
Dieses Beispiel erstellt zwei Trigger, um die verzögerte Namensauflösung zu erläutern.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig1' AND type = 'TR')
DROP TRIGGER trig1
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER trig1
on authors
FOR INSERT, UPDATE, DELETE
AS
SELECT a.au_lname, a.au_fname, x.info
FROM authors a INNER JOIN does_not_exist x
ON a.au_id = x.au_id
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig1'
-- Creating a trigger on an existing table, but with a nonexistent
-- column.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig2' AND type = 'TR')
DROP TRIGGER trig2
GO
CREATE TRIGGER trig2
ON authors
FOR INSERT, UPDATE
AS
DECLARE @fax varchar(12)
SELECT @fax = phone
FROM authors
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig2'
E. Verwenden von COLUMNS_UPDATED
Dieses Beispiel erstellt zwei Tabellen: employeeData und auditEmployeeData. Die
employeeData-Tabelle, die vertrauliche Gehaltsinformationen enthält, kann von Mitgliedern
der Personalabteilung geändert werden. Wenn die Sozialversicherungsnummer (SSN), das
Jahresgehalt oder die Kontonummer des Angestellten geändert wird, wird ein
Überwachungsdatensatz erzeugt und in die auditEmployeeData-Tabelle eingefügt.
Mithilfe der COLUMNS_UPDATED()-Funktion kann schnell getestet werden, ob
Änderungen an diesen Spalten, die vertrauliche Informationen über Mitarbeiter enthalten,
vorgenommen wurden.
USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employeeData')
DROP TABLE employeeData
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'auditEmployeeData')
DROP TABLE auditEmployeeData
GO
CREATE TABLE employeeData (
emp_id
int NOT NULL,
emp_bankAccountNumber char (10) NOT NULL,
emp_salary
int NOT NULL,
emp_SSN
char (11) NOT NULL,
emp_lname
nchar (32) NOT NULL,
emp_fname
nchar (32) NOT NULL,
emp_manager
int NOT NULL
)
GO
CREATE TABLE auditEmployeeData (
audit_log_id
uniqueidentifier DEFAULT NEWID(),
audit_log_type
char (3) NOT NULL,
audit_emp_id
int NOT NULL,
audit_emp_bankAccountNumber char (10) NULL,
audit_emp_salary
int NULL,
audit_emp_SSN
char (11) NULL,
audit_user
sysname DEFAULT SUSER_SNAME(),
audit_changed
datetime DEFAULT GETDATE()
)
GO
CREATE TRIGGER updEmployeeData
ON employeeData
FOR update AS
-- Check whether columns 2, 3 or 4 has been updated. If any or all of
-- columns 2, 3 or 4 have been changed, create an audit record.
-- The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14
-- To check if all columns 2, 3, and 4 are updated, use = 14 in place of
-- >0 (below).
IF (COLUMNS_UPDATED() & 14) > 0
-- Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3,
-- and 4 are updated.
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'OLD',
del.emp_id,
del.emp_bankAccountNumber,
del.emp_salary,
del.emp_SSN
FROM deleted del
-- Audit NEW record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'NEW',
ins.emp_id,
ins.emp_bankAccountNumber,
ins.emp_salary,
ins.emp_SSN
FROM inserted ins
END
GO
--Inserting a new employee does not cause the UPDATE trigger to fire.
INSERT INTO employeeData
VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)
GO
-- Updating the employee record for employee number 101 to change the
-- salary to 51000 causes the UPDATE trigger to fire and an audit trail
-- to be produced.
UPDATE employeeData
SET emp_salary = 51000
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO
--Updating the employee record for employee number 101 to change both the bank account number and social
security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.
UPDATE employeeData
SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO
Siehe auch
ALTER TABLE
ALTER TRIGGER
CREATE TABLE
DROP TRIGGER
sp_depends
sp_help
sp_helptext
sp_rename
sp_spaceused
Vorgang protokollieren:
create table mittel protokoll
(pr_nr char(4) null,
benutzer char(16) null,
zeit datetime null,
mittel_alt float null,
mittel_neu float null)
create trigger mittel_aendern
on projekt
for update
as if update (mittel)
begin
declare @wert_vor float
declare @wert_danach float
declare @projekt_Nummer char(4)
select @wert_vor = (select mittel from deleted)
select @wert_danach = (select mittel from
inserted)
select @projekt_Nummer = (select pr_nr from
deleted)
insert into mittel_protokoll
values (@projekt_nummer, user_name(), getdate(),
@wert_vor, @wert_danach)
end
Annahme: FOR EACH ROW
(entspricht der Vereinfachung des allgemeinen
Falls)
Einhaltung einer Geschäftsregel:
create trigger tr_gesamt_mittel
on projekt
for update
as if update (mittel)
begin
declare @alte_summe float
declare @neue_summe float
select @alte_summe =(select sum(mittel) from
deleted)
select @neue_summe =(select sum(mittel) from
inserted)
if @neue_summe > @alte_summe * 1.5
begin
print 'Die Änderung der Projektmittel wurde
nicht ausgeführt'
rollback transaction
end
else
print 'Die Änderung der Projektmittel wurde
ausgeführt'
end
Einhaltung der referentiellen Integrität:
create trigger tr_refint_arb
on arbeiten
for insert, update
as if update (m_nr)
begin
if (select mitarbeiter.m_nr
from mitarbeiter, inserted
where mitarbeiter.m_nr = inserted.m_nr) is
null begin
print 'Das Tupel konnte nicht eingefügt/geändert
werden'
rollback transaction
end
else print 'Das Tupel wurde eingefügt/geändert'
end
Integritätsverletzungen zurücksetzen:
create trigger tr_refint_arb2
on mitarbeiter
for delete, update
as if update (m_nr)
begin
if (select count(*)
from arbeiten, deleted
where arbeiten.m_nr = deleted.m_nr) > 0
begin
rollback transaction
print 'Das Tupel konnte nicht gelöscht/geändert
werden'
end
else print 'Das Tupel wurde gelöscht/geändert'
end
INSTEAD_OF-Trigger:
create table bestellung
(bestell_nr int not null,
preis money not null,
menge int not null,
bestell_datum datetime not null,
gesamt_preis as preis * menge,
liefer_datum as dateadd (day, 7, bestell_datum))
create view alle_bestellungen
as
select bestell_nr, preis, menge, bestell_datum,
gesamt_preis, liefer_datum
from bestellung
create trigger tr_bestellung
on alle_bestellungen
instead of insert
as
begin
insert into bestellung
select bestell_nr, preis, menge, bestell_datum
from inserted
end
DDL-Trigger:
CREATE TRIGGER [ schema_name ] trigger_name
ON { ALL SERVER | DATABASE }
[ WITH { ENCRYPTION | EXECUTE AS klausel } ]
{ FOR | AFTER } { event_group | event_type }
AS { batch | EXTERNAL NAME method_name }
event_group - Name einer vordefinierten Gruppe von
Transact-SQL-Anweisungen. Der DDLTrigger wird jedes Mal aktiviert, wenn
eine der Anweisungen aus dieser Gruppe
ausgeführt wird.
event_type - Name des Ereignisses, das die
Aktivierung
des DDL-Triggers verursacht.
Beispiel:
CREATE TRIGGER prevent_drop_triggers
ON DATABASE
FOR DROP TRIGGER
AS
BEGIN
PRINT 'Sie muessen "prevent_drop_triggers"
deaktivieren,
um einen Trigger loeschen zu koennen'
ROLLBACK
END
?
Trigger und prozedurale Sprachen:
Genauso wie Funktionen und Prozeduren können Trigger mit
Hilfe prozeduraler Sprachen, die zur Common Language
Runtime (CLR) gehören, implementiert werden.
Für die Implementierung eines Triggers mit Hilfe von C# bzw.
Visual Basic 2005 sind folgende Schritte notwendig:
–
Trigger mit Hilfe von C# bzw. Visual Basic 2005
implementieren und übersetzen,
–
mittels CREATE ASSEMBLY-Anweisung eine ausführbare Version
(Executable File) des Programms erstellen,
–
den Trigger mit Hilfe der CREATE TRIGGER-Anweisung
speichern.
Beispiel mit C#:
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class StoredProcedures {
public static void Modify Budget()
{
SqlTriggerContext context
SqlContext.TriggerContext;
if(context.IsUpdatedColumn(2))
float budget_old;
float budget_new;
string pr_nr;
SqlConnection conn
new SqlConnection("context connection=true");
conn.Open();
SqlCommand cmd conn.CreateCommand();
cmd.CommandText "SELECT mittel FROM deleted";
budget_old =
(float)Convert.ToDouble(cmd.ExecuteScalar());
cmd.CommandText="SELECT mittel FROM inserted";
budget_new =
(float)Convert.ToDouble(cmd.ExecuteScalar());
cmd.CommandText = "SELECT pr_nr FROM deleted";
pr_nr = Convert.ToString(cmd.ExecuteScalar());
cmd.CommandText =
"INSERT INTO mittel protokoll
VALUES(@pr_nr, USER_NAME(), GETDATE(),
@budget_old, @budget_new)";
cmd.Parameters.AddWithValue("@pr_nr",pr_nr);
cmd.Parameters.AddWithValue("@budget_old",budget_old)
;
cmd.Parameters.AddWithValue("@budget_new",budget_new)
;
cmd.ExecuteNonQuery();
C#-Programm übersetzen:
csc /target:library C#_Beispiel.cs
/reference:"c:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\Binn\sqlaccess.dll"