Sdílet prostřednictvím

COLUMNS_UPDATED (Transact-SQL)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL databáze v Microsoft Fabric

Tato funkce vrátí varbinární bitový vzor označující vložené nebo aktualizované sloupce tabulky nebo zobrazení. Pomocí COLUMNS_UPDATED libovolného místa v těle Transact-SQL INSERT nebo UPDATE triggeru otestujte, jestli má trigger provádět určité akce.

Transact-SQL konvence syntaxe

Syntaxe

COLUMNS_UPDATED ( )

Návratové typy

varbinary

Poznámky

COLUMNS_UPDATED testy nebo UPDATEINSERT akce prováděné na více sloupcích. K otestování nebo UPDATE pokusů o INSERT jeden sloupec použijte funkci UPDATE().

COLUMNS_UPDATED vrátí jeden nebo více bajtů, které jsou seřazeny zleva doprava. Bit nejvíce vpravo každého bajtu je nejméně významný bit. Bit nejvíce zprava nejvíce bajtů představuje první sloupec tabulky v tabulce, další bit vlevo představuje druhý sloupec atd. COLUMNS_UPDATED vrátí více bajtů, pokud tabulka, na které je trigger vytvořen, obsahuje více než osm sloupců, přičemž nejméně významný bajt je nejvíce vlevo. COLUMNS_UPDATED vrátí TRUE pro všechny sloupce v INSERT akcích, protože sloupce mají vložené explicitní hodnoty nebo implicitní hodnoty (NULL).

Pokud chcete otestovat aktualizace nebo vložení do konkrétních sloupců, použijte syntaxi s bitovým operátorem a celočíselnou maskou testovaných sloupců. Řekněme například, že tabulka t1 obsahuje sloupce C1, C2, C3, C4, a C5. Chcete-li ověřit, že sloupce , a všechny úspěšně aktualizovány (s tabulkou C2 s triggeremC3), postupujte podle syntaxe pomocí C4.t1UPDATE& 14 Chcete-li otestovat, zda je aktualizován pouze sloupec C2 , zadejte & 2. Podívejte se na příklad A a příklad B pro skutečné příklady.

Používejte COLUMNS_UPDATED kdekoli uvnitř Transact-SQL INSERT nebo UPDATE triggeru. Pokud se spustí mimo trigger, vrátí se hodnota NULL.

ORDINAL_POSITION Sloupec INFORMATION_SCHEMA.COLUMNS zobrazení není kompatibilní s bitovým vzorem sloupců vrácených funkcí COLUMNS_UPDATED. Chcete-li získat bitový vzor kompatibilní s COLUMNS_UPDATED, odkazovat na ColumnID vlastnost COLUMNPROPERTY systémové funkce při dotazování INFORMATION_SCHEMA.COLUMNS zobrazení, jak je znázorněno v následujícím příkladu.

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';

Pokud se aktivační událost vztahuje na sloupec, COLUMNS_UPDATED vrátí se jako true nebo 1, i když hodnota sloupce zůstane beze změny. Jedná se o návrh a trigger by měl implementovat obchodní logiku, která určuje, jestli je operace vložení, aktualizace/odstranění povolená nebo ne.

Sady sloupců

Pokud je sada sloupců definovaná v tabulce, COLUMNS_UPDATED funkce se chová následujícími způsoby:

  • Při explicitní aktualizaci člena sloupce sady sloupců je odpovídající bit pro tento sloupec nastaven na 1a bit sady sloupců je nastaven na 1.

  • Při explicitní aktualizaci sady sloupců je bit sady sloupců nastaven na 1a bity pro všechny řídké sloupce v této tabulce jsou nastaveny na 1.

  • Pro operace vložení jsou všechny bity nastaveny na 1.

    Vzhledem k tomu, že změny v sadě sloupců způsobují, že se bity všech sloupců ve sloupci nastaveny na resetují 1, budou změněny nezměněné sloupce v sadě sloupců. Další informace o sadách sloupců najdete v tématu Použití sad sloupců .

Příklady

A. Použití COLUMNS_UPDATED k otestování prvních osmi sloupců tabulky

Tento příklad vytvoří dvě tabulky: employeeData a auditEmployeeData. V employeeData tabulce jsou citlivé informace o mzdách zaměstnanců a členové oddělení lidských zdrojů ho můžou upravit. Pokud se změní číslo sociálního pojištění (SSN), roční plat nebo číslo bankovního účtu zaměstnance, vygeneruje se záznam auditu a vloží se auditEmployeeData do tabulky auditu.

COLUMNS_UPDATED() Pomocí funkce můžeme rychle otestovat všechny změny ve sloupcích obsahujících citlivé informace o zaměstnanci. Tímto COLUMNS_UPDATED() způsobem funguje pouze při pokusu o zjištění změn prvních osmi sloupců v tabulce.

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. Použití COLUMNS_UPDATED k otestování více než osmi sloupců

K otestování aktualizací, které ovlivňují jiné sloupce než prvních osm sloupců tabulky, použijte SUBSTRING funkci k otestování správného bitu vráceného COLUMNS_UPDATEDfunkcí . Tento příklad testů aktualizací ovlivňujících sloupce 3, 5a 9 v AdventureWorks2022.Person.Person tabulce.

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

Související obsah

  • Last updated on