Mfcttrf

How can I help our ranger feel special about her beast companion?

In this iconic lunar orbit rendezvous photo of John Houbolt, why do arrows #5 and #6 point the "wrong" way?

The most secure way to handle someone forgetting to verify their account?

Company looks for long-term employees, but I know I won't be interested in staying long

How many opportunity attacks can you make per turn before becoming exhausted?

Pauli exclusion principle - black holes

May I use a railway velocipede on actively-used British railways?

Which failed attempts have there been to find a contradiction in ZFC or ZF?

What is this green alien supposed to be on the American covers of the "Hitchhiker's Guide to the Galaxy"?

Which modern firearm should a time traveler bring to be easily reproducible for a historic civilization?

Symbolic integration of logmultinormal distribution

How is a T-SQL Trigger that only fires on real changes made?

When designing an adventure, how can I ensure a continuous player experience in a setting that's likely to favor TPKs?

Manager asking me to eat breakfast from now on

How slow ( not zero) can a car engine run without hurting engine and saving on fuel

What would be the safest way to drop thousands of small, hard objects from a typical, high wing, GA airplane?

Why isn't a binary file shown as 0s and 1s?

How did J. J. Thomson establish the particle nature of the electron?

Should I have one hand on the throttle during engine ignition?

How long were the Apollo astronauts allowed to breathe 100% oxygen at 1 atmosphere continuously?

What is the name for the average of the largest and the smallest values in a given data set?

I have found a mistake on someone's code published online: what is the protocol?

Should I have shared a document with a former employee?

Do pedestrians imitate auto traffic?

How is a T-SQL Trigger that only fires on real changes made?

Trigger in combination with transactionSQL Server 2012 Replication, how to ignore action with specific trigger?How to commit transaction on an after update event trigger?assignment in trigger function does not workPostgresql trigger update whole table after deleteAfter insert trigger only using single inserted virtual table rowSQL Server : How to disable trigger for an update only for your current session?How to ensure that row with same PK inserted replaces old row in MS SQL Server, instead of throwing exception of PK Violation?mysql trigger before insertHow to convert UPDATE operation into INSERT using triggers

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

I have a table trigger on UPDATE and INSERT that adds a row to another table. It only needs to add a row if one of four columns are changed. I tried using IF UPDATE(col) to test for changes but it has a blind spot. It only tests that some value came in. I need to go deeper, I need to compare the old and new values to see of a true change has occurred. It has to work with both INSERT and UPDATE.

In the case of an UPDATE that's easy because both the inserted and deleted tables have values I can compare within the trigger. However, for the INSERT only the insert table has values. Because I need this all in the same trigger, how do I handle that INSERT case?

Here is the script of the trigger I want to modify:

ALTER TRIGGER [dbo].[trATPerson_alter] 
 ON [mydb].[dbo].[AT_Person]
 AFTER INSERT,UPDATE
AS 
BEGIN
 SET NOCOUNT ON;

 -- Not all updates require a push
 IF (UPDATE([First_Name]) OR UPDATE([Last_Name]) OR UPDATE([JobCode]) OR UPDATE([Inactive]))
 BEGIN
 INSERT INTO [mydb].[dbo].[AT_Person_To_Push] (
 [Facility],
 [VendorID],
 [Person_code],
 [First_Name],
 [Last_Name],
 [JobCode],
 [Alink],
 [Inactive]
 )
 SELECT [Facility],
 [VendorID],
 [Person_code],
 [First_Name],
 [Last_Name],
 [JobCode],
 [Alink],
 [Inactive]
 FROM inserted 
 END
END

t-sql trigger

share|improve this question

asked 8 hours ago

WillGWillG

17066 bronze badges

add a comment | 

2

I have a table trigger on UPDATE and INSERT that adds a row to another table. It only needs to add a row if one of four columns are changed. I tried using IF UPDATE(col) to test for changes but it has a blind spot. It only tests that some value came in. I need to go deeper, I need to compare the old and new values to see of a true change has occurred. It has to work with both INSERT and UPDATE.

In the case of an UPDATE that's easy because both the inserted and deleted tables have values I can compare within the trigger. However, for the INSERT only the insert table has values. Because I need this all in the same trigger, how do I handle that INSERT case?

