| Author |
Topic |
|
Mondeo
Constraint Violating Yak Guru
287 Posts |
 Posted - 2007-11-01 : 11:42:11
|
| I've got 10 bit fields in my table. Whenever the state of number 1 changes i.e. true, false or NULL I want the other 9 to change to that.Can I do this with a trigger, if so can someone help me with the syntax?Thanks |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2007-11-01 : 11:46:00
|
| You should be able to do this with an Update Trigger, but I have to ask if all 10 of the columns should be the same value in your table, why not reference a single column in your front-end application?Jeremy W. Oldham |
 |
|
|
Mondeo
Constraint Violating Yak Guru
287 Posts |
Posted - 2007-11-01 : 11:51:30
|
| Your right, its becuase in the future we'll need the functionality to modify the 10 columns seperately. But at the moment they all need to be the same, obviously i'll just delete the trigger when the time comes.Thanks |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2007-11-01 : 11:57:26
|
| Try looking up COLUMNS_UPDATED in BOL:CREATE TRIGGER updEmployeeData ON 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. 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 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;Jeremy W. Oldham |
|
|
|
Mondeo
Constraint Violating Yak Guru
287 Posts |
Posted - 2007-11-01 : 12:05:29
|
| Hi,Thanks for that, IF (COLUMNS_UPDATED() & 14) > 0/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see whether all columns 2, 3, and 4 are updated.*/What does the & 14 mean - how does 14 relate to columns 2,3 and 4?Thanks |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2007-11-01 : 12:10:27
|
| Not sure. This is posted from Books Online and I didn't dive into the details.Jeremy W. Oldham |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 13:02:16
|
My perceived problem with COLUMNS_UPDATED() is that it only tells you that at least one row in "inserted" as changed with respect to that column. You still need to put a test in a WHERE clause of the update ... in which case there doesn't seem to be much point using COLUMNS_UPDATED() at all to me  Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 13:06:57
|
| Or bit-map expressed as an INT ... although that isn't really "normalised" is it ... |
|
|
|
|
Trigger question