Query help to track daily updates made to table(for specific column)

Question

I have 2 tables Individual(IndividualId is primary key) and IndividualAudit. Every time update is made on individual table record goes to audit table. There are many columns that can be modified but i am interested only in picking up records where SSN is modified. I m using below query:

Select DI.IndividualId,DI.ssn FRom Individual I
INNER JOIN IndividualAudit  A
ON(I.IndividualId = A.IndividualId and A.UpdateDate = GETDATE())
where i.updatedate = GETDATE() and I.ssn <> a.ssn
group by I.IndividualId,I.ssn

Can someone please tell me whether my approach is correct.

Actually i was searching on google and got scared looking at below link: Query help when using audit table

the person who answered similar query on this post seem to be very good with sql and comparing with his answer my approach looks quite naive. so i just want to know where am i wrong in my understanding.

Thanks a lot

Assuming `GETDATE` returns the current time, it looks like this would only get rows that were being modified right now (ie, probably none). Also, if the row was updated, why would the `SSN` differ? I'm not sure what data is going into the audit table - is it the previous value of the row or the current (as updated) value? — Mikeb, Jul 19 '13 at 13:57
Hi Mike...for getdate() i will be using CONVERT(VARCHAR(10),mydatecol,110) to compare against dates only. Previous value of row goes to audit table. (there is no necessity ssn should differ. only if ssn has differed, i want to pick the row that's why <> check) — user2159471, Jul 19 '13 at 14:07

score 0 · Answer 1 · answered Jul 19 '13 at 14:39

Rather than fixing the query, I'd suggest instead using an update trigger aimed specifically at changes to that SSN column you're concerned about. The query you've supplied won't work because of the date comparison (as user2159471 has pointed out). But even after you get the query fixed, you'll still have to run it in order to see which SSNs have been updated.

Instead use a SQL update trigger that, perhaps, inserts an entry into a third table each time an individual's SSN get changed. Then you can look at that table any time you, or run a report against it, to see who's been changed.

The trigger code looks like this:

CREATE TRIGGER MyCoolNewTrigger ON Individual 
FOR UPDATE
AS
SET NOCOUNT ON
IF (UPDATE(SSN))
BEGIN
   Declare @oldSSN as varchar(40)
   Declare @NewSSN as varchar(40)
   set @oldSSN = deleted.SSN --holds the old SSN being changes
   Set @NewSSN = inserted.SSN -- holds the new SSN inserted
   Insert into IndividualUpdateLog (NewSSN, OldSSN, ChangeDate)
   values (@NewSSN, @oldSSN,  getdate)    
END

Query help to track daily updates made to table(for specific column)

1 Answers1