1

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

?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • The question is tagged `tsql`. `CREATE OR REPLACE` is not seen in Microsoft T-SQL, but is seen in Oracle. Ditto `:new` and `for each row` and etc. Which database management system are you trying to write this for? – Shannon Severance Apr 10 '17 at 20:35
  • This needs to be TSQL -the database in SQL Server; I got that code in response to a google request "how can i create a beforeinsert trigger in tsql?" but see now that the page, although starting with TSQL, then added the Oracle stuff you see above. – B. Clay Shannon-B. Crow Raven Apr 10 '17 at 20:43
  • I was able to get it working in VB.NET without resorting to a BeforeInsert after all; see http://stackoverflow.com/questions/43329262/how-can-i-add-an-inline-vb-net-method – B. Clay Shannon-B. Crow Raven Apr 10 '17 at 21:39

1 Answers1

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66