March 28, 2008 at 8:21 am
I have a table with 14 columns
I'm writing an update trigger and i only want it to fire if only any of these columns are updated:
col3
col4
col9
I have more than 8 columns and i'm not sure how to write the substring syntax:
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1))))
Can someone help me out
THANK YOU
March 29, 2008 at 4:38 am
Alex:
I can see pieces of what you are trying to do; however, to me it is not clear how you are trying to use the substring function. I need more description of what you are trying to accomplish.
March 29, 2008 at 5:34 am
columns_updates just says is a column is updated, it does not contain the value.
from bol
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. 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.
maybe you're better off using
if UPDATE ( yourcolumnname )
begin
-- do something for that column
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 29, 2008 at 7:08 pm
I agree... Use IF UPDATE(somecolumn) instead of trying to setup the byte slicing of COLUMNS_UPDATED.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2008 at 3:41 pm
Alex, int value returned by POWER function holds 4 bytes, 32 bits.
It's more than enough for your 14 columns.
There is no need for any SUBSTRING.
Your problem is the way you wrote the script you're taking the case when ALL of 3 columns are updated, NOT ANY of them.
You should use OR instead of AND.
_____________
Code for TallyGenerator
March 30, 2008 at 3:47 pm
And if you put the list of your columns into specially designed table:
CREATE TABLE dbo.TriggerUpdatedColumns (
TriggerName sysname,
ColNo smallint
)
You can use simple script :
IF EXISTS (
SELECT 1 FROM dbo.TriggerUpdatedColumns
WHERE TriggerName = Object_Name(@@procid) -- returns current procedure name, in this case Trigger name
AND Columns_Update() & POWER(2, ColNo-1) > 0
)
Then you can change the rules without modifying procedures, just by updating the data in the table.
_____________
Code for TallyGenerator
March 31, 2008 at 11:52 am
Thanks for input everyone
I used:
if update (columnname) or update(columnname) or update (columnname)
begin
--sql statements
end
and it worked
March 31, 2008 at 6:51 pm
AlexSQLForums (3/31/2008)
Thanks for input everyoneI used:
if update (columnname) or update(columnname) or update (columnname)begin
--sql statements
end
and it worked
Thanks for posting your solution, Alex!
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2008 at 8:28 pm
We can use BIT MASK concept to implement this task.
For example:
Column123456
Bit Mask12481632
To test whether or not columns 4 or 6 are updated, we can use (40 = 8 + 32):
IF (COLUMNS_UPDATED() & 40) > 0
...
March 31, 2008 at 10:21 pm
SQL ORACLE (3/31/2008)
We can use BIT MASK concept to implement this task.
Sure could... now, pretend it's 5PM on a Friday and you have a really hot gig at 5:30PM (what ever it is)... As you start to leave, the BIG BOSS with the pointy hair waddles his way into your cube and says that you're not going home until a problem with that trigger is solved. How much time do you wanna spend with that sweaty bugger with the massive deoderant failure trying to figure out which columns it's updating? And don't say it's a piece of cake because you don't really know if the order of the columns in the table has been changed or not, now do ya? 😉 And, I'm sure the trigger has the absolute best documentation in the world in it... 😛
Using Columns_Updated is worse than using SORT BY 1,2,3 :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 12:36 pm
Indeed, changes to the underlying table's column order will make a bitmapped trigger buggy, and there is no way you can avoid it becomming buggy ...
Best is to seal it with a KISS (keep it simple and stupid) :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 1, 2008 at 2:34 pm
Jeff Moden (3/31/2008)
SQL ORACLE (3/31/2008)
We can use BIT MASK concept to implement this task.Sure could... now, pretend it's 5PM on a Friday and you have a really hot gig at 5:30PM (what ever it is)... As you start to leave, the BIG BOSS with the pointy hair waddles his way into your cube and says that you're not going home until a problem with that trigger is solved.
Take my approach, write a simple interface and let that stinky bas..d choose the columns himself. 😎
_____________
Code for TallyGenerator
April 2, 2008 at 9:01 am
My understanding is that using something like
if UPDATE ( yourcolumnname )
begin
-- do something for that column
end
will execute the "do something" if the existing value is over-written with the same value - it does not tell you if the "new" value is different. Many .NET apps will write values to several fields even if the values have not changed. Am I wrong about that?
Since you are working inside a trigger, wouldn't it be better to compare the values between the inserted and deleted tables?
April 2, 2008 at 9:36 am
Don't even bother with updated_columns or updated()
I've seen both of those say a column was updated even when it isn't.
Join inserted to deleted and compare the values
i.e.
select
count(*)
from
inserted a join deleted b on
a.key = b.key
where
a.field1 != b.field1 or
a.field2 != b.field2 or
..
a.field8 != b.field8
Then you do this
declare @resultset integer
declare csr_important_col_changed as
select
count(*)
from
inserted a join deleted b on
a.key = b.key
where
a.field1 != b.field1 or
a.field2 != b.field2 or
..
a.field8 != b.field8
open csr_important_col_changed
fetch next from csr_important_col_changed into @resultset
close csr_important_col_changed
deallocate csr_important_col_changed
if @resultset = 0
BEGIN
RETURN
END
April 2, 2008 at 11:14 am
Expensive move to compare all columns if you only care if a couple have been updated...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply