Need suggestion for Trigger

Question

Need suggestion for Trigger

Zohaib Anwar

Ten Centuries

Points: 1120
More actions
January 15, 2015 at 6:10 am

#397876

I know that Triggers execute invisible to client-application and they are not visible or can be traced in debugging code also Triggers run every time when the database fields are updated and it is overhead on system and Triggers makes system run slower
But I have to maintain and fill the "Last Update Date" and "Last Updated User" on every transactional table . I created following Trigger on 133 tables just to get last update date and last update user I need suggestion that is there any alternate way OR does the following Trigger will make my Database or Application SLOW???
My team is really concerned about the DATABASE Performance. Please suggest something and also let me know that following trigger will make my DB Slow or not?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_trans_insurances_denials]
ON [dbo].[trans_insurances_denials] AFTER UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
IF TRIGGER_NESTLEVEL() > 1
RETURN
DECLARE@ltCurrentDatedatetime,
@lcUserCodevarchar(50)
SELECT@ltCurrentDate= GetDate(),
@lcUserCode= CASE
WHEN SUBSTRING(SYSTEM_USER,PATINDEX('%\%',SYSTEM_USER)+1,10) = 'MMURP001'
THEN 'VCSBATCH'
ELSE UPPER(SUBSTRING(SYSTEM_USER,PATINDEX('%\%',SYSTEM_USER)+1,10))
END
UPDATEx
SET
x.tlast_updated_date = @ltcurrentdate,
x.clast_updated_user = @lcusercode
FROM inserted AS i
JOIN dbo.trans_insurances_denials AS x
ON x.itrans_insurances_denials_pk= i.itrans_insurances_denials_pk
WHERE NOT UPDATE(clast_updated_user) OR i.clast_updated_user IS NULL
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Note that the trigger you posted will work for single-row inserts only. Something like the following will handle batch inserts/updates:

BEGIN

SET NOCOUNT ON

SET XACT_ABORT ON

SET ARITHABORT ON

IF TRIGGER_NESTLEVEL() > 1 RETURN

UPDATE x SET

tlast_updated_date = GETDATE(),

clast_updated_user = CASE

WHEN SUBSTRING(SYSTEM_USER,PATINDEX('%\%',SYSTEM_USER)+1,10) = 'MMURP001' THEN 'VCSBATCH'

ELSE UPPER(SUBSTRING(SYSTEM_USER,PATINDEX('%\%',SYSTEM_USER)+1,10)) END

FROM dbo.trans_insurances_denials AS x

JOIN inserted AS i

ON i.itrans_insurances_denials_pk = x.itrans_insurances_denials_pk

WHERE NOT UPDATE(clast_updated_user) OR i.clast_updated_user IS NULL

END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Zohaib Anwar Ten Centuries Points: 1120 More actions · Answer 2

Thanks for your reply but the main concern is that is there any performance impact?? if I use your trigger or mine?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

There will be a performance impact, whether or not it's significant is a different matter - but you could test this with little effort by timing an update to a copy of one of your tables with and without the trigger.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden