Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count). but finally realize that it has wrong data without any Extension at the end of the data, in One of the column of the table, so now I want to change the column data of multiple rows based on specific ID? I just want to add '.jpg' at the end on these data.To verify that columns C2, C3, and C4 are all updated (with table t1 having an UPDATE trigger), follow the syntax with & 14.To test whether only column C2 is updated, specify & 2.
If ' N' is not specified, SQL Server converts the string to the code page that corresponds to the default collation of the database or column.COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than eight columns, with the least significant byte being the leftmost.COLUMNS_UPDATED returns TRUE for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.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.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.To test for updates or inserts to specific columns, follow the syntax with a bitwise operator and an integer bitmask of the columns being tested.For example, table t1 contains columns C1, C2, C3, C4, and C5.Transact-SQL Syntax Conventions varbinary COLUMNS_UPDATED tests for UPDATE or INSERT actions performed on multiple columns.To test for UPDATE or INSERT attempts on one column, use UPDATE().To test whether all columns 2, 3, and 4 are updated, use = 14 instead of 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.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.