Aracılığıyla paylaş

COLUMNS_UPDATED (Transact-SQL)

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen ÖrneğiMicrosoft Fabric'te SQL veritabanı

Bu işlev, bir tablonun veya görünümün eklenen veya güncelleştirilmiş sütunlarını gösteren bir varbinary bit deseni döndürür. Tetikleyicinin belirli eylemleri yürütip yürütmediğini test etmek için bir Transact-SQL COLUMNS_UPDATED veya INSERT tetikleyici gövdesinin içinde herhangi bir yerde kullanınUPDATE.

Transact-SQL söz dizimi kuralları

Sözdizimi

COLUMNS_UPDATED ( )

Dönüş türleri

varbinary

Açıklamalar

COLUMNS_UPDATEDveya UPDATE birden çok sütunda gerçekleştirilen eylemler için INSERT testler. Bir sütunu test etmek veya UPDATE bu sütunda deneme yapmak için INSERTUPDATE() kullanın.

COLUMNS_UPDATED soldan sağa sıralanmış bir veya daha fazla bayt döndürür. Her baytın en sağdaki biti en az önemli bittir. En soldaki baytın en sağdaki biti tablodaki ilk tablo sütununu, soldaki sonraki bit ikinci sütunu temsil eder ve bu şekilde devam eder. COLUMNS_UPDATED tetikleyicinin oluşturulduğu tablo sekizden fazla sütun içeriyorsa birden çok bayt döndürür ve en az önemli bayt en soldaki bayt olur. COLUMNS_UPDATED, sütunlarda açık değerler veya örtük (NULL) değerler eklendiğinden eylemlerdeki TRUE tüm sütunlar için döndürürINSERT.

Güncelleştirmeleri veya belirli sütunlara eklemeleri test etmek için, bit düzeyinde işleç ve test edilen sütunların tamsayı bit maskesiyle söz dizimini izleyin. Örneğin, tablonun t1 , , C1C2, C3ve C4sütunlarını C5içerdiğini varsayalım. , C2ve sütunlarının C3başarıyla güncelleştirildiğini doğrulamak için (tetikleyicisi olan bir C4 tabloylat1), ile UPDATEsöz dizimini & 14 izleyin. Yalnızca sütunun C2 güncelleştirilip güncelleştirilmediğini test etmek için belirtin & 2. Gerçek örnekler için bkz. Örnek A ve Örnek B .

bir Transact-SQL COLUMNS_UPDATED veya INSERT tetikleyicinin içinde herhangi bir yerde kullanınUPDATE. Bu bir tetikleyicinin dışında çalıştırılırsa NULL döndürülür.

Görünümün ORDINAL_POSITIONINFORMATION_SCHEMA.COLUMNS sütunu, tarafından COLUMNS_UPDATEDdöndürülen sütunların bit düzeniyle uyumlu değildir. ile COLUMNS_UPDATEDuyumlu bir bit deseni elde etmek için, aşağıdaki örnekte gösterildiği gibi görünümü sorgularken ColumnID sistem işlevinin özelliğine COLUMNPROPERTY başvurunINFORMATION_SCHEMA.COLUMNS.

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

Bir tetikleyici bir sütuna uygulanırsa, COLUMNS_UPDATED sütun değeri değişmeden kalsa bile veya trueolarak 1 döndürür. Bu tasarım gereğidir ve tetikleyici, ekleme/güncelleştirme/silme işleminin izin verilip verilmediğini belirleyen iş mantığını uygulamalıdır.

Sütun kümeleri

Tabloda bir sütun kümesi tanımlandığında işlev COLUMNS_UPDATED aşağıdaki şekillerde davranır:

  • Sütun kümesinin üye sütununu açıkça güncelleştirirken, bu sütuna karşılık gelen bit olarak ayarlanır 1ve sütun kümesi biti olarak 1ayarlanır.

  • Bir sütun kümesini açıkça güncelleştirirken, sütun kümesi biti olarak 1ayarlanır ve bu tablodaki tüm seyrek sütunların bitleri olarak 1ayarlanır.

  • Ekleme işlemleri için tüm bitler olarak 1ayarlanır.

    Bir sütun kümesindeki değişiklikler, sütun kümesindeki tüm sütunların bitlerinin olarak sıfırlanmasına 1neden olduğundan, sütun kümesindeki değişmemiş sütunlar değiştirilmiş olarak görünür. Sütun kümeleri hakkında daha fazla bilgi için bkz. Sütun Kümelerini Kullanma .

Örnekler

A. Tablonun ilk sekiz sütununu test etmek için COLUMNS_UPDATED kullanma

Bu örnek iki tablo oluşturur: employeeData ve auditEmployeeData. Tabloda employeeData hassas çalışan bordro bilgileri bulunur ve insan kaynakları departmanı üyeleri bunları değiştirebilir. Bir çalışanın sosyal güvenlik numarası (SSN), yıllık maaşı veya banka hesabı numarası değişirse, denetim kaydı oluşturulur ve denetim tablosuna auditEmployeeData eklenir.

işleviyle COLUMNS_UPDATED() , hassas çalışan bilgilerini içeren sütunlarda yapılan değişiklikleri hızla test edebiliriz. COLUMNS_UPDATED() Bu şekilde kullanmak yalnızca tablodaki ilk sekiz sütunda yapılan değişiklikleri algılamaya çalışırken çalışır.

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. Sekizden fazla sütunu test etmek için COLUMNS_UPDATED kullanma

İlk sekiz tablo sütunu dışındaki sütunları etkileyen güncelleştirmeleri test etmek için işlevini kullanarak SUBSTRING tarafından COLUMNS_UPDATEDdöndürülen doğru biti test edin. Bu örnek, tablodaki , ve sütunlarını 3etkileyen güncelleştirmeleri sınar5.9AdventureWorks2022.Person.Person

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

İlgili içerik

  • Last updated on