First time trigger writer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

First time trigger writer

Author

Topic

mattpont
Starting Member

4 Posts

Posted - 2006-12-21 : 08:54:39

I've never written a trigger before, and I'm trying to write some code for the following problem. 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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 08:57:29

create trigger ....

update t1
set t1.test_updated = sign(t1.test_a + t1.test_b + t1.test_c)
from tbl_test as t1
inner join inserted as i on i.test_id = t1.test_id

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-21 : 08:57:40

Why trigger? why not in the original Insert/Update query?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mattpont
Starting Member

4 Posts

Posted - 2006-12-21 : 09:18:13

Wicked.

Thanks Peso, 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 09:52:04

update t1
set t1.test_updated = sign(0 + t1.test_a + t1.test_b + t1.test_c)
from tbl_test as t1
inner join inserted as i on i.test_id = t1.test_id

Peter Larsson
Helsingborg, Sweden

mattpont
Starting Member

4 Posts

Posted - 2006-12-21 : 09:56:31

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!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-21 : 09:58:09

What if any of sum of all values is NULL or negative?

update t1
set t1.test_updated = sign(Abs(0 + IsNull(t1.test_a,0) + IsNull(t1.test_b,0) + IsNull(t1.test_c,0)))
from tbl_test as t1
inner join inserted as i on i.test_id = t1.test_id

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 10:01:57

BIT can be negative!?

Using the code above, SIGN is not needed, because any value <> 0 will become true (1).

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 10:03:41

Also, how do we know that NULLs are to be treated as FALSE (0), and not TRUE (1) ???

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 10:04:26

If NULLs (if any at all) are treated as FALSE (0), use this

update t1
set t1.test_updated = 1 + sign( Nullif(t1.test_a, 0) + Nullif(t1.test_b, 0) + Nullif(t1.test_c, 0) )
from tbl_test as t1
inner join inserted as i on i.test_id = t1.test_id

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-21 : 10:09:57

quote:
If NULLs (if any at all) are treated as FALSE (0), use this
update t1
set t1.test_updated = 1 + sign( Nullif(t1.test_a, 0) + Nullif(t1.test_b, 0) + Nullif(t1.test_c, 0) )
from tbl_test as t1
inner join inserted as i on i.test_id = t1.test_id

But that would set test_updated to NULL if all three values are zero, Am I right? or missing something?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 10:14:09

You're correct! Change NULLIF to ISNULL...

(I am pretty exited about this newly learned NULLIF function

)

Peter Larsson
Helsingborg, Sweden

mattpont
Starting Member

4 Posts

Posted - 2006-12-21 : 10:38:19

Cool, thanks everyone.

It's working now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources