March 17, 2023 at 6:02 am
Cool! What's the answer?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2023 at 6:10 am
Probably something with
If exists(select * from inserted)
N 56°04'39.16"
E 12°55'05.25"
March 17, 2023 at 6:18 am
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!March 17, 2023 at 2:47 pm
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
Change is inevitable... Change for the better is not.
March 17, 2023 at 4:55 pm
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!March 17, 2023 at 5:06 pm
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.
March 17, 2023 at 5:46 pm
Be careful, now... sometimes the change is from a value to a NULL.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2023 at 5:56 pm
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!March 17, 2023 at 7:11 pm
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
Change is inevitable... Change for the better is not.
March 17, 2023 at 9:07 pm
Jeff - Gotcha!
Kindest Regards,
Just say No to Facebook!March 17, 2023 at 9:46 pm
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
March 17, 2023 at 11:34 pm
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!Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply