Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Gilt für:
SQL ServerAzure SQL-DatenbankVerwaltete Azure SQL-InstanzSQL-Datenbank in Microsoft Fabric
Diese Funktion gibt ein varbinary-Bitmuster zurück, das die eingefügten oder aktualisierten Spalten der Tabelle oder Ansicht anzeigt. Verwenden Sie COLUMNS_UPDATED eine beliebige Stelle im Textkörper eines Transact-SQL INSERT oder UPDATE Triggers, um zu testen, ob der Trigger bestimmte Aktionen ausführen soll.
Transact-SQL-Syntaxkonventionen
Syntax
COLUMNS_UPDATED ( )
Rückgabetypen
varbinär
Bemerkungen
COLUMNS_UPDATED Tests für UPDATE oder INSERT Aktionen, die für mehrere Spalten ausgeführt werden. Verwenden Sie UPDATE, um auf eine Spalte zu INSERT testen oder versuchen.
COLUMNS_UPDATED gibt mindestens ein Byte in der Reihenfolge von links nach rechts zurück. Das äußere rechte Bit stellt in jedem Byte das niederwertigste Bit dar. Das äußere rechte Bit im Byte ganz links stellt die erste Tabellenspalte in der Tabelle dar. Das nächste Bit links davon stellt die zweite Spalte dar usw.
COLUMNS_UPDATED gibt mehrere Bytes zurück, falls die Tabelle, für die der Trigger erstellt wird, mehr als acht Spalten enthält, wobei das niederwertigste Byte ganz links steht.
COLUMNS_UPDATEDgibt für alle Spalten in TRUE Aktionen zurückINSERT, da die Spalten entweder explizite Werte oder implizite (NULL)-Werte eingefügt haben.
Verwenden Sie zum Testen, ob in bestimmten Spalten Updates oder Einfügungen vorgenommen wurden, die Syntax mit einem bitweisen Operator und einer ganzzahligen Bitmaske der getesteten Spalten. Angenommen, die Tabelle t1 enthält Spalten C1, C2, , C3, C4und C5. Um zu überprüfen, ob Spalten C2, C3und C4 alle erfolgreich aktualisiert wurden (mit Tabelle t1 mit einem UPDATE Auslöser), folgen Sie der Syntax mit & 14. Um zu testen, ob nur Die Spalte C2 aktualisiert wird, geben Sie an & 2. Tatsächliche Beispiele finden Sie unter Beispiel A und Beispiel B.
Verwenden Sie COLUMNS_UPDATED eine beliebige Stelle innerhalb eines Transact-SQL INSERT oder UPDATE Triggers. Wenn dies außerhalb eines Triggers ausgeführt wird, wird ein NULL-Wert zurückgegeben.
Die ORDINAL_POSITION Spalte der INFORMATION_SCHEMA.COLUMNS Ansicht ist nicht mit dem Bitmuster von Spalten kompatibel, die von COLUMNS_UPDATED. Für ein mit COLUMNS_UPDATED kompatibles Bitmuster verweisen Sie, wie im folgenden Beispiel dargestellt, auf die ColumnID-Eigenschaft der COLUMNPROPERTY-Systemfunktion, wenn Sie die INFORMATION_SCHEMA.COLUMNS-Ansicht abfragen.
SELECT TABLE_NAME, COLUMN_NAME,
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks2022.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Person';
Wenn ein Trigger für eine Spalte gilt, wird der Wert COLUMNS_UPDATED als true oder 1 zurückgegeben. Dies ist auch der Fall, wenn der Spaltenwert unverändert bleibt. Dies wurde mit Absicht so eingerichtet, und der Trigger sollte eine Geschäftslogik implementieren, die bestimmt, ob der Einfüge-/Update-/Löschvorgang zulässig ist oder nicht.
Spaltensätze
Wenn ein Spaltensatz für eine Tabelle definiert wird, verhält sich die Funktion COLUMNS_UPDATED wie folgt:
Wenn eine Elementspalte des Spaltensatzes explizit aktualisiert wird, wird das entsprechende Bit für diese Spalte auf
1festgelegt, und das Spaltensatzbit wird auf1.Beim expliziten Aktualisieren eines Spaltensatzes wird das Spaltensatzbit auf
1, und die Bits für alle sparse Spalten in dieser Tabelle sind auf festgelegt1.Bei Einfügevorgängen werden alle Bits auf
1.Da Änderungen an einem Spaltensatz dazu führen, dass die Bits aller Spalten in der Spalte zurückgesetzt werden
1, werden unveränderte Spalten in einem Spaltensatz geändert. Weitere Informationen zu Spaltensätzen finden Sie unter Verwenden von Spaltensätzen.
Beispiele
Ein. Verwenden von COLUMNS_UPDATED zum Testen der ersten acht Spalten einer Tabelle
In diesem Beispiel werden zwei Tabellen erstellt: employeeData und auditEmployeeData. Die employeeData-Tabelle enthält vertrauliche Gehaltsinformationen und kann von Mitgliedern der Personalabteilung geändert werden. Wenn die Sozialversicherungsnummer (SSN, Social Security Number), das Jahresgehalt oder die Kontonummer eines Mitarbeiters geändert wird, wird ein Überwachungsdatensatz generiert und in die auditEmployeeData-Überwachungstabelle eingefügt.
Mit der COLUMNS_UPDATED()-Funktion kann auf schnelle Weise getestet werden, ob Änderungen an Spalten mit vertraulichen Informationen zu Mitarbeitern vorgenommen wurden.
COLUMNS_UPDATED() kann jedoch nur dann auf diese Weise verwendet werden, wenn Sie Änderungen an den ersten acht Spalten in der Tabelle erkennen möchten.
USE AdventureWorks2022;
GO
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 dbo.employeeData
(
emp_id INT NOT NULL PRIMARY KEY,
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 dbo.auditEmployeeData
(
audit_log_id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
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 dbo.updEmployeeData
ON dbo.employeeData
AFTER UPDATE AS
/* Check whether columns 2, 3 or 4 have been updated. If any or all
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.
This bitmask translates into base_10 as: 2 + 4 + 8 = 14.
To test whether all columns 2, 3, and 4 are updated, use = 14 instead of > 0
(below). */
IF (COLUMNS_UPDATED() & 14) > 0
/* Use IF (COLUMNS_UPDATED() & 14) = 14 to see whether all columns 2, 3,
and 4 are updated. */
BEGIN
-- Audit OLD record.
INSERT INTO dbo.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 AS del;
-- Audit NEW record.
INSERT INTO dbo.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 AS 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 dbo.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 dbo.employeeData
SET emp_bankAccountNumber = '133146A0',
emp_SSN = 'R-M53550M'
WHERE emp_id = 101;
GO
SELECT * FROM dbo.auditEmployeeData;
GO
B. Verwenden von COLUMNS_UPDATED zum Testen von mehr als acht Spalten
Wenn Sie testen möchten, ob Updates in anderen Spalten als den ersten acht Spalten in einer Tabelle ausgeführt wurden, verwenden Sie die SUBSTRING-Funktion, um das richtige durch COLUMNS_UPDATED zurückgegebene Bit zu testen. In diesem Beispiel wird getestet, ob Updates vorhanden sind, die die Spalten 3, 5 und 9 in der Tabelle AdventureWorks2022.Person.Person betreffen.
USE AdventureWorks2022;
GO
IF OBJECT_ID(N'Person.uContact2', N'TR') IS NOT NULL
DROP TRIGGER Person.uContact2;
GO
CREATE TRIGGER Person.uContact2
ON Person.Person
AFTER UPDATE AS
IF ((SUBSTRING(COLUMNS_UPDATED(), 1, 1) & 20 = 20)
AND (SUBSTRING(COLUMNS_UPDATED(), 2, 1) & 1 = 1))
PRINT 'Columns 3, 5 and 9 updated';
GO
UPDATE Person.Person
SET NameStyle = NameStyle,
FirstName = FirstName,
EmailPromotion = EmailPromotion;
GO