SQL同表Insert/Update触发器递归避免及整数转换功能验证咨询

Hey there! Since you didn’t share the actual trigger code you put together, I’ll walk you through the right approach to hit your requirements, and you can cross-check it against your own code to see if it’s on the mark.

• Convert the Myvalue column to an integer (truncate decimal parts, no rounding) on INSERT/UPDATE
• Prevent recursive execution of the same-table trigger
• No changes to database-level parameters or table structure allowed

Here’s a trigger that meets all your needs—this is the standard pattern for this scenario:

CREATE TRIGGER trg_YourTableName_InsertUpdate
ON YourTableName
AFTER INSERT, UPDATE
AS
BEGIN
    -- Block recursion: exit if this is a nested trigger call
    IF TRIGGER_NESTLEVEL() > 1
        RETURN;

    SET NOCOUNT ON; -- Suppress row count messages to avoid client-side confusion

    -- Update only rows where Myvalue needs truncation
    UPDATE t
    SET t.Myvalue = CAST(i.Myvalue AS INT)
    FROM YourTableName t
    INNER JOIN inserted i ON t.YourPrimaryKey = i.YourPrimaryKey
    WHERE i.Myvalue <> CAST(i.Myvalue AS INT);
END;

1. Preventing Recursion #

The TRIGGER_NESTLEVEL() function checks how deep the trigger call stack is. When the trigger runs an UPDATE on the same table, it would normally trigger itself again—this check exits early when the nest level is greater than 1, stopping recursion without needing to modify database-wide settings like RECURSIVE_TRIGGERS. This fits your "no database parameter changes" rule perfectly.

2. Truncating Decimals to Integers #

• CAST(Myvalue AS INT) truncates decimal parts for both positive and negative numbers (e.g., 1.9 becomes 1, -1.9 becomes -1). If you specifically need to round down (e.g., -1.9 → -2), use FLOOR(Myvalue) instead.
• The WHERE clause ensures we only update rows that actually need conversion—this avoids unnecessary DML operations and helps with performance.

3. Database-Specific Adjustments #

If you’re using a different database (like MySQL), the syntax changes slightly, but the logic stays the same:

DELIMITER //
-- Trigger for INSERTs
CREATE TRIGGER trg_YourTableName_BeforeInsert
BEFORE INSERT ON YourTableName
FOR EACH ROW
BEGIN
    IF @@TRIGGER_NESTLEVEL > 1 THEN
        RETURN;
    END IF;
    SET NEW.Myvalue = CAST(NEW.Myvalue AS SIGNED); -- Truncate to integer
END //

-- Trigger for UPDATEs
CREATE TRIGGER trg_YourTableName_BeforeUpdate
BEFORE UPDATE ON YourTableName
FOR EACH ROW
BEGIN
    IF @@TRIGGER_NESTLEVEL > 1 THEN
        RETURN;
    END IF;
    SET NEW.Myvalue = CAST(NEW.Myvalue AS SIGNED);
END //
DELIMITER ;

If your existing code includes:

• A check for trigger nesting level (or another method to block recursion without changing DB settings)
• Correct truncation of Myvalue to an integer
• No alterations to table structure or database parameters

Then it should work exactly as you need it to!

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