Here is the script of the trigger I want to modify:

ALTER TRIGGER [dbo].[trATPerson_alter] 
 ON [mydb].[dbo].[AT_Person]
 AFTER INSERT,UPDATE
AS 
BEGIN
 SET NOCOUNT ON;

 -- Not all updates require a push
 IF (UPDATE([First_Name]) OR UPDATE([Last_Name]) OR UPDATE([JobCode]) OR UPDATE([Inactive]))
 BEGIN
 INSERT INTO [mydb].[dbo].[AT_Person_To_Push] (
 [Facility],
 [VendorID],
 [Person_code],
 [First_Name],
 [Last_Name],
 [JobCode],
 [Alink],
 [Inactive]
 )
 SELECT [Facility],
 [VendorID],
 [Person_code],
 [First_Name],
 [Last_Name],
 [JobCode],
 [Alink],
 [Inactive]
 FROM inserted 
 END
END

t-sql trigger

share|improve this question

asked 8 hours ago

WillGWillG

17066 bronze badges

add a comment | 

I have a table trigger on UPDATE and INSERT that adds a row to another table. It only needs to add a row if one of four columns are changed. I tried using IF UPDATE(col) to test for changes but it has a blind spot. It only tests that some value came in. I need to go deeper, I need to compare the old and new values to see of a true change has occurred. It has to work with both INSERT and UPDATE.

In the case of an UPDATE that's easy because both the inserted and deleted tables have values I can compare within the trigger. However, for the INSERT only the insert table has values. Because I need this all in the same trigger, how do I handle that INSERT case?

Here is the script of the trigger I want to modify:

ALTER TRIGGER [dbo].[trATPerson_alter] 
 ON [mydb].[dbo].[AT_Person]
 AFTER INSERT,UPDATE
AS 
BEGIN
 SET NOCOUNT ON;

 -- Not all updates require a push
 IF (UPDATE([First_Name]) OR UPDATE([Last_Name]) OR UPDATE([JobCode]) OR UPDATE([Inactive]))
 BEGIN
 INSERT INTO [mydb].[dbo].[AT_Person_To_Push] (
 [Facility],
 [VendorID],
 [Person_code],
 [First_Name],
 [Last_Name],
 [JobCode],
 [Alink],
 [Inactive]
 )
 SELECT [Facility],
 [VendorID],
 [Person_code],
 [First_Name],
 [Last_Name],
 [JobCode],
 [Alink],
 [Inactive]
 FROM inserted 
 END
END

t-sql trigger

share|improve this question

asked 8 hours ago

WillGWillG

17066 bronze badges

ALTER TRIGGER [dbo].[trATPerson_alter] 
 ON [mydb].[dbo].[AT_Person]
 AFTER INSERT,UPDATE
AS 
BEGIN
 SET NOCOUNT ON;

 -- Not all updates require a push
 IF (UPDATE([First_Name]) OR UPDATE([Last_Name]) OR UPDATE([JobCode]) OR UPDATE([Inactive]))
 BEGIN
 INSERT INTO [mydb].[dbo].[AT_Person_To_Push] (
 [Facility],
 [VendorID],
 [Person_code],
 [First_Name],
 [Last_Name],
 [JobCode],
 [Alink],
 [Inactive]
 )
 SELECT [Facility],
 [VendorID],
 [Person_code],
 [First_Name],
 [Last_Name],
 [JobCode],
 [Alink],
 [Inactive]
 FROM inserted 
 END
END

share|improve this question

asked 8 hours ago

WillGWillG

17066 bronze badges

share|improve this question

asked 8 hours ago

WillGWillG

17066 bronze badges

asked 8 hours ago

WillGWillG

17066 bronze badges

asked 8 hours ago

WillGWillG

17066 bronze badges

1 Answer
1

active

oldest

votes

3

You can handle both INSERT and UPDATE with an EXCEPT set operator.
The EXISTS will only evaluate to TRUE both if it's just an INSERT, or if it's an UPDATE with different values for any of these columns.

IF EXISTS (
 SELECT First_Name, Last_Name, JobCoe, Inactive FROM inserted
 EXCEPT
 SELECT First_Name, Last_Name, JobCoe, Inactive FROM deleted
 )
