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

Hi team,

I have a requirement to update USTATUS with 'Y', in the Contact2 table, if any of the Company, Contact, Address1, Address2,Address3, City, ZipCode fields in the Contact1 table have changed.

I tried a trigger in SQL and it works but it takes a lot of time, >10 seconds to complete. There are 39146 records in Contact1. It appears to be processing through all the records?

How do I force the trigger to change only the current record?

Can I limit the trigger to work only if the Company, Contact, Address1, Address2,Address3, City, ZipCode fields have changed?

All help is appreciated
Cheers
Bernard
USE [CENTRAL]
GO
/****** Object:  Trigger [dbo].[UPD_USTATUS_C2]    Script Date: 04/05/2011 15:13:44 ******/
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 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'
	
END

Open in new window

BernardGBaileyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TelnetServicesCommented:
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?
Ryan McCauleyEnterprise Analytics ManagerCommented:
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

Open 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.
BernardGBaileyAuthor Commented:
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

Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Alpesh PatelAssistant ConsultantCommented:
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.
Ryan McCauleyEnterprise Analytics ManagerCommented:
The second table name is called "DELETED", not "UPDATED" - see my original code snippet. Those two tables (INSERTED and DELETED) will both always exist inside a trigger and have the same schema as a source table, but depending on the type of trigger, they may or may not have rows in them:

Trigger the result of an insert - INSERTED table will contain the rows that were added and DELETED table will be empty
Trigger the result of a delete - INSERTED table will be empty and DELETED will have the rows that were removed from the source table
Trigger the result of an update - INSERTED will contain the results of the update (the "new" rows) and DELETED will contain the old copy of the rows (the versions that were "deleted")

You're getting the syntax error before you've changed the DELETED table in my example to a table called UPDATED, which doesn't exist.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LCSandman8301Commented:
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

Open in new window

BernardGBaileyAuthor Commented:
Thanks Ryan for the explanation, it was most helpful
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.