SQL Server Trigger [modified]

Jay Royall

Hi, Scenario: I have a database which is going to be replicated via a set of web services. The web services will select all records where [AwaitingSync] = true. I have cerated two triggers for each table which sets [AwaitingSync] to true when a record is inserted or updated. The web service then sets [AwaitingSync] to false for all records. Problem: Due to a combination of my inexperience and lack of thought there is a slight problem(you'ver probably already noticed what the problem is!). After synching, when the web service sets [AwaitingSync] to false the update trigger fires and sets it back to true! :doh: My question is this then; what can I do about this? Can I add a conditional statement to the trigger so that it only fires if a particular column is being updated(or not being updated in this case). Or maybe the trigger be temporarily disabled while the [AwaitingSync] column is being updated. Or are there any other solutions to this? Thanks for your time. EDIT: Think I've found a solution but I would still appreciate your views if there is a better way of doing this. I have modified the trigger to check the value of [AwaitingSync] before updating.

IF(SELECT [AwaitingSync] FROM DELETED) = 0
BEGIN
UPDATE tblProductCategories
SET [DateModified] = GetDate(),
[AwaitingSync] = 1
WHERE [ID] = (SELECT [ID] FROM INSERTED)
END

modified on Monday, October 25, 2010 11:21 AM

Chris Meech · modified on Monday, October 25, 2010 11:21 AM

Your update trigger should exclude the column [Awaiting Synch] from the list of columns that the trigger should fire on. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

Jay Royall

Ah! I didn't realise that you could specify what columns fired the trigger. This is the current trigger definition:

CREATE TRIGGER [dbo].[trgProductCategoriesUPDATE]
ON [dbo].[tblProductCategories]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE	tblProductCategories
SET	\[DateModified\] = GetDate(),
	\[AwaitingSync\] = 1
WHERE	\[ID\] = (SELECT \[ID\] FROM INSERTED)

END

Which fires when any column is updated. Are you saying that it should look something like this:

CREATE TRIGGER [dbo].[trgProductCategoriesUPDATE]
ON [dbo].[tblProductCategories]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    IF UPDATE(CategoryName)
    BEGIN
        UPDATE  tblProductCategories
        SET     \[DateModified\] = GetDate(),
	        \[AwaitingSync\] = 1
        WHERE	\[ID\] = (SELECT \[ID\] FROM INSERTED)
    END

END

Thanks :)

Chris Meech

Yes that is correct. I'm not familiar with the SQL Server flavour of doing things, but when defining a trigger you can specify a list of columns as opposed to the entire table, that the trigger is fired on. Glad that has helped you out. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]