asked on

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.

akramryk

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

ASKER CERTIFIED SOLUTION

Dale Burrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial