Trigger to update a column in same table

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

General SQL Server Forums

New to SQL Server Programming

Trigger to update a column in same table

Author

Topic

kwuh97
Starting Member

9 Posts

Posted - 2010-01-24 : 10:33:12

Very new to triggers I need help to create a trigger that, when a specific Column1 (Answer) in table A is updated another Column2 (ModifiedAnswer) in Table A gets updated with a certain value.

Tickets Table(Question varchar(max), Answer varchar(max), ModifiedAnswer bit....)

When the question is inserted, the Answer Column is null and the ModifiedAnswer is 0.

When the Answer first entered the ModifiedAnswer column is still 0.

However when the Answer is updated the ModifiedAnswer column is set to 1 permanantly.

Here is what I came up with: I had set a constraint on the AnswerModified with a default value = 0, so I amm thinking that would suffice for an update of the ModifiedAnswer. This is what I came up with for the update trigger:

--IF UPDATE(AnswerModified)
BEGIN
UPDATE T
SET T.AnswerModified = 1,
CUT = 0
FROM atbl_Bids_BEQregister (NOLOCK) T
INNER JOIN Inserted I ON I.PrimKey = T.PrimKey
WHERE I.Answer <> T.Answer
END

Like I said, I am new at this so I am not sure if this is correct. Thanks In Advance For Any assistance.
kw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 10:36:29

it should be IF UPDATE(Answer) rather than what you've now. rest all looks fine

kwuh97
Starting Member

9 Posts

Posted - 2010-01-24 : 10:48:52

quote:
Originally posted by visakh16

it should be IF UPDATE(Answer) rather than what you've now. rest all looks fine

Great! :) So you are saying the only thing that DOES NOT looks good are my comments(documentation).

Also I have seen examples where they use "!=" instead of "<>". Are both correct?

Also I wanted to correct a typo on my original post:
"Here is what I came up with: I had set a constraint on the AnswerModified with a default value = 0, so I am thinking that would suffice for the first update of the "Answer" column."

kw

kwuh97
Starting Member

9 Posts

Posted - 2010-01-24 : 11:06:56

quote:
Originally posted by visakh16

it should be IF UPDATE(Answer) rather than what you've now. rest all looks fine

