SQL Server Performance Forum – Threads Archive

Triggery

Hi, Our transaction table has 3 triggers (mainly for audit purpose): -a trigger for INSERT, UPDATE, DELETE
-another trigger for INSERT
-and another for DELETE I am doing performance tuning and was wandering if this is bad setup since e.g. for delete, theres calls to 2 separate triggers. Thanks

It’s hard to say without looking at the triggers. Generally, you only want one trigger. There are exceptions of course. Can you post the triggers? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Here are the triggers (in this example trigger 3 is for update instead of delete).
They are pretty trivial with addition of creating time delay in trigger 1 and conditional execution depending on db. trigger 1
——— CREATE TRIGGER dbo.LoadsAuditTR ON dbo.Loads
FOR INSERT, UPDATE, DELETE
AS
BEGIN — Don’t execute the trigger on a remote (replicated) IF RIGHT(DB_NAME(), 2) = ‘xxxxx’
RETURN
DECLARE @AuditDateTime DATETIME
, @PreviousUpdateTime DATETIME
, @ID INTEGER — Set TIME once, so that all rows get marked with the exact same time.
— This is particularly important for Billing which needs to be able to match up DELETES and INSERTS
SET @AuditDateTime = GetDate()
— The following code ensures that each discrete update to a LOAD will get recorded in the Audit table
— with a discrete time.
— That is if one update happens, then another within 3 milliseconds, this audit trigger will wait
— for 3 milliseconds to pass to ensure the Audit rows are created with a different time for the
— second update
SELECT TOP 1 @ID = ID
FROM DELETED IF NOT (@ID IS NULL) — if not null, then this an update to a Load
BEGIN
SELECT @PreviousUpdateTime = MAX(AuditModifiedDateTime)
FROM LoadsAudit (NOLOCK)
WHERE ID = @ID IF @PreviousUpdateTime = @AuditDateTime
BEGIN
WAITFOR DELAY ’00:00:00.003′
SET @AuditDateTime = GetDate()
END
END
— The LoadsAudit table must have all the columns of the Loads table in exactly the same order
— Insert log of "Deleted" rows before "Inserted" to correctly reflect sequence of events
INSERT INTO dbo.LoadsAudit
SELECT @AuditDateTime, ‘D’, *
FROM DELETED INSERT INTO dbo.LoadsAudit
SELECT @AuditDateTime, ‘I’, *
FROM INSERTED
END
trigger 2
——— CREATE TRIGGER [LoadsCancelStatusInsTR] ON dbo.Loads
FOR INSERT
AS
— maintain cancelled status on table stocktransactions
— as in uscPostShrinkage
— Don’t do this if on a remote server
IF DB_NAME () <> ‘xxxxxx’
RETURN
UPDATE StockTransactions
SET StockTransactions.Cancelled = INSERTED.Cancelled
FROM StockTransactions, INSERTED
WHERE StockTransactions.LoadID = INSERTED.ID trigger 3
——— CREATE TRIGGER [LoadsCancelStatusUPDTR] ON dbo.Loads
FOR UPDATE
AS
— maintain cancelled status on table stocktransactions
— as in uscPostShrinkage
IF DB_NAME () <> ‘xxxxxxx’
RETURN
UPDATE StockTransactions
SET StockTransactions.Cancelled = INSERTED.Cancelled
FROM StockTransactions, INSERTED, DELETED
WHERE StockTransactions.LoadID = INSERTED.ID
AND INSERTED.ID = DELETED.ID
AND INSERTED.Cancelled <> DELETED.Cancelled

For maintainability, i would keep it how it is…otherwise you would have the code for trigger 1 duplicated 3 times for insert, update, delete… Performance wise this might not be the best option though! ‘I reject your reality and substitute my own’ – Adam Savage

You can include this in a single IUD trigger to see what event has occurred, and handle the flow accordingly: DECLARE @Insert BIT, @Update BIT, @Delete BIT SELECT @Insert = 0, @Update = 0, @Delete = 0 IF EXISTS (SELECT &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K> FROM inserted WHERE &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K> IS NOT NULL) BEGIN SET @Insert = 1 END IF EXISTS (SELECT &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K> FROM deleted WHERE &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K> IS NOT NULL) BEGIN SET @Delete = 1 END IF (@Insert = 1 AND @Delete = 1) BEGIN SELECT @Update = 1, @Insert = 0, @Delete = 0 END –If none of the BIT variables is set to 1, then the trigger was fired because of a cascading update/delete through DRI.

]]>