Arduous Activity Auditing

This would also be a perfect use case for a service interceptor if you are open to considering that option.

Alan

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

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) 

        Selectgetdate(), [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

Frominserted a

JOIN deleted b ON a.customer_no = b.customer_no and a.program_no = b.program_no

WhereCoalesce(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

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?

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!

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!

This would also be a perfect use case for a service interceptor if you are open to considering that option.

Alan

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

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) 

        Selectgetdate(), [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

Frominserted a

JOIN deleted b ON a.customer_no = b.customer_no and a.program_no = b.program_no

WhereCoalesce(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

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?

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!

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!