Data sanitation options on INSERT or UPDATE

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.

0

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)

0

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.

2

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 

0

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().

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

0

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.

New contributor

Vlado Bošnjaković is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

0

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật