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,
A trigger is a logical technical solution to this; however, Brian is correct - Tessitura does not support the creation of custom triggers on their tables - for good reasons. Alan's idea of a service interceptor is a good one; however, that does require some .NET development skills (as well as time to figure it out).
I'll give you one option that is somewhat lame, but can work. You could create a copy of TX_CUST_KEYWORD table into something like, LTX_AUDIT_CUST_KEYWORD. Then, build a stored procedure scheduled to run nightly that does the following:
(1) Compare TX_CUST_KEYWORD and LTX_AUDIT_CUST_KEYWORD for differences - specifically, changes in existing records, new additions in TX_CUST_KEYWORD, and deletions. Output those differences to TA_AUDIT_TRAIL.
(2) Truncate (or drop) and rebuild LTX_AUDIT_CUST_KEYWORD with a fresh copy of TX_CUST_KEYWORD, which would be used when the procedure runs the next night.
There is a risk of losing some changes that occur between steps #1 and #2. If that is a significant risk in your environment, you could create a second copy prior to step #1 and use that as your baseline for the next audit job.
If you wanted to keep the size down, just build the primary key and columns you want to monitor in LTX_AUDIT_CUST_KEYWORD and leave out the rest. You could also store these table copies in a separate database if you wanted to make sure it didn't impact the size and transaction logs of the impresario db.
This is not the most ideal method, but it is fairly easy and has zero impact to users as they use Tessitura. The main downside is the time it would take to run at night and some additional database growth (as well as activity in the transaction logs) as a result of keeping copies of TX_CUST_KEYWORD.
Thanks,David