AFTER TRIGGER QUESTION - How To filter Non Real Updates

Question

Post reply

AFTER TRIGGER QUESTION - How To filter Non Real Updates

YSLGuru

SSC-Insane

Points: 21632
More actions
March 16, 2023 at 11:52 pm

#4161704
NEERMIND I just figured it out.
- This topic was modified 1 year, 1 month ago by YSLGuru.
Kindest Regards,
Just say No to Facebook!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003874 More actions · Answer 1

Cool! What's the answer?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 2

Probably something with

If exists(select * from inserted)

N 56°04'39.16"
E 12°55'05.25"

YSLGuru SSC-Insane Points: 21632 More actions · Answer 3

Dumb Dumb me was looking at it in the opposite direction. Its 1AM and because the work computer is in the hallway next to my kids bedrooms I can't get to the code but long story short I was using And I.Col01 <> D.Col1 (where I = INSERTED and D = DELETED) for several of the columns when I should have been using and I.Col1 = D.Col1. I'll try tomorrow when at work to post the actual code so you can see my mistake. Once I changed to = from <>it worked perfectly

Thanks Jeff

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1003874 More actions · Answer 4

YSLGuru wrote:

Dumb Dumb me was looking at it in the opposite direction. Its 1AM and because the work computer is in the hallway next to my kids bedrooms I can't get to the code but long story short I was using And I.Col01 <> D.Col1 (where I = INSERTED and D = DELETED) for several of the columns when I should have been using and I.Col1 = D.Col1. I'll try tomorrow when at work to post the actual code so you can see my mistake. Once I changed to = from <>it worked perfectly
Thanks Jeff

I was just making sure. Sounds like you got it. Just don't forget that you need to join on the PK for both, as well (again, just making sure). Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

YSLGuru SSC-Insane Points: 21632 More actions · Answer 5

Jeff - Below is the code I used for INSERTs. Originally the join conditions (in the parens) were all set to AND. Once I switched to OR instead it worked. Now it only captures and updates where 1 or more fields of data are actually been changed. Any updates where the old and new values in every filed are equal don't get captured. I don't have access to the apps code but based on what I do know about programming for business apps I have a feeling it does this because the grid that is in a popup window (child window) that is used to list all the rows and columns for the data from this table is set such that if anything is changed the entire thing is registered as dirty and so the app updates every row instead of only the ones that had a change. It gets worse. I've seen where they will also delete every row and insert every row back with new values even when only 1 or a few rows actually had any changes. I guess it's easier to code that way, assume it's all dirty as opposed to just what was changed.

SELECT GETDATE(), 'U', CONVERT(VARCHAR(255),@@SPID), SYSTEM_USER, HOST_NAME(), ISNULL(APP_NAME(),'unknown'), DATABASE_PRINCIPAL_ID(), SESSION_USER, CURRENT_USER, 'WAMAUDIT SNAPSHOT', 
       I.hMy, I.hProp, I.sSource, I.dtInactive, I.bPortal, I.hPerson
       
FROM INSERTED I JOIN DELETED D ON I.hMy = D.hMy AND (    I.hProp <> D.hProp 
                                                      OR IsNull(I.sSource,'1') <> IsNull(D.sSource,'1') 
                                                      OR IsNull(I.dtInactive,'1900/01/01') <> IsNull(D.dtInactive,'1900/01/01')
                                                      OR IsNull(I.bPortal,-1) <> IsNull(D.bPortal,-1) 
                                                      OR isNUll(I.hPerson,-1) <> IsNull(D.hPerson,-1) 
                                                   )

Kindest Regards,

Just say No to Facebook!

Louis Hillebrand SSCarpal Tunnel Points: 4970 More actions · Answer 6

I used the NULLIF function to capture the change, this works for all values and is the same for all types

SELECT GETDATE(), 'U', CONVERT(VARCHAR(255),@@SPID), SYSTEM_USER, HOST_NAME(), ISNULL(APP_NAME(),'unknown'), DATABASE_PRINCIPAL_ID(), SESSION_USER, CURRENT_USER, 'WAMAUDIT SNAPSHOT', 
       I.hMy, I.hProp, I.sSource, I.dtInactive, I.bPortal, I.hPerson
       
FROM INSERTED I JOIN DELETED D ON I.hMy = D.hMy AND (    I.hProp <> D.hProp 
                                                      OR NULLIF(I.sSource,    D.sSource) IS NOT NULL 
                                                      OR NULLIF(I.dtInactive, D.dtInactive) IS NOT NULL
                                                      OR NULLIF(I.bPortal,    D.bPortal) IS NOT NULL 
                                                      OR NULLIf(I.hPerson,    D.hPerson) IS NOT NULL 
                                                   )

Kind regards.

YSLGuru SSC-Insane Points: 21632 More actions · Answer 7

Thanks for the tip

This reply was modified 1 year, 1 month ago by YSLGuru.

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1003874 More actions · Answer 8

Be careful, now... sometimes the change is from a value to a NULL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

YSLGuru SSC-Insane Points: 21632 More actions · Answer 9

Jeff - The trigger did capture the change of a non-null value to null when I removed the date form an item and dtInactive was set to NULL.

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1003874 More actions · Answer 10

YSLGuru wrote:

Jeff - The trigger did capture the change of a non-null value to null when I removed the date form an item and dtInactive was set to NULL.

Sorry. I should have been more clear... your trigger probably did... I don't believe that Louis' trigger will.

Of course, I've not tested either. I'm just eye-balling it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

YSLGuru SSC-Insane Points: 21632 More actions · Answer 11

Jeff - Gotcha!

Kindest Regards,

Just say No to Facebook!

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 12

It looks like you removed the original question - so I am not sure what that was, but this actually seems like a good candidate for using EXCEPT. It seems you want all rows from inserted where something has changed - comparing that to deleted:

   WITH updatedRows
     AS (
 SELECT i.hMy
      , i.hProp
      , i.sSource
      , i.dtInactive
      , i.bPortal
      , i.hPerson 
   FROM inserted           i
 EXCEPT
 SELECT d.hMy
      , d.hProp
      , d.sSource
      , d.dtInactive
      , d.bPortal
      , d.hPerson 
   FROM deleted            d
        )
 INSERT INTO {audit table} (audit column list)
 SELECT GETDATE()
      , 'U'
      , CONVERT(VARCHAR(255), @@SPID)
      , SYSTEM_USER
      , HOST_NAME()
      , ISNULL(APP_NAME(), 'unknown')
      , DATABASE_PRINCIPAL_ID()
      , SESSION_USER
      , CURRENT_USER
      , 'WAMAUDIT SNAPSHOT'
      , ur.hMy
      , ur.hProp
      , ur.sSource
      , ur.dtInactive
      , ur.bPortal
      , ur.hPerson
   FROM updatedRows        ur;

This would give you all rows in inserted where one or more columns in deleted have a different value. No need to explicitly check for changes to/from NULL.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

YSLGuru SSC-Insane Points: 21632 More actions · Answer 13

Jeff - the code in the original post was removed b/c in just a few mins after posting it I figured it out and I was going to delete the post but I don't know how to do that here if its even doable. The code I deleted is the exact same I sent in one of my replies to you.

if I get time to I'll have to check out your EXCEPT suggestion.

Thanks

Kindest Regards,

Just say No to Facebook!