:( Naw... its not working as expected, sigh.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-24 : 11:37:01

It's not working is very helpful for us to help you, you know?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 12:15:37

quote:
Originally posted by kwuh97

quote:
Originally posted by visakh16

it should be IF UPDATE(Answer) rather than what you've now. rest all looks fine

:( Naw... its not working as expected, sigh.

can we see what you really expected to get?

kwuh97
Starting Member

9 Posts

Posted - 2010-01-24 : 12:49:22

quote:
Originally posted by visakh16

quote:
Originally posted by kwuh97

quote:
Originally posted by visakh16

it should be IF UPDATE(Answer) rather than what you've now. rest all looks fine

Ok here's what I expect to see when the original answer is entered into Table1

SELECT Answer, AnswerModified
FROM Table1
WHERE ID = 'ABC'

Answer AnswerModified
--------------------------- --------------
This is the original answer 0

Today when I run this update statement:
UPDATE Table1
SET Answer = 'This is the modified answer'
WHERE ID = 'ABC'

Then run the same select statement as above and I get:
Answer AnswerModified
---------------------------- --------------
This is the modified answer 0

My expectation is to get this instead:
Answer AnswerModified
---------------------------- --------------
This is the modified answer 1

Thanks for any help.
kw

:( Naw... its not working as expected, sigh.

can we see what you really expected to get?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 12:52:48

can you post your current trigger code?

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 13:18:14

"FROM atbl_Bids_BEQregister (NOLOCK) T"

I have very rarely see NOLOCK used where it wasn't a sign of a problem in the code or design of the system. Avoid using this unless you have a real, unavoidable, problem.


UPDATE T
SET T.AnswerModified = 1,
    CUT = 0
FROM atbl_Bids_BEQregister (NOLOCK) T
     INNER JOIN Inserted I 
         ON I.PrimKey = T.PrimKey
WHERE I.Answer <> T.Answer

Actually, this is probably the problem. You are joining in the PK and then on a column in INSERTED not matching the [Answer]. If this is an AFTER trigger this cannot be, the Update will already have been applied, so the columns in INSERTED will be the same as in the main table. This WHERE clause will mean that zero rows are updated

kwuh97
Starting Member

9 Posts

Posted - 2010-01-24 : 13:20:01

quote:
Originally posted by visakh16

can you post your current trigger code?

uhm... is this what you are referring to:

BEGIN
UPDATE T
SET T.AnswerModified = 1,
CUT = 0
FROM Table1 (NOLOCK) T
INNER JOIN Inserted I ON I.ID = T.ID
WHERE I.Answer <> T.Answer
END

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 13:21:04

See my answer just before you posted

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 13:23:05

"I have seen examples where they use "!=" instead of "<>". Are both correct?"

You can use either; preference probably depends on what Application language you are used to. Basic uses "<>", C uses "!=".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 13:23:13

actually where should be I.Answer <> D.Answer and you've to join to deleted d also

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 13:24:55

Ah, that makes sense. "Where Updated"

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 13:27:12

I would add (to the WHERE)


AND (COALESCE(I.AnswerModified, 0) <> 1 OR (COALESCE(I.CUT, 1) <> 0)

No sense updating if the row already has those values. (If it cannot be that the row already has those values then leave that code out; but I've seen large UPDATES where the trigger re-updates them, changing nothing, but bloating the log and taking more CPU !!)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-24 : 13:29:09

I think it would be enough to join deleted INSTEAD of inserted if this is an update-trigger...

No, you're never too old to Yak'n'Roll if you're too young to die.

kwuh97
Starting Member

9 Posts

Posted - 2010-01-24 : 13:54:24

quote:
Originally posted by Kristen

I would add (to the WHERE)

AND (COALESCE(I.AnswerModified, 0) <> 1 OR (COALESCE(I.CUT, 1) <> 0)

No sense updating if the row already has those values. (If it cannot be that the row already has those values then leave that code out; but I've seen large UPDATES where the trigger re-updates them, changing nothing, but bloating the log and taking more CPU !!)

Ok guys for the novice in this discussion. Can you provide some systax from the what I have provided that will allow me to fully understand what you guys are talking about.

Thanks In Advance for any help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 13:57:50

its about how you need to change your where condition in original query

kwuh97
Starting Member

9 Posts

Posted - 2010-01-24 : 14:24:29

quote:
Originally posted by visakh16

its about how you need to change your where condition in original query

Ok let me see if I follow what you have discussed, here is my attempt

BEGIN
UPDATE T
SET T.AnswerModified = 1,
CUT = 0
FROM Table1 (NOLOCK) T
INNER JOIN Inserted I ON I.ID = T.ID
INNER JOIN Deleted D ON I.ID = D.ID
WHERE I.Answer <> D.Answer
AND (COALESCE(I.AnswerModified, 0) <> 1 OR (COALESCE(I.CUT, 1) <> 0)

END

This is the code and it still does not update AnswerModified. What could be the reason?
kw

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 15:15:39

Is it possible that the original Answer, or the new Answer, is null?

I.Answer <> D.Answer will NOT be true if either is Null

I still don't think you should be using NOLOCK. Looks like you are just using it "as a matter of course"

What does you UPDATE statement look like? Something like this?


UPDATE U
SET Answer = 'New answer'
FROM Table1 AS U
WHERE Answer = 'Old answer'

kwuh97
Starting Member

9 Posts

Posted - 2010-01-24 : 15:41:40

quote:
Originally posted by Kristen

Is it possible that the original Answer, or the new Answer, is null?

I.Answer <> D.Answer will NOT be true if either is Null

I still don't think you should be using NOLOCK. Looks like you are just using it "as a matter of course"

What does you UPDATE statement look like? Something like this?

UPDATE U
SET Answer = 'New answer'
FROM Table1 AS U
WHERE Answer = 'Old answer'

Well, just a simple update statement

UPDATE Table1
SET Answer = 'This is the modified answer'
WHERE ID = 'ABC'

Nothing special.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources