Solved

Help in fine tune my SQL Server trigger.

Posted on 2016-09-27
17
42 Views
Last Modified: 2016-09-29
Hello I need help in fine tune my SQL Server trigger. I need this trigger to execute *ONLY* when the *PhoneNumber* is updated from nothing (NULL) to something and from something to something else (If updating from something to nothing (NULL) then it will not execute.  In shot, the trigger will copy most of the data from table [DATA] record into Table [FOLLOWUP] if the phone number has been added or changed in table [DATA] Thank you.

USE [prd_customers]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[SetFollowUp]
 ON [dbo].[DATA]
 AFTER UPDATE
 AS
 SET NOCOUNT ON;
 INSERT INTO dbo.FOLLOWUP ( 
     RecordIdFK, 
	 FullName, 
	 Address, 
	 City, 
	 State, 
	 Zip, 
	 PhoneNumber, 
	 eMail, 
	 Notes)
 SELECT
	 i.RecordId,
	 i.FullName, 
	 i.Address, 
	 i.City, 
	 i.State, 
	 i.Zip, 
	 i.PhoneNumber, 
	 i.eMail, 
	 i.Notes 
 FROM inserted i
 WHERE PhoneNumber is not null

Open in new window

0
Comment
Question by:Cobra967
  • 5
  • 4
  • 3
  • +2
17 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41817970
Your code looks ok to me.
Why do you think isn't working?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41817978
--Would be something like:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[SetFollowUp]
 ON [dbo].[DATA]
 AFTER UPDATE
 AS
 SET NOCOUNT ON;

IF UPDATE(PhoneNumber) AND
(  
    -- from not null to null
((SELECT PhoneNumber FROM inserted) is null AND (SELECT PhoneNumber FROM deleted) is null)
OR  -- from null to not null
((SELECT PhoneNumber FROM inserted) is not null AND (SELECT PhoneNumber FROM deleted) is null)
)

BEGIN
 INSERT INTO dbo.FOLLOWUP (
     RecordIdFK,
       FullName,
       Address,
       City,
       State,
       Zip,
       PhoneNumber,
       eMail,
       Notes)
 SELECT
       i.RecordId,
       i.FullName,
       i.Address,
       i.City,
       i.State,
       i.Zip,
       i.PhoneNumber,
       i.eMail,
       i.Notes
 FROM inserted i
 WHERE PhoneNumber is not null
 
END
0
 

Author Comment

by:Cobra967
ID: 41817993
As is the trigger appears to update even when some other field is updated (But I have to confirm this - Only I see are duplicated of the same record in FOLLOUP. It is also possible that when user click the save button from the app, the app may overwrite the PhoneNumber again even though is the same as before, So, is this is the case then the trigger should execute only if the PhoneNumber being updated is different than the original (oldvalue?).
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41818007
Use this:
USE [prd_customers]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[SetFollowUp]
 ON [dbo].[DATA]
 AFTER UPDATE
 AS
 SET NOCOUNT ON;
 INSERT INTO dbo.FOLLOWUP ( 
     RecordIdFK, 
	 FullName, 
	 Address, 
	 City, 
	 State, 
	 Zip, 
	 PhoneNumber, 
	 eMail, 
	 Notes)
 SELECT
	 i.RecordId,
	 i.FullName, 
	 i.Address, 
	 i.City, 
	 i.State, 
	 i.Zip, 
	 i.PhoneNumber, 
	 i.eMail, 
	 i.Notes 
 FROM inserted i
 WHERE PhoneNumber is not null
 and i.PhoneNumber in 
 (select PhoneNumber from inserted
  except
  select PhoneNumber from deleted)

Open in new window

0
 
LVL 39

Expert Comment

by:lcohan
ID: 41818215
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[SetFollowUp]
 ON [dbo].[DATA]
 AFTER UPDATE
 AS
 SET NOCOUNT ON;

IF UPDATE(PhoneNumber)
AND
--restrict any updates with same value
((SELECT PhoneNumber FROM inserted) <> (SELECT PhoneNumber FROM deleted))
AND
(  
      -- from not null to null
      ((SELECT PhoneNumber FROM inserted) is null AND (SELECT PhoneNumber FROM deleted) is null)
      OR  
      -- from null to not null
      ((SELECT PhoneNumber FROM inserted) is not null AND (SELECT PhoneNumber FROM deleted) is null)
)
BEGIN

 INSERT INTO dbo.FOLLOWUP (
     RecordIdFK,
       FullName,
       Address,
       City,
       State,
       Zip,
       PhoneNumber,
       eMail,
       Notes)
 SELECT
       i.RecordId,
       i.FullName,
       i.Address,
       i.City,
       i.State,
       i.Zip,
       i.PhoneNumber,
       i.eMail,
       i.Notes
 FROM inserted i
 WHERE PhoneNumber is not null
 
END
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41818351
USE [prd_customers]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[SetFollowUp]
ON [dbo].[DATA]
AFTER UPDATE
AS
SET NOCOUNT ON;
INSERT INTO dbo.FOLLOWUP (
      RecordIdFK,
      FullName,
      Address,
      City,
      State,
      Zip,
      PhoneNumber,
      eMail,
      Notes)
SELECT
      i.RecordId,
      i.FullName,
      i.Address,
      i.City,
      i.State,
      i.Zip,
      i.PhoneNumber,
      i.eMail,
      i.Notes
FROM inserted i
INNER JOIN deleted d ON d.RecordId = i.RecordId
WHERE (d.PhoneNumber IS NULL AND i.PhoneNumber IS NOT NULL) OR
    (d.PhoneNumber <> i.PhoneNumber)
GO /*end of trigger*/
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41818503
As far as I'm aware the truth is that IF you don't use the

IF UPDATE(col_name) ...

structure in a trigger that trigger will fire and action done regardless which is a waste right? especially if "nothing" needs to get logged/audited/queued and I learned that the hard way with some hefty UPDATE trigger action on a huge Clients table that took massive bulk updates (by merchantid).

"UPDATE() is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions."
https://msdn.microsoft.com/en-us/library/ms187326.aspx
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41818517
If you have a table with tens of columns to wrap all or lot of them in UPDATE and build a condition it is kind of tedious. It is way simpler and as fast if not faster to use EXCEPT between INSERTED and DELETED tables with a list of columns you are interested in. Also the UPDATE will "fire" even if you put the same value in a column, which is not really an update, while selecting with excepts will filter out the rows with same values updated.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 41818654
As far as I'm aware the truth is that IF you don't use the
IF UPDATE(col_name) ...
structure in a trigger that trigger will fire and action done regardless which is a waste right?

Quite true and a very valid point.  My adjusted code below.

But this style of code:
 ((SELECT PhoneNumber FROM inserted) is null AND (SELECT PhoneNumber FROM deleted) is null)
is not valid in a trigger, because if more than one row gets updated, you have no idea which row an unordered SELECT will return, and no way to compare each row individually to see if it changed.


CREATE TRIGGER [dbo].[SetFollowUp]
ON [dbo].[DATA]
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(PhoneNumber)
BEGIN
   INSERT INTO dbo.FOLLOWUP (
          RecordIdFK, FullName, Address, City, State, Zip, PhoneNumber, eMail, Notes)
    SELECT
          i.RecordId, i.FullName, i.Address, i.City, i.State, i.Zip, i.PhoneNumber, i.eMail,
          i.Notes
    FROM inserted i
    INNER JOIN deleted d ON d.RecordId = i.RecordId
    WHERE (d.PhoneNumber IS NULL AND i.PhoneNumber IS NOT NULL) OR
        (d.PhoneNumber <> i.PhoneNumber)
END /*IF*/
GO
/*end of trigger*/
0
 

Author Closing Comment

by:Cobra967
ID: 41819122
Thank you everyone for the overwhelming contribution to my question! I did not have the to try everyone's suggestion but Scott's code seem to work just fine. Thank you all!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41819757
You should start trying the suggestions in the order they are posted. That would be fair.
0
 

Author Comment

by:Cobra967
ID: 41819776
I can certainly see your point, I will do better next time! I am not as experienced as you guys are and I have hard time to pick the "best" solution. For sure you guys have pointed out items that I did not even think about in interest of performance and logic.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41820231
Certainly consider solutions in order.  But you should consider accuracy and completeness as well.

For example, technically this method of checking for a changed number is not correct:

FROM inserted i
 WHERE PhoneNumber is not null
and i.PhoneNumber in
 (select PhoneNumber from inserted
  except
  select PhoneNumber from deleted)


Say, for example, Person 1's number changed from 111 to 222.  Person 2's number changed from 333 to 111.  In that situation, phone number 111 would be excluded from the results because of the EXCEPT [ I think; I find that logic somewhat complex to follow], but it shouldn't be, since Person 2's number did change to 111.  

Not likely to happen?  Probably true.  But I think it's possible that two phone numbers could get transposed while being entered, and later someone goes back in to correct them both.  Since it's so rare, the bug could show up years from now instead of during testing, which could be even worse, since it's hard to track down.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41820314
Correct. This should do it then:
USE [prd_customers]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[SetFollowUp]
 ON [dbo].[DATA]
 AFTER UPDATE
 AS
 SET NOCOUNT ON;
 INSERT INTO dbo.FOLLOWUP ( 
     RecordIdFK, 
	 FullName, 
	 Address, 
	 City, 
	 State, 
	 Zip, 
	 PhoneNumber, 
	 eMail, 
	 Notes)
 SELECT
	 i.RecordId,
	 i.FullName, 
	 i.Address, 
	 i.City, 
	 i.State, 
	 i.Zip, 
	 i.PhoneNumber, 
	 i.eMail, 
	 i.Notes 
 FROM inserted i
 inner join 
 (select RecordId,PhoneNumber from inserted
  except
  select RecordId,PhoneNumber from deleted) changed
	on changed.RecordId=i.RecordId
	and changed.PhoneNumber=i.PhoneNumber
WHERE i.PhoneNumber is not null

Open in new window

However, generally speaking the asker SHOULD test the solutions in the order they are offered.
0
 

Author Comment

by:Cobra967
ID: 41820349
To my defense, it is unrealistic to expect users to test possibly dozens of proposals when the response is *overwhelming*. I don't know what your experience is on this subject, however it is my experience that, normally, the last response is the best one since no improvements or refinements are possible. Sure, the first options "could" possible work, but it may not necessarily be the best one like has been pointed out in this very constructive conversation. As for the rating, I am not sure why that is very important to you. Do you get pay accordingly? From my point of view, the Best solution is a simple mechanizing to tell everyone with similar circumstances what did work and what I overall felt was the most effective solution in terms of performance and logic and functionality. I know there is endless way to do things, Normally I am not a fan or very lengthy and convoluted solutions when I a simple and as effective exist. So, how many points everyone should deserve in your opinion? What ultimately is the best solution? Do you all agree?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41820392
That is not the case. If what you said were true then only the experts that post the last would be rewarded points. There are questions with multiple solution given and in that case you don't start to test only the last one because if it works and given points would be totally disrespectful for the time spent by the other experts experts with the result that they will either stop posting or will wait like on eBay to be the last to bid!

You HAVE to test the solutions in order they are given and the first one that works for your problem should be awarded. Scott also is right when he says that in the case of multiple solutions it is recommendable to go over all of them to see which one is more complete and offer best performance if possible. You can also split the points if you consider that all of them are good as alternative solutions.

Anyway, testing only the last and awarding only that with points is against the EE philosophy.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41821236
normally, the last response is the best one since no improvements or refinements are possible.
Not true at all. Just go and check random closed questions and see how many of them have the last comment marked as solution.
Besides, all Experts here are not paid. They come here because they like to help others and the minimum that an asker can do is to consider their comments by giving them a feedback or in the future they might not want to help you anymore.
Cheers
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 51
.htaccess file settings 4 36
What's the difference between these two "qualifiers?" 3 35
sql query Help 12 33
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now