Limit the SQL trigger to work only if specifed fields have changed in MSSQL 2008 W/E

You have no WHERE clause on your update -therefore this will set ustatus to Y for all records in Contact2

Is this not the cause of your problem?

Your current trigger is updating the field on every record in the table, not just the records that were modified (since you're updating "contaCT2" without a WHERE clause).

Think of a trigger as a stored procedure that's run when a table change happens - there's nothing special in the trigger that makes it act only on the rows that were updated (or inserted/deleted). You have to tell it which rows you want to update.

The way you do that is by accessing either of two virtual tables in your trigger, called INSERTED and DELETED (which hold the rows that were changed). If you just want to just update the fields that were changed, you can keep the format of your trigger unchanged, but change the UPDATE statement to something something like this:

UPDATE contaCT2
   SET ustatus = 'Y'
  FROM contaCT2 c
  JOIN INSERTED i
    ON c.primarykeyfield = i.primarykeyfield -- Obviously, change this to your primary key
  JOIN DELETED d
    on c.primarykeyfield = d.primarykeyfield -- Obviously, this one too
 WHERE i.Company <> d.Company
    OR i.Contact <> d.Contact
    OR i.Address1 <> d.Address1
    OR i.Address2 <> d.Address2
    OR i.Address3 <> d.Address3
    OR i.City <> d.City
    OR i.ZipCode <> d.ZipCode

Select allOpen in new window

In this case, it joins to the inserted/deleted virtual tables, so it will only act on rows that were modified as part of the action that executed the trigger, and since there's a WHERE clause, it only modifies rows where one of those fields has changed as well.

Hi ryan,

That was a great starter, I'm getting an error  "Invalid Object Name UPDATED" from the code snippet.


I have commented the lines as below as I think they relate, can you check them and point out where my assumption is wrong?
 

UPDATE contaCT2                        --  normal update table syntax  
SET ustatus = 'Y'                       -- normal set field = syntax
  FROM contaCT2 c                    -- alias contact2 as table c
  JOIN INSERTED i                      -- joining INSERTED temp table as i
    ON c.accountno = i.accountno -- link contact2 to inserted temp table
  JOIN UPDATED d                        -- joining UPDATED temp table as d  
    on c.accountno = d.accountno -- link contact2 to updated temp table
 WHERE i.Company <> d.Company  -- where inserted  table.company is different from updated table company
    OR i.Contact <> d.Contact           -- as previous line
    OR i.Address1 <> d.Address1      -- as previous line  
    OR i.Address2 <> d.Address2      -- as previous line  
    OR i.Address3 <> d.Address3      -- as previous line  
    OR i.City <> d.City                        -- as previous line
    OR i.ZipCode <> d.ZipCode               -- as previous line

There is no link to CONTACT1 which holds  Company, Contact, Addressline1/2/3 etc.

Where does this come into the sql?

Cheers
Bernard

USE [CENTRAL]
GO
/****** Object:  Trigger [dbo].[UPD_USTATUS_C2]    Script Date: 04/05/2011 17:01:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER TRIGGER [dbo].[UPD_USTATUS_C2] 
   ON  [dbo].[CONTACT1] 
   AFTER INSERT, UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON
    -- Insert statements for trigger here
UPDATE contaCT2
   SET ustatus = 'Y'
  FROM contaCT2 c
  JOIN INSERTED i
    ON c.accountno = i.accountno -- Obviously, change this to your primary key
  JOIN UPDATED d
    on c.accountno = d.accountno -- Obviously, this one too
 WHERE i.Company <> d.Company
    OR i.Contact <> d.Contact
    OR i.Address1 <> d.Address1
    OR i.Address2 <> d.Address2
    OR i.Address3 <> d.Address3
    OR i.City <> d.City
    OR i.ZipCode <> d.ZipCode	
END

Select allOpen in new window

Before update or insert new rows please check the changed field (Status as you want)

by

Select field from table where id = ?

select field from inserted/updated where id = ?

if both values are same do nothing else work as you wish insert or update row.

everything above seems right for the first part of your question dealing with why it takes so long to run the query. however the second part of your question dealing with only doing logic if a certain field is updated is done like this


so you will probably want to check if any of the columns are updated so if (updated (col1) or updated(col2)) begin do work end

IF UPDATE(logtime)
	BEGIN
		-- do work here
	END

Select allOpen in new window

Thanks Ryan for the explanation, it was most helpful