SQL Case Statement in Trigger

Hello,

  I am attempting to create a trigger on insert into a table on one of my databases.  The database is MSSQL 2k5.

   Essentially, after the insert, i want to get the value of a particular field, then based on it's value, which could be up to 5 different things, I want to execute multiple other queries.

It looks something like this.

CREATE TRIGGER trig_UpdateValues
 ON table
 AFTER INSERT
 AS
 DECLARE @var int
 SELECT @var = (SELECT varID FROM Inserted)
 CASE @var
 WHEN 1 THEN
  --  3 update queries
WHEN 2 THEN
  --  more sql
WHEN 3 THEN
  -- other sql
WHEN 4 THEN
  -- even more sql
ELSE
  -- do something else
END

I am really stuck on this here.  I have been able to accomplish this using nested IF / ELSE blocks, but was hoping for a "switch" type of mechanism to reduce the code.

theblanc0Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

akramrykProject ManagerCommented:
Try this may be just work around :


CREATE TRIGGER trig_UpdateValues
 ON table
 AFTER INSERT
 AS
 DECLARE @var int
 SELECT @var = (SELECT varID FROM Inserted)
 CASE @var
 WHEN @var=1 THEN
  --  3 update queries
WHEN @var=2 THEN
  --  more sql
WHEN @var=3 THEN
  -- other sql
WHEN @var=4 THEN
  -- even more sql
ELSE
  -- do something else
END

Also check if you are getting desired value in @var
Dale BurrellDirectorCommented:
IF/ELSE is the only option. There is no case/switch which allows statements inside.

DECLARE @var int
SELECT @var = (SELECT varID FROM Inserted)

IF @var = 1 THEN
  --  3 update queries
ELSE IF @var = 2 THEN
  --  more sql
ELSE IF @var = 3 THEN
  -- other sql
ELSE IF @var = 4 THEN
  -- even more sql
ELSE
  -- do something else
END

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.