April 11, 2018 at 4:53 pm
I am trying to find differences between a normal table and an audit table.
Iam_audit:
A_n L_name A_status
1 abc AL
2 def AC
Iam:
| a_n | audit_field | field_after | |
| 1 | L_name | abd | |
| 1 | a_status | AL | |
| 2 | L_name | def | |
| 2 | a_status | AD |
Expected:
| a_n | field_after | audit_field | a_n | name |
| 1 | abc | l_name | 1 | abd |
| a_n | field_after | audit_field | a_n | name |
| 2 | AC | a_status | 2 | AD |
Current:
| a_n | field_after | audit_field | a_n | name |
| 1 | abc | l_name | 1 | l_name |
| 2 | def | l_name | 1 | l_name |
| a_n | field_after | audit_field | a_n | name |
| 1 | AL | a_status | 1 | a_status |
| 2 | AC | a_status | 2 | a_status |
The problem is in the table iam_audit it is a column name and in table Iam it is a field itself.I have lot more fields to compare thatsy using cursor. Any solution?
declare @af varchar(500)
set @af = 'L_Name,A_Status’
declare @pa varchar(50)
declare audit_cur cursor for
select ltrim(rtrim(Parameter))'Audit_FLD'
from phngeneral.dbo.fcn_TheDelimiterEliminator(@af,',')
OPEN audit_cur
FETCH NEXT FROM audit_cur into @pa
WHILE @@FETCH_STATUS = 0
BEGIN
select * from (Select a.A_n, a.field_after,audit_field
from Db.iam_audit a
where a.Audit_Field =@af) i
left join (select distinct a_n,@pa as name
from Db.iam
) il on i.a_n=il.a_n
where i.audittable_field_after<>il.name
order by 1 desc
FETCH NEXT FROM audit_cur into @pa
END
CLOSE audit_cur
DEALLOCATE audit_cur
April 12, 2018 at 6:38 am
Sounds like a rather painful audit methodology. However, your listed scenario seems considerably over-simplified. Usually, an audit table record contains a datetime value indicating when the change occurred, and I can't imagine that there haven't been situations where someone makes a change to a record, only to realize they changed either the wrong record or they changed something they weren't supposed to change, and thus they soon after change it back to what it was. If you didn't have a datetime value sitting there, you'd have no viable way to do what you appear to want to do, which appears to be flatten out the audit table. That's seriously fraught with issues, and without both sample data that includes a datetime value along with just that kind of scenario, and a realistic number of columns that are subject to audit rather than just the two in your sample code, I'm doubtful that anything coded to handle your example could handle the real-world scenario you're dealing with. I'm looking for more detail before I suggest an approach, as audit can be particularly trickier with every possible additional wrinkle.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 12, 2018 at 11:31 am
I've done similar tasks. For me the path to success involves making the regular table look like the audit table, including sticking in the field name from the source regular table into a temporary table as a column value. Then once the fields and values are converted to that temporary table / work table, then you are comparing apples to apples!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply