theblanc0
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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