• Replies 2 replies
  • Subscribers 337 subscribers
  • Views 40 views
  • Users 0 members are here
Related

Help with Triggers

Ryan Rowell
$organization

Hi all,

I'm working on a custom tab that I want to have a full audit trail for and
to do this I've mimicked the layout of the triggers used for the t_customer table.
The thing is, I've got 36 check boxes I would like to track as well.
Is there a better way of updating an audit table than just making 36 separate bits of code
to do this? If not should I be concerned with something like this taxing the system?

Here is an example of one or the 36.

if update (m1)
    insert    [dbo].lt_contact_ind_audit(date, userid, location, action,
        customer_no, column_updated, new_value)
        Select    getdate(), user_name(), host_name(), 'Updated', 
        a.customer_no, 'm1', c.m1
    From inserted a
        JOIN deleted b ON a.customer_no = b.customer_no
        JOIN lt_contact_ind c ON a.customer_no = c.customer_no

 

Thanks for any help/advice!

 

  • $organization

    You could do something like this... but it would take a little work.

     

    I am kind of speculating here, since I have never done what I am going to discuss in Tessitura, although I have written code dealing with similar concepts in Access.

    I will go ahead and assume you are storing data as a char(1), like Tessitura does in other places for check boxes.

    You could set up one column in your custom table  that stores the values of all your check boxes in one column (like t_address.mail_purposes) When you retrieve you could parse out the data based on the position of the item in the string. When you check a box it would then update a specific character in the string. 

    The downside of that would be that the information stuck in the audit trail would be kind of hard to interpret by a regular user. I guess in the update trigger you could check to see specifically what character(s) changed, and insert them separately to your audit trail in the same way you would parse them out on your custom form.

    I hope this helps out some. Good luck.

    Matt

     

     

  • $organization in reply to Matthew Hoyt

    Thanks Matt, that sounds like something worth looking into.

    Cheers!