BEGIN...

share|improve this answer

edited 1 hour ago

answered 7 hours ago

SQLRaptorSQLRaptor

2,96411 gold badge44 silver badges2020 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I haven't needed EXCEPT before so I'm trying read up on it in the MS docs. It seems like that script would work out to be an AND (all four would have to be different) where I want an OR (any one of the four is different). Can you clarify?
  
  – WillG
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The EXCEPT set operator compares the entire tuple, so any difference in values in any of the columns will result in the EXISTS evaluating TRUE. Try it with literals and see for yourself...
  
  – SQLRaptor
  2 hours ago
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f243347%2fhow-is-a-t-sql-trigger-that-only-fires-on-real-changes-made%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

You can handle both INSERT and UPDATE with an EXCEPT set operator.
The EXISTS will only evaluate to TRUE both if it's just an INSERT, or if it's an UPDATE with different values for any of these columns.

IF EXISTS (
 SELECT First_Name, Last_Name, JobCoe, Inactive FROM inserted
 EXCEPT
 SELECT First_Name, Last_Name, JobCoe, Inactive FROM deleted
 )
BEGIN...

share|improve this answer

edited 1 hour ago

answered 7 hours ago

SQLRaptorSQLRaptor

2,96411 gold badge44 silver badges2020 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I haven't needed EXCEPT before so I'm trying read up on it in the MS docs. It seems like that script would work out to be an AND (all four would have to be different) where I want an OR (any one of the four is different). Can you clarify?
  
  – WillG
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The EXCEPT set operator compares the entire tuple, so any difference in values in any of the columns will result in the EXISTS evaluating TRUE. Try it with literals and see for yourself...
  
  – SQLRaptor
  2 hours ago
  
  

  
  
  
  

add a comment | 

3

You can handle both INSERT and UPDATE with an EXCEPT set operator.
The EXISTS will only evaluate to TRUE both if it's just an INSERT, or if it's an UPDATE with different values for any of these columns.

IF EXISTS (
 SELECT First_Name, Last_Name, JobCoe, Inactive FROM inserted
 EXCEPT
 SELECT First_Name, Last_Name, JobCoe, Inactive FROM deleted
 )
BEGIN...

share|improve this answer

edited 1 hour ago

answered 7 hours ago

SQLRaptorSQLRaptor

2,96411 gold badge44 silver badges2020 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I haven't needed EXCEPT before so I'm trying read up on it in the MS docs. It seems like that script would work out to be an AND (all four would have to be different) where I want an OR (any one of the four is different). Can you clarify?
  
  – WillG
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The EXCEPT set operator compares the entire tuple, so any difference in values in any of the columns will result in the EXISTS evaluating TRUE. Try it with literals and see for yourself...
  
  – SQLRaptor
  2 hours ago
  
  

  
  
  
  

add a comment | 

You can handle both INSERT and UPDATE with an EXCEPT set operator.
The EXISTS will only evaluate to TRUE both if it's just an INSERT, or if it's an UPDATE with different values for any of these columns.

IF EXISTS (
 SELECT First_Name, Last_Name, JobCoe, Inactive FROM inserted
 EXCEPT
 SELECT First_Name, Last_Name, JobCoe, Inactive FROM deleted
 )
BEGIN...

share|improve this answer

edited 1 hour ago

answered 7 hours ago

SQLRaptorSQLRaptor

2,96411 gold badge44 silver badges2020 bronze badges

IF EXISTS (
 SELECT First_Name, Last_Name, JobCoe, Inactive FROM inserted
 EXCEPT
 SELECT First_Name, Last_Name, JobCoe, Inactive FROM deleted
 )
BEGIN...

share|improve this answer

edited 1 hour ago

answered 7 hours ago

SQLRaptorSQLRaptor

2,96411 gold badge44 silver badges2020 bronze badges

answered 7 hours ago

SQLRaptorSQLRaptor

2,96411 gold badge44 silver badges2020 bronze badges

answered 7 hours ago

SQLRaptorSQLRaptor

2,96411 gold badge44 silver badges2020 bronze badges