如何在SQL Server中使用CASE编写状态转换触发器？

Got it, let's fix this up properly—your sample code has some syntax gaps, so here's a working, clean trigger that does exactly what you need: automatically standardizing the Status column whenever a value starting with 'd' or 'p' is inserted or updated.

Working Trigger Code (SQL Server) #

CREATE TRIGGER Update_status
ON orders
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON; -- Avoids extra row-count messages that can break app integrations

    UPDATE o
    SET o.Status = 
        CASE
            WHEN i.Status LIKE 'd%' THEN 'done'
            WHEN i.Status LIKE 'p%' THEN 'processing'
            ELSE o.Status -- Leave non-matching values unchanged
        END
    FROM orders o
    INNER JOIN inserted i ON o.OrderID = i.OrderID -- Target only modified rows
    WHERE i.Status LIKE 'd%' OR i.Status LIKE 'p%'; -- Skip rows that don't need updates
END

Key Details to Know: #

• SET NOCOUNT ON;: This is a standard best practice—it prevents SQL Server from sending unnecessary "rows affected" messages back to your application, which can cause issues with some client tools.
• inserted Table: This is a system-generated table that holds all the new values being inserted or updated. We join it with orders using your table's primary key (like OrderID) to make sure we only modify the rows that were just changed.
• CASE Logic: It checks the incoming status value and standardizes it:
  • Any value starting with 'd' (case-insensitive by default) becomes 'done'
  • Any value starting with 'p' becomes 'processing'
  • All other values stay as they are—so you don't accidentally overwrite valid statuses that don't match these patterns
• WHERE Clause: Filters to only update rows that actually match our 'd%' or 'p%' patterns, which avoids unnecessary database operations.

Quick Bonus Tip: #

If you need case-sensitive matching (e.g., only 'D' counts but not 'd'), add a collation to the LIKE checks, like LIKE 'd%' COLLATE SQL_Latin1_General_CP1_CS_AS.

内容的提问来源于stack exchange，提问作者NKK