Data sanitation options on INSERT or UPDATE

Question

The answer in this post is pretty much what I did to get my problem: SQL Server trigger before insert or update depend on value

I have a CUSTOMER table that has multiple triggers attached. One of these is responsible for sanitizing data entered into a specific column on the table...stripping a comma off the end of a customer's NAME.

Each time the sanitizing trigger is fired, it ends up triggering the other triggers on the table, which causes a lot of needless logic and slows the update to a crawl.

I could do this in the Application, but there's many other SQL processes out there that updates data in this table, and it'd miss all of those.

Is there a way to update a table without triggering the other triggers temporarily?

If not, is there some way of doing data sanitation on INSERT/DELETE that doesn't involve a trigger?

Things I tried:

I tried finding a way to run my trigger without hitting others and saw this: Will one trigger activate another trigger in SQL Server?

There's a setting..Allow Triggers To Fire Others, but that's a global change and could break so much else.

I looked for some variant of CHECK CONSTRAINT to see if it could actually correct data, but it doesn't.

Maybe I could play with the order of the triggers? Not sure it that would actually help, though.

Answer 1

You can't stop the triggers firing, but there are various ways to detect whether the trigger has fired after the outer trigger. As already mentioned TRIGGER_NESTLEVEL() is one method, but that is delicate because if there are any situations where you require nesting, this breaks.

Using UPDATE(column)

Is another option, if a particular column is only ever updated/inserted from the trigger in question. You can even add a column especially for this purpose. In your nested trigger you test this and return immediately if the condition for the outer trigger running is met.

This is fragile because a change to an update might cause this to stop working.

note: just because update returns true doesn't mean the value changed.

Using COLUMNS_UPDATED()

Is another possibility, with more flexibility than the previous option, because now you can check for a specific set of columns being updated. In your nested trigger you test this and return immediately if the condition for the outer trigger running is met.

This is also fragile because a change to an update might cause this to stop working.

note: just because columns_updated returns true doesn't mean the value changed.

Using SESSION_CONTEXT or CONTENT-INFO if pre-2016.

Is another option, where you set this to a value in the outer trigger and test whether it was set in the nested trigger, returning immediately if set.

This is robust - although note the MARS remark.

Answer 2

Lots of reasonable answers here, but I think the simplest approach is to sanitize the data in an INSTEAD OF trigger, which will fire before you actually insert the data. That way your AFTER triggers only fire once because you only modify the table once. EG

drop table if exists t
go
create table t(id int primary key, a varchar(20))

go

create or alter trigger t_II on t
instead of insert
as
begin
  set nocount on

  print 'instead of trigger running'
  insert into t(id,a) 
  select id, trim(a) from inserted 
end

go 

create or alter trigger t_AI on t
after insert
as
begin
  set nocount on 

  print 'after trigger running'
end

go

insert into t(id,a) values (1, '  a  '),(2, '  a  ')

outputs

instead of trigger running
after trigger running

(2 rows affected)

Answer 3

ze problem

Each time the sanitizing trigger is fired, it ends up triggering the other triggers on the table, which causes a lot of needless logic and slows the update to a crawl.

You can do this by creating a #temporary table in the sanitizing trigger, and then having the other triggers check for its existence before going into the main logic.

From How To Get SQL Server Triggers To Selectively Fire:

-- Add this to the sanitizing trigger
CREATE TABLE
    #B7E66DC66D9C4C4182ECCF583D126677
(
    the_crumb bit
);

And then in the other triggers...

--Example trigger that bails early, 
--you'll need to flip the logic to bail on existence rather than non-existence.    
CREATE OR ALTER TRIGGER
    dbo.the_trigger
ON dbo.the_table
INSTEAD OF UPDATE
AS
BEGIN
    IF 
    (
           ROWCOUNT_BIG() = 0
        OR OBJECT_ID(N'tempdb..#B7E66DC66D9C4C4182ECCF583D126677') IS NULL
    )
    BEGIN
        RAISERROR('Only admins can update that column!', 11, 1) WITH NOWAIT;
        RETURN;
    END;
    ELSE
    BEGIN
        SET NOCOUNT ON;
        IF UPDATE(dt)
        BEGIN
            UPDATE t
              SET t.dt = i.dt
            FROM dbo.the_table AS t
            JOIN Inserted AS i
              ON i.id = t.id;
        END
    END;
END;
GO 

Answer 4

Is there a way to update a table without triggering the other triggers temporarily?

Implicitly, yes.

Maybe, maybe, maybe...

Depending on what kind of cleaning you need to do, you can persist the the cleaned results to the same table with a computed column or in a new indexed view (if you don't want to alter the original table), so long as the expression you use is deterministic. Fortunately, all built-in string functions are deterministic (except FORMAT()).

So you could totally do something like:

ALTER TABLE YourSourceTable
ADD NameFieldClaned AS RTRIM(NameField, ',') PERSISTED;

...and axe the trigger that is doing the cleaning logic, instead.

Yes

You don't even have to mark the column as persisted for the expression to be materialized to disk. So long as the expression is deterministic, the non-persisted computed column is indexable (aside from persisted ones being indexable).

No

The one thing you'd want to avoid putting in a computed column is a user-defined scalar function, as these are parallelism inhibitors (to different degrees) and are executed row by agonizing row (RBAR).

Good Luck

Of course the drawback to this solution is any dependencies on the original cleaned column will need to be re-pointed to the new column or indexed view, unless you play 3-card monte on the column names with something like sp_rename().

Answer 5

There's a setting..Allow Triggers To Fire Others, but that's a global change and could break so much else.

There is also TRIGGER_NESTLEVEL(), which you can use inside specific trigger to skip running it again and again.

CREATE TRIGGER dbo.AnotherTrigger ON someTable
AFTER UPDATE, INSERT
AS
BEGIN
    IF TRIGGER_NESTLEVEL() = 1
        PRINT 'Another trigger fired';
END

Answer 6

I may be old school, but I use my own semaphores in cases like this one. For that purpose, I have a table in the database in which the trigger inserts a row with its own ID or name. Other triggers and procedures can check if certain triggers that should have an advantage are running. It is very handy to prevent some recursive references or executions. The tricky part is just when the trigger is not executed properly or hangs. But with proper error handling, you can make sure that the semaphore flag (a record) is removed even if the trigger is not executed entirely.