COLUMNS_UPDATED is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.
Transact-SQL Syntax Conventions varbinary COLUMNS_UPDATED tests for UPDATE or INSERT actions performed on multiple columns.
Appreciate if someone can help me built my code further.
Here is my code : ******************************** CREATE OR REPLACE TRIGGER TT AFTER INSERT ON GL_ORDER REFERENCING NEW AS NEW OLD AS OLD --FOR EACH ROW DECLARE tmp Var NUMBER; i NUMBER; i := 0; SELECT 1 INTO i FROM GL_ORDER WHERE TRANS_KEY IS NULL; SELECT GL_INCR_KEY.
The ORDINAL_POSITION column of the INFORMATION_SCHEMA. COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED.
To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA '.' TABLE_NAME), COLUMN_NAME, ' Column ID') AS COLUMN_ID FROM Adventure Works2012. COLUMNS WHERE TABLE_NAME = ' Person'; table holds sensitive employee payroll information and can be modified by members of the human resources department.
TABLES WHERE TABLE_NAME = 'audit Employee Data') DROP TABLE audit Employee Data; GO CREATE TABLE dbo.employee Data ( emp_id int NOT NULL PRIMARY KEY, emp_bank Account Number 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.audit Employee Data ( audit_log_id uniqueidentifier DEFAULT NEWID() PRIMARY KEY, audit_log_type char (3) NOT NULL, audit_emp_id int NOT NULL, audit_emp_bank Account Number 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 Employee Data ON dbo.employee Data AFTER UPDATE AS /*Check whether columns 2, 3 or 4 have been updated.
What I love about writing SQL Tuning articles is that I very rarely end up publishing the findings I set out to achieve. We have a table containing years worth of data, most of which is static; we are updating selected rows that were recently inserted and are still volatile. For the purposes of the test, we will assume that the target table of the update is arbitrarily large, and we want to avoid things like full-scans and index rebuilds.INSERT INTO dbo.audit Employee Data (audit_log_type, audit_emp_id, audit_emp_bank Account Number, audit_emp_salary, audit_emp_SSN) SELECT ' OLD', del.emp_id, del.emp_bank Account Number, del.emp_salary, del.emp_SSN FROM deleted del; -- Audit NEW record.INSERT INTO dbo.audit Employee Data (audit_log_type, audit_emp_id, audit_emp_bank Account Number, audit_emp_salary, audit_emp_SSN) SELECT ' NEW', ins.emp_id, ins.emp_bank Account Number, 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 employee Data 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.employee Data SET emp_salary = 51000 WHERE emp_id = 101; GO SELECT * FROM audit Employee Data; 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.employee Data SET emp_bank Account Number = '133146A0', emp_SSN = ' R-M53550M' WHERE emp_id = 101; GO SELECT * FROM dbo.audit Employee Data; GO USE Adventure Works2012; GO IF OBJECT_ID (N' Person.u Contact2', N' TR') IS NOT NULL DROP TRIGGER Person.u Contact2; GO CREATE TRIGGER Person.u Contact2 ON Person.For example, table t1 contains columns C1, C2, C3, C4, and C5.To verify that columns C2, C3, and C4 are all updated (with table t1 having an UPDATE trigger), follow the syntax with & 14.With this one, I set out to demonstrate the advantages of PARALLEL DML, didn't find what I thought I would, and ended up testing 8 different techniques to find out how they differed. The methods covered include both PL/SQL and SQL approaches.