Can this be done using a trigger?

[mattpont]

I have a table called tbl_test:

test_id, int, (Primary Key)
test_A, int
test_B, int
test_C, int
test_updated, int

Basically, I want the value of test_updated to be 0 if test_A, test_B and test_C are all 0. And the value of test_updated to be 1 if any of test_A, test_B or test_C are set to 1. I want the trigger to run on when a row is inserted, or if the row is updated.

What code do I need in order to write this trigger? I'm really struggling here.

Thanks.

 

[Catadmin]

Yes, you can do it with a trigger. I'd recommend an AFTER trigger rather than an INSTEAD OF Trigger.

There are so many little "gotchas" that I can't cover them here. You'll need to check CREATE TRIGGER on Books Online for those details, but the basics are:

CREATE TRIGGER MyTrig
ON MyTable
FOR INSERT
AFTER
 AS
...

BTW, AFTER is the default for Triggers and does not need to be specified. At least in SQL 2000.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mattpont]

Yeah, thanks.

I'd got that far, but I just don't really have a clue about the code afterwards!

Can anyone help me with this part?

Thanks

 

[Catadmin]

It's a simple Update statement. Two actually. One update for the zeros and one update for the ones. Of course, if you do it as an AFTER trigger, you'll update all rows in the table all over again.

Actually, you could do this as an INSTEAD OF trigger, then do an Insert into your table using FROM #Inserted (which is the temp table used in all Updates & Inserts) and a CASE statement to quantify WHEN A & B & C = 0 Then 0, etc. Mind you, the above is psuedo-code and not the proper way to write a WHEN clause in a CASE statement.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

one update statement is more than enough, you can use the sign function

update y 
set y.test_updated =sign(test_A+test_B+test_C)
from tbl_test y 
join inserted i on y.test_id = i.test_id

here is how the sign function works
0=0
less than 0 = -1
greater than 0 = 1

examples

select sign(1+0+0)
select sign(0+0+0)
select sign(1+1+0)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLDenis]

you might need to qualify test_A+test_B+test_C like this

update y 
set y.test_updated =sign(y.test_A+y.test_B+y.test_C)
from tbl_test y 
join inserted i on y.test_id = i.test_id

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mattpont]

Wicked.

Thanks, that worked fine.

Although I've made a bit of a cock up. The field type's of test_a, test_b, test_c and test_updated should be BIT instead of INT! Your code stops working when I make these changes to my table. Can it be tweaked so it will work with BITs rather than INTs?

Thanks

 

[SQLDenis]

sign(convert(int,y.test_A)+convert(int,y.test_B)+convert(int,y.test_C))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

So, if A, B, OR C is 1, you want updated = 1. The pipe ( | ) character of T-SQL's or operator. By the way... Ampersand ( & ) is the AND operator.

update y 
set y.test_updated = [!](y.test_A | y.test_B | y.test_C)[/!]
from tbl_test y 
join inserted i on y.test_id = i.test_id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mattpont]

Ok,

the goalposts have been moved again by my client.

I now need test_updated to be the opposite of the other columns. i.e. when test_A, test_B and test_C ALL equal to 0, test_updated must be 1. And if any of them equal 1, then test_updated = 0.

I hope you understand me!

 

[Catadmin]

I think in that case, it's time for a CASE statement.

update y 
set y.test_updated = (Select CASE 
     WHEN (y.test_A & y.test_B & y.test_C) = 1 THEN 0
     WHEN (y.test_A & y.test_B & y.test_C) = 0 THEN 1
     END)
from tbl_test y 
join inserted i on y.test_id = i.test_id

Mind you, I did not test this, so I don't know if George's ampersand thing will work here or not.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Yup... But I would write it like this...

update y 
set y.test_updated = CASE 
     WHEN (y.test_A & y.test_B & y.test_C) = 1 THEN 0
     Else 1
     END
from tbl_test y 
join inserted i on y.test_id = i.test_id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

update y 
set y.test_updated =case sign(test_A+test_B+test_C) when 1 then 0 else 1 end
from tbl_test y 
join inserted i on y.test_id = i.test_id

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Catadmin]

George,

Except the ELSE doesn't take into consideration that Test_A = 0, Test_B = 1, Test_C = (either 1 or 0), which is why I worded it that way.

If I'm correctly reading what mattpont wants, then he only wants the D to equal 1 or 0 when all other three columns match each other.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

damn George,

you only posted 3 yocto-seconds before me




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLDenis]

To me

this

when test_A, test_B and test_C ALL equal to 0, test_updated must be 1. And if any of them equal 1, then test_updated = 0


sounds like

if any columns is not 0 then test_updated = 0
if all of them are 0 then test_updated =1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mattpont]

Cool, thanks everyone.

It's working now.