| Author |
Topic |
|
sinva
Starting Member
23 Posts |
 Posted - 2010-05-19 : 18:39:53
|
| Dear all,As the trigger is checking on the table, when I have update on multiple rows of a table A, the statement select columnA from deleted will return a single row or multiple rows??Thanks in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sinva
Starting Member
23 Posts |
Posted - 2010-05-19 : 18:54:18
|
| Thank you for your reply. That means I could not use "select columnA from deleted <> select columnA from inserted" to check if the value in columnA has been changed? Am I right? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-19 : 18:58:21
|
From BOL:quote:
Testing for UPDATE or INSERT Actions to Specific ColumnsYou can design a Transact-SQL trigger to perform certain actions based on UPDATE or INSERT modifications to specific columns. Use UPDATE() or COLUMNS_UPDATED in the body of the trigger for this purpose. UPDATE() tests for UPDATE or INSERT tries on one column. COLUMNS_UPDATED tests for UPDATE or INSERT actions that are performed on multiple columns and returns a bit pattern that indicates which columns were inserted or updated.
Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-19 : 20:59:34
|
quote:
Originally posted by sinva
Thank you for your reply. That means I could not use "select columnA from deleted <> select columnA from inserted" to check if the value in columnA has been changed? Am I right?
Yes. You can't do that. INSERTED or DELETED may contain more than 1 record.it should beselect *from inserted i inner join deleted d on i.pkcol = d.pkcolwhere i.columnA <> d.columnAor (i.columnA is null and d.columnA is not null)or (i.columnA is not null and d.columnA is null)
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
sinva
Starting Member
23 Posts |
Posted - 2010-05-19 : 21:51:26
|
| KH, Thank you for your adviceThen do you think if there are some more checking I should first join the inserted and deleted and then put it to a temp table and loop the records in the temp table and have the checking??Thanks |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-19 : 22:23:25
|
i don't quite get the temp table & looping part. If you are doing some sort of audit trail you can insert to the audit trail table with the result of joining the inserted with deleted table.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
sinva
Starting Member
23 Posts |
Posted - 2010-05-19 : 23:51:22
|
| Sorry KH,As I have some more checking for the deleted and inserted, for example I may need to join other tables for comparison. Then should I create temp table and use cursor to loop the temp table and compare??Thank you for your reply |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-20 : 01:30:57
|
you don't have to do that.just join it to the required tablefrom inserted i inner join deleted d on i.pkcol = d.pkcol inner join othertable o on i.somecol = o.somecol
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
sinva
Starting Member
23 Posts |
Posted - 2010-05-20 : 04:35:10
|
| KH,Thank you. But if I have to perform many checkings some join table X some join table Y then should I use the cursor and a temp table??Thank you |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-20 : 04:55:10
|
no. if you can have the checking within a query why do you want to use temp table + cursor ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
Trigger Question