May 16, 2010 at 2:17 pm
I know that IF Update([column name]) can be used in a trigger to control the logic if a particular column is updated.
If I wanted the trigger NOT to fire when one field is updated, but any of the others are updated, how would I construct the IF?
In the trigger below, the only time that the trigger shouldn't fire is if the txtVendorCrossReference field is the one and only field that was updated.
[sql]
USE [TrailerManagementSystem]
GO
/****** Object: Trigger [dbo].[trg_UpdateTrailerInventory_AfterUpdate] Script Date: 05/16/2010 16:12:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_UpdateTrailerInventory_AfterUpdate] ON [dbo].[tblTrailerInventory]
AFTER UPDATE
AS
BEGIN
INSERT INTO tblTrailerInventoryHistory (txtTrailerDOTNumber, ynIsInFleet, intInventoryDefinitionId, dteStartDateTime, dteEndDateTime, txtUserId, dteDateTimeStamp, lngBranchId, lngTrailerVendorId, intFleetAssignment)
SELECT txtTrailerDOTNumber, ynIsInFleet, intInventoryDefinitionId, dteDateTimeStamp, GETDATE(), REPLACE(SUSER_NAME(),HOST_NAME() + '\','') as userId, GETDATE() as dateTimeStamp, lngBranchId, lngTrailerVendorId, intFleetAssignment from inserted
UPDATE tblTrailerInventory SET dteDateTimeStamp = GETDATE() WHERE EXISTS (SELECT txtTrailerDOTNumber FROM inserted WHERE tblTrailerInventory.txtTrailerDOTNumber = inserted.txtTrailerDOTNumber)
END
GO
[/sql]
May 16, 2010 at 2:47 pm
The UPDATE() can be confusing. It's detecting whether a column was included in an update, not necessarily whether the value of a column for a specific row was ACTUALLY changed. So you would actually want to check the values from inserted and deleted to know what rows got updated accordingly
Something along the line of
USE [TrailerManagementSystem]
GO
/****** Object: Trigger [dbo].[trg_UpdateTrailerInventory_AfterUpdate] Script Date: 05/16/2010 16:12:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_UpdateTrailerInventory_AfterUpdate] ON [dbo].[tblTrailerInventory]
AFTER UPDATE
AS
BEGIN
INSERT INTO tblTrailerInventoryHistory (txtTrailerDOTNumber, ynIsInFleet, intInventoryDefinitionId, dteStartDateTime, dteEndDateTime, txtUserId, dteDateTimeStamp, lngBranchId, lngTrailerVendorId, intFleetAssignment)
SELECT txtTrailerDOTNumber, ynIsInFleet, intInventoryDefinitionId, dteDateTimeStamp, GETDATE(), REPLACE(SUSER_NAME(),HOST_NAME() + '\','') as userId, GETDATE() as dateTimeStamp, lngBranchId, lngTrailerVendorId, intFleetAssignment from inserted
--NEW STUFF
WHERE
txtTrailerDOTNumber in
(
select txtTrailerDOTNumber
from
(select everycolumnExcepttxtVendorCrossReference --<<replace this
from inserted
EXCEPT
select everycolumnExcepttxtVendorCrossReference --<<replace this
from deleted
) s
)
--END NEW STUFF
UPDATE tblTrailerInventory SET dteDateTimeStamp = GETDATE() WHERE EXISTS (SELECT txtTrailerDOTNumber FROM inserted WHERE tblTrailerInventory.txtTrailerDOTNumber = inserted.txtTrailerDOTNumber)
END
GO
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 16, 2010 at 4:49 pm
david.c.holley (5/16/2010)
If I wanted the trigger NOT to fire when one field is updated, but any of the others are updated, how would I construct the IF?
Let me note that technically the trigger will fire - that's the nature of triggers. What you can control is what the trigger will do once it gets control. Matt's suggestion is pretty spot on on that matter.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply