Wednesday, March 28, 2012

If Else Statement in a Update Trigger

I am trying to build an update trigger to check the condition if checkbox is true then add 1 year, else add 3 years. The code works before i added the if checkbox = true.

Original Working code:

IF NOT UPDATE (EDITED)

UPDATE drvisit

SET nextvisit = dateadd (yy, 1, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Code I am trying to use with an IF Statement:

IF NOT UPDATE (EDITED)

UPDATE drvisit

IF Checkbox = True

SET nextvisit = dateadd (yy, 1, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Else

Set nextvisit = dateadd (yy, 3, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Use a case statement instead of "IF"
This example assumes "checkbox" is a bit field...

IF NOT UPDATE (EDITED)

UPDATE drvisit
SET nextvisit = case when checkbox = 1 then dateadd (yy, 1, lastaudio) else dateadd (yy, 3, lastaudio) end
FROM Apt
WHERE rec# IN (SELECT rec# FROM inserted)

|||Sweet thanks.. If i did want to do and if statement how would I? On a few occasions I tried it and could never get it to work.|||

Glad to be of help.

"IF" is really only for controlling the flow of execution of your SQL script. It can't be used within a single SQL statement (like UPDATE) to conditionally apply a value.

No comments:

Post a Comment