无需触发器或修改存储过程实现SQL记录更新标记自动重置问询

Hey there, let’s tackle this problem step by step. You need a flag field that gets set by another application after processing, and automatically resets when the record is updated manually or via existing stored procedures—without using triggers or altering those existing SPs. Here are a few practical approaches tailored to different scenarios:

1. Wrapper Stored Procedures + Permission Controls #

This is the most straightforward way to enforce the reset logic while keeping your existing SPs untouched.

Step 1: Add the Flag Field #

First, create your flag column with the desired initial state:

ALTER TABLE YourTargetTable
ADD IsProcessed BIT DEFAULT 0; -- Use NULL if you prefer an initial null state instead

Step 2: Build a Wrapper SP #

Create a new stored procedure that wraps your existing update logic, then adds the flag reset step. For example:

CREATE PROCEDURE dbo.Wrapper_UpdateYourTable
    @RecordID INT,
    @UpdatedValue VARCHAR(100) -- Match parameters from your existing SP
AS
BEGIN
    SET NOCOUNT ON;

    -- Execute your existing update procedure
    EXEC dbo.YourExistingUpdateSP @RecordID, @UpdatedValue;

    -- Reset the processing flag
    UPDATE YourTargetTable
    SET IsProcessed = 0 -- Or NULL, based on your initial value
    WHERE RecordID = @RecordID;
END

Step 3: Enforce Usage #

To ensure manual updates don’t bypass this logic:

• Revoke direct UPDATE permissions on YourTargetTable from all users/roles except the database admin.
• Direct all update operations (manual or app-driven) to use the wrapper SP instead of raw UPDATE statements or the original SP.

Pros: Full control over reset logic; works with all update scenarios.
Cons: Requires enforcing usage via permissions and process rules.

2. Application-Level Reset Logic #

If most updates happen through your application (rather than direct database edits), you can handle the flag reset in your app code.

When a user initiates an update (manual or via app workflow):

1. Execute the update statement or call the existing SP to modify the record.
2. Immediately run a second query to reset the flag:

   UPDATE YourTargetTable SET IsProcessed = 0 WHERE RecordID = @ID;
   

To block direct database edits, you can still use permission controls as in the first approach.

Pros: Minimal database changes; leverages existing app workflows.
Cons: Doesn’t prevent flag bypass if users have direct database access.

3. Computed Column (Auto-Reset Based on Changes) #

This is a clever, hands-off approach if you can track when records are modified. The flag will automatically "reset" whenever the record is changed, using a computed column tied to a version or timestamp field.

Step 1: Add Tracking Fields (if not already present) #

If your table doesn’t have a modification timestamp or row version, add one:

-- Option A: Use a modification timestamp
ALTER TABLE YourTargetTable
ADD LastModified DATETIME DEFAULT GETDATE() NULL;
-- Make sure to update this field on any record change (you can use a default constraint or app logic)

-- Option B: Use a row version (auto-updates on any change)
ALTER TABLE YourTargetTable
ADD RecordVersion ROWVERSION;

Step 2: Add the Computed Flag Column #

Create a computed column that checks if the record has been modified since the last processing:

-- First, add a field to track when the record was processed by the other app
ALTER TABLE YourTargetTable
ADD LastProcessedDateTime DATETIME NULL; -- Or LastProcessedVersion BINARY(8) if using ROWVERSION

-- Then create the computed flag
ALTER TABLE YourTargetTable
ADD IsProcessed AS 
    CASE 
        WHEN LastModified > LastProcessedDateTime THEN 0 -- Modified since processing: reset to unprocessed
        ELSE 1 -- Not modified since processing: remains processed
    END 
PERSISTED;

-- If using ROWVERSION:
-- ADD IsProcessed AS 
--     CASE 
--         WHEN RecordVersion > LastProcessedVersion THEN 0
--         ELSE 1
--     END 
-- PERSISTED;

How It Works: #

• When the other app finishes processing a record, it updates LastProcessedDateTime (or LastProcessedVersion) to the current value.
• Any subsequent change to the record will update LastModified (or RecordVersion), triggering the computed column to flip IsProcessed back to 0 automatically.

Pros: Fully automatic; no triggers or wrapper SPs needed.
Cons: Requires existing or added tracking fields; the flag’s logic is tied to modification history (matches your requirement, but worth confirming alignment).

Pick the approach that best fits your environment:

• Use the wrapper SP + permissions if you need strict control over all update paths.
• Go with app-level logic if updates are mostly app-driven and you can restrict direct DB access.
• The computed column is ideal if you already track record modifications and want a hands-off solution.

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