Updating columns in sql

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.

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.

If the social security number (SSN), yearly salary, or bank account number for an employee is changed, an audit record is generated and inserted into the USE Adventure Works2012; GO IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.

The ORDINAL_POSITION column of the INFORMATION_SCHEMA. COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED.

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.

Common table expressions can also be used with the SELECT, INSERT, DELETE, and CREATE VIEW statements.

For more information, see WITH common_table_expression (Transact-SQL). For information about table hints, see Table Hints (Transact-SQL).

Search for updating columns in sql:

updating columns in sql-73

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “updating columns in sql”

  1. Romance Academy began life back in September 2005 when the BBC showed a three part documentary called No Sex Please We’re Teenagers which followed the lives and loves of the very first, "Romance Academy" - Twelve teenagers from north London were challenged to give up sex for 5 months, so that they may explore the true value of relationships and the positive and negatives of delaying sexual activity.

  2. However, when ever the King of Lanka was weak, the three "kingdoms" functioned as quasi-independent units fighting each other for hegemony, often allying with Indian regional princes in the Chola, Pandyan or Northern India.