August 9, 2008 at 3:06 pm
Howdy experts,
I have a requirement here for a trigger. What I need to do is to watch the 9th to 18th columns if any of them is updated with a particular value (say, "keyword").
I am trying to do something like this:
CREATE TRIGGER mytrigger ON mytable
AFTER UPDATE AS
-- Check for 9th - 18th if ANY of the columns has been updated (bitmask calculation)
IF ( (SUBSTRING(COLUMNS_UPDATED(),2,1) & 255 > 0)
OR (SUBSTRING(COLUMNS_UPDATED(),3,1) & 3 > 0) )
-- CHECK FOR THE UPDATED VALUE
-- IF UPDATED VALUE = "KEYWORD"
-- THEN:
UPDATE mytable
SET mycol = 0
-- WHERE colID = colID of the UPDATED row
How can I accomplish this?
SQL Server Database Administrator
August 9, 2008 at 5:01 pm
Regarding "mycol"
What is the datatype ?
What are all the possible values ?
SQL = Scarcely Qualifies as a Language
August 9, 2008 at 5:06 pm
Carl Federl (8/9/2008)
Regarding "mycol"What is the datatype ?
What are all the possible values ?
mycol's data type is INT
My Update would look something like this:
UPDATE dbo.mytable
SET mycol = 0
WHERE mycolID IN (Select mycoldID from Inserted)
But Before I can do that Update, the following conditions should be met:
1. Update that triggers my TRIGGER is to be made in any column from column 9th to column 18th
2. That the updated value should be "mykeyword" (literal)
I have created triggers before but none so far is quite similar to what I am doing now.
SQL Server Database Administrator
August 9, 2008 at 10:35 pm
Try this:
CREATE TRIGGER mytrigger ON mytable
AFTER UPDATE AS
Declare @UpdateBits int
Declare @Mask9to18 int--set mask for bits 9 to 18
Set @Mask9to18 = 512|1024|2048|4096|8192|16384|32768|65536|131072|262144
Set @UpdateBits = Cast(COLUMNS_UPDATED() as int) & @Mask9to18
IF @UpdateBits > 0
BEGIN
UPDATE mytable
SET mycol = ( Select MAX(CASE "KEYWORD"
When Col8 then 8
When Col9 then 9
When Col10 then 10
When Col11 then 11
When Col12 then 12
When Col13 then 13
When Col14 then 14
When Col15 then 15
When Col16 then 16
When Col17 then 17
When Col18 then 18
Else 0 End)
From inserted )
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 10, 2008 at 6:20 am
I do not think that you need any triggers as a computed column is a viable alternative solution.
create table dbo.MyTable
(id int not null
,col8 varchar(8) not null
,col9 varchar(8) not null
,col10 varchar(8) not null
,KeyWordInd as
(case when 'keyword' in (col8,col9,col10) then cast(1 as integer) else 0 end )
)
SQL = Scarcely Qualifies as a Language
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply