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
This would also be a perfect use case for a service interceptor if you are open to considering that option.
Alan
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Brian Wilbur Grundstrom Sent: Thursday, April 25, 2013 2:40 PM To: Levine, Alan Subject: RE: [Tessitura Technical Forum] Arduous Activity Auditing
I believe that we are only supposed to edit triggers on our own local tables…
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Simon Basyuk Sent: Tuesday, April 23, 2013 9:03 AM To: Brian W. Grundstrom Subject: Re: [Tessitura Technical Forum] Arduous Activity Auditing
insert[dbo].TA_AUDIT_TRAIL (date, userid, location, action, table_name,
Selectgetdate(), [dbo].fs_user(), [dbo].fs_location(), 'Updated', 'TX_CUST_PROGRAM',
Frominserted a
WhereCoalesce(a.cust_pname, '') <> Coalesce(b.cust_pname, '')
From: Matt Gonzales <bounce-mattgonzales9808@tessituranetwork.com> Sent: 4/12/2013 9:53:52 AM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
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