CASE Statement

[woogoo]

Hi All,

I want to create this Trigger:

CREATE TRIGGER [dbo].[TR_Info_Insertion] ON [dbo].[Information] FOR INSERT AS
BEGIN
	CASE Inserted.VarType 
		WHEN 'EB' THEN Inserted.EB = Inserted.Price
		WHEN 'ES' THEN Inserted.ES = Inserted.Price
	END
END ;

But when parsed I get an error:
Msg 156, Level 15, State 1, Procedure TR_Info_Insertion, Line 3
Incorrect syntax near the keyword 'CASE'.

Obviously not right, but from my understanding of the docs then it should be! Could someone tell me where I'm going wrong please.
--

woogoo

 

[RiverGuy]

CASE needs to be used within a query, not to control the flow of your T-SQL statments. Here's an example based on what I think you're looking to do. It looks like you're trying to conditionally change the values of the EB and ES columns.

CREATE TRIGGER [dbo].[TR_Info_Insertion] ON [dbo].[Information] INSTEAD OF INSERT AS
BEGIN
   INSERT INTO dbo.Information
   (Column1Name, Column2Name, etc, Price, VarType, EB, ES)
   SELECT Column1Name, Column2Name, etc, Price, VarType,
   EB =
   CASE
      WHEN VarType = 'EB' THEN Price
      ELSE EB
   END,
   EBS =
   CASE
      WHEN VarType = 'ES' THEN Price
      ELSE ES
   END
   FROM INSERTED
END ;

 

[woogoo]

Hey RiverGuy, thanks for the reply, it does of course explain everything!



woogoo