Is there any reliable way already in place that would keep a historical record of attributes from TX_CUST_KEYWORD. I've checked TA_AUDIT_TRAIL and it doesn't seem to track the insertion and deletion of rows from that table.
Basically, we wanted a way to track how long a given constituent has been in a certain stage of sponsorship (e.g. When someone changes from Portfolio Owner: "Bob Johnson - Prospect" to Portfolio Owner: "Bob Johnson - Renewal") and we thought that using the create_dt within TX_CUST_KEYWORD was logical, but not in the case where the Portfolio Owner would change hands, giving us some difficulty in tracking historical activity for this particular interest.
If TA_AUDIT_TRAIL doesn't track that, is there an easy way to alter the procedure that inserts such rows into TA_AUDIT_TRAIL to include the creation and deletion of such rows from TX_CUST_KEYWORD, thereby giving us a method of tracking this activity in a special report?
Or is that not even a good way to track this?
Hello? *tap, tap* Is this thing on?
Hi Matt,
It can be done exactly the way other entries in table TA_AUDIT_TRAIL get created: through "on update" trigger of a corresponding table. See, for instance how it was done in trigger TX_CUST_PROGRAM_TU of the table TX_CUST_PROGRAM:...
IF update(cust_pname)
Begin
insert [dbo].TA_AUDIT_TRAIL (date, userid, location, action, table_name,
customer_no, column_updated, old_value, new_value,
alternate_key, cg_key)
Select getdate(), [dbo].fs_user(), [dbo].fs_location(), 'Updated', 'TX_CUST_PROGRAM',
a.customer_no, 'cust_pname', b.cust_pname, a.cust_pname,
convert(varchar,a.program_no), a.program_no
From inserted a
JOIN deleted b ON a.customer_no = b.customer_no and a.program_no = b.program_no
Where Coalesce(a.cust_pname, '') <> Coalesce(b.cust_pname, '')
End
So, you can modify the existing trigger TX_CUST_KEYWORD_TU of the table TX_CUST_KEYWORD (I would not do that because it is Tessitura's code) or build your own, additionally to that one. If you wish I can help you with that.
Simon Basyuk, DBA at Carnegie Hall
sbasyuk@carnegiehall.org