What code is needed to conditionally provide a value to a field in a beforeinsert trigger?

Question

I need to store a string value in a field in a table, specifically in its Subcategory VarChar(50) column.

The value of Subcategory prior to this post processing is either 0 or 1; I need to change that to a more human-friendly value.

I haven't created a database trigger in decades and need some help with the code. This is my pseudo SQL (a hodgepodge of SQL and VB):

CREATE OR REPLACE TRIGGER tr_CustomerCategoryLog_BeforeInsert
BEFORE INSERT ON CustomerCategoryLog FOR EACH ROW

DECLARE _Category = :new.Category;
DECLARE _Subcategory = :new.Subcategory;

BEGIN
    If _Category = "New"
       If _Subcategory = 0
          :new.Subcategory := 'New';
       End If
       Else If _Subcategory = 1
           :new.Subcategory := 'Assumed';
        End If    
    End If

    If _Category = "Existing"
            If _Subcategory = 0
              :new.Subcategory := 'Existing';
            End If
            Else If _Subcategory = 1
                :new.Subcategory := 'Organic'
            End If    
        End If
        Return "Unknown"
        End Function        
  END;

If the logic isn't clear, in semi-plain English it is:

If the value of the Category field is "New", set the Subcategory field value also to "New" if the value of Subcategory is currently 0; otherwise, set it to "Assumed"

If the value of the Category field is "Existing", set the Subcategory field value also to "Existing" if the value of Subcategory is currently 0; otherwise, set it to "Organic"

Maybe I need to give Steely Dan's album "Trigger Logic" a listen.

UPDATE

I think the answer will work, but it's not complete enough for me.

Since I apparently have Oracle code mixed up in the pseudoSQL above, what would the complete code need to look like (to create a BeforeInsert trigger on the CustomerCategoryLog table)?

Is this more like it:

CREATE TRIGGER tr_CustomerCategoryLog_BeforeInsert
ON CustomerCategoryLog
INSTEAD OF INSERT
AS
BEGIN
    SELECT
        CASE
           WHEN @Category = 'New'      
              THEN CHOOSE(@Subcategory + 1, 'New', 'Assumed')
           WHEN @Category = 'Existing' 
              THEN CHOOSE(@Subcategory + 1, 'Existing', 'Organic')
           ELSE 'Unknown' 
        END
END

?

Answer 1

I tend to avoid triggers (perhaps a character flaw... I also don't like mashed potatoes), but the following illustration could simplify your logic

Declare @Category varchar(50) = 'Existing'
Declare @Subcategory int      = 1            -- works if BIT

Select case when @Category = 'New'      then choose(@Subcategory+1,'New','Assumed')
            when @Category = 'Existing' then choose(@Subcategory+1,'Existing','Organic')
            else 'Unknown' end

Returns

Organic