SQL Server Performance Forum – Threads Archive

copying columns

I have two columns (CustomerID and DivisionID) in a table (Case) that I would like to get updated whenever the columns (CustomerID and DivisionID) in table (Proj) change. I could do this programmatically from my C++ program, but I would have to manage this in many different areas. Someone suggested using a trigger that ran whenever the values changed it "Proj". Is this the way to go? Where could I go to find out how to write a trigger? I noticed that it’s good practice to check that the columns I want to update in "Case" have actually changed in "Proj" before I run the trigger (i.e. the row was updated, but the two columns stayed the same, so don’t run the code in the trigger). How do I go about this? Thanks for all your help, Ben

---

Ben, Triggers could indeed do this for you. Another way might be to move your update away from your c++ program, and into a stored procedure. In that stored procedure, you can begin a transaction, do both updates, and if both updates are successful, you commit, else if either fails, you rollback. The SP is, imho, the way to go. here is an example for BOL for the trigger (search index "Create trigger" in BOL)
E. Use COLUMNS_UPDATED
This example creates two tables: an employeeData table and an auditEmployeeData table. The employeeData table, which holds sensitive employee payroll information, can be modified by members of the human resources department. If the employee’s social security number (SSN), yearly salary, or bank account number is changed, an audit record is generated and inserted into the auditEmployeeData audit table. By using the COLUMNS_UPDATED() function, it is possible to test quickly for any changes to these columns that contain sensitive employee information. This use of COLUMNS_UPDATED() only works if you are trying to detect changes to the first 8 columns in the table. USE pubs
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 employeeData (
emp_id int NOT NULL,
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 auditEmployeeData (
audit_log_id uniqueidentifier DEFAULT NEWID(),
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 updEmployeeData
ON employeeData
FOR update AS
/*Check whether columns 2, 3 or 4 has been updated. If any or all of 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 check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/ IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
BEGIN
— Audit OLD record.
INSERT INTO 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 del — Audit NEW record.
INSERT INTO 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 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 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 employeeData
SET emp_bankAccountNumber = ‘133146A0’, emp_SSN = ‘R-M53550M’
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO
Panic, Chaos, Disorder … my work here is done –unknown

---

Thanks for your help. Here is what I came up with: CREATE TRIGGER [DupData] ON [dbo].[Proj]
AFTER UPDATE
AS
IF (UPDATE(CustomerID))
BEGIN
IF EXISTS(select * from [Case],[Proj] where [Case].[CaseID] = [Proj].[ProjID])
BEGIN
update [Case] set [Case].[CustomerID] = [Proj].[CustomerID] from [Case],[Proj] where [Case].[CaseID] = [Proj].[ProjID]
END
END
My problem is that every caseid that matches a projid gets changed. I only want to change the caseid that matches the projid that caused the trigger to fire. How do I reference the row that caused the trigger? Thanks in advance, Ben

---

This trigger is fired everytime that an update happens on Proj and all the times, the IF statement runs because it is always true. Also, the UPDATE statement always updates ALL the columns which satisfy its condition. So, this trigger slowdowns your SQL-server. Even if the Proj table is updated too much, may be you need to implement your need in other way because it may slow-down your server. To solve the current problem:
1. Change the IF condition to allow UPDATE to be run only if the exact condition has happened. Your current condition is not correct. 2. Change the UPDATE statement (condition part) to point to the change condition.
CanadaDBA

---

There are 2 virtual tables that are called INSERTED and DELETED. when you insert a row to table A the table INSERTED will have the same structure of table A, and you will find there the new rows. Same goes for deleted. Update works like delete and insert, meaning, the old rows will be in the DELETED table and the new updated value in the INSERTED.
In your trigger you should add those tables to your join.
You can also add if @@rowcount > 0 at the begining of the procedure. This will tell you if at least 1 row was affected.

---

Where are these virtual tables? How can I SELECT them? CanadaDBA

---

Two special tables are used in trigger statements: the deleted table and the inserted table.
BOL specifies:
The inserted and deleted tables are used primarily in triggers to: Extend referential integrity between tables.
Insert or update data in base tables underlying a view.
Check for errors and take action based on the error.
Find the difference between the state of a table before and after a data modification and take action(s) based on that difference.
… for more information review BOL. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Thank you, this has solved my problem.

---

]]>