Topic
  • 5 replies
  • Latest Post - ‏2013-03-06T22:08:44Z by SystemAdmin
SystemAdmin
SystemAdmin
344 Posts

Pinned topic Any issues with removing all the history table triggers?

‏2013-03-04T19:14:06Z | mdm-migration
Hello,

Question on the usage of the history table. I know for every table created in MDM, the convention is to create the associated history tables and then apply the triggers for it ( create, update, delete ). However, my client has not used this history table for over 3 years and does not seem to have the need for it. More importantly, for performance reasons, this just seems additional overhead on unnecessary processing. Can I remove all the triggers to improve performance? any issues with this?

your help/suggestion is greatly appreciated.

David.
  • SystemAdmin
    SystemAdmin
    344 Posts

    Re: Any issues with removing all the history table triggers?

    ‏2013-03-04T19:15:22Z  
    I forgot to mention that we have a large number of custom tables. If not the OTB tables, then possibly just to eliminate the triggers for our custom tables...
  • SystemAdmin
    SystemAdmin
    344 Posts

    Re: Any issues with removing all the history table triggers?

    ‏2013-03-05T17:00:32Z  
    I forgot to mention that we have a large number of custom tables. If not the OTB tables, then possibly just to eliminate the triggers for our custom tables...
    These triggers are meant to be configurable (ie. enable or disable). we have some customers running with them disabled, and some with them enabled.

    So in general, if you don't need them, they should be dropped. It's not only good for performance (of add/update transactions) but also save disk space.
  • SystemAdmin
    SystemAdmin
    344 Posts

    Re: Any issues with removing all the history table triggers?

    ‏2013-03-05T20:18:49Z  
    when we drop the triggers - is there a process to do this within MDM ( available script? ) or is it just manual drop trigger command. I am OK with the custom tables...how about all the base OTB triggers...do other clients drop those triggers as well? Just thinking ahead to see if there will be any issues with upgrades. We are currently running MDM8.5 and will be upgrading to v.10 soon.
  • SystemAdmin
    SystemAdmin
    344 Posts

    Re: Any issues with removing all the history table triggers?

    ‏2013-03-06T03:42:37Z  
    when we drop the triggers - is there a process to do this within MDM ( available script? ) or is it just manual drop trigger command. I am OK with the custom tables...how about all the base OTB triggers...do other clients drop those triggers as well? Just thinking ahead to see if there will be any issues with upgrades. We are currently running MDM8.5 and will be upgrading to v.10 soon.
    Yes, they can be dropped by using the database scripts. I am not aware of anything else needs to be done on the application side.

    I believe there will be implications if later the customer needs to turn on history. That is, once it's off, keeping it off. However, I don't have enough experience with this scenario and I hope others with more experience with history data requirements in the field can chime in. Similarly, when migrate, just need to make sure those triggers are also dropped (manually or as part of the migration script).
  • SystemAdmin
    SystemAdmin
    344 Posts

    Re: Any issues with removing all the history table triggers?

    ‏2013-03-06T22:08:44Z  
    Yes, they can be dropped by using the database scripts. I am not aware of anything else needs to be done on the application side.

    I believe there will be implications if later the customer needs to turn on history. That is, once it's off, keeping it off. However, I don't have enough experience with this scenario and I hope others with more experience with history data requirements in the field can chime in. Similarly, when migrate, just need to make sure those triggers are also dropped (manually or as part of the migration script).
    History is important and is important part of Entity's update life-cycle.

    Plan properly in your migration project that if you are sure to avoid history?
    if answer is yes, in that case just go ahead and drop all the triggers. Delete all the data which will be present in existing history tables i.e. (All the H_ tables).

    If your migration approach is to upgrade the existing database, remove all alter script for history tables from your scripts inventory... communicate to development and DBA teams to never execute the trigger for MDM extensions/additions/customization, unless you change your plan and want to reuse triggers.