Identifying Triggers registered against a Table

David Meego - Click for blog homepageLast week, I wrote about a situation which was causing issues with the Database Validation feature of GP Power Tools. Triggers on the ACTIVITY table that fired when records are deleted that attempt to clean up data in the company that the record is related to.

The problem is that if the current user does not have access to this company or company database does not exist or is not online, errors are generated and the code fails. This is discussed in my blog:

To fix the problem, my first version of the solution just disabled the syVendorAnalysisDeleteTrigger trigger beforehand and enabled it afterwards.

Then came the news that there was another trigger installed when the Manufacturing module was installed. So if I kept up this approach, I would have to check if Manufacturing was installed and then disable and enable this extra trigger.

But what would happen if another Microsoft product installed a another trigger, or a third party add-on from another ISV (Independent Software Vendor installed a trigger, or a consultant or the customer’s DBA (Database Administrator) installed a customer trigger.

Adjusting my code to handle individual triggers was not going to be a permanent solution to this issue. There had to be a better way….

After a little research, I came up with the script below which lists all the currently active triggers for a specified table in a SQL database:

select TRIG.name as Trigger_Name, TAB.name as Table_Name, TRIG.is_disabled
from [sys].[triggers] as TRIG
inner join sys.tables as TAB on TRIG.parent_id = TAB.object_id
where TAB.name = 'ACTIVITY' and TRIG.is_disabled = 0

With a little bit of Dexterity code to execute this query and interpret the resulting data set, I generated the pass through SQL commands needed to disable and then enable the triggers.

[Edit] In fact, I used the GP Power Tools SQL Execute Setup window and used the new option (for Build 22) to Generate Pass Through Dexterity Code, which I then pasted into my development dictionary and adjusted as needed. Using GPPT to develop GPPT… oooh codeception! 🙂

This method works perfectly and is future proof as additional triggers will be picked up by the code. It also only disables the triggers that are currently active and then enables those same triggers. A trigger on the table that is already disabled, is ignored and will remain disabled.

I hope this script and the method described here help you resolve similar issues in your code.

David

PS: A new build of GP Power Tools with this issue fixed is available for download.

This article was originally posted on http://www.winthropdc.com/blog.

2 thoughts on “Identifying Triggers registered against a Table

  1. Hi David, we have a client that has been experiencing FP close errors, accessing SQL data errors, Etc since GP 2013R2, they do have a third party app, MCVisions Advanced intercompany. After working with Microsoft support and making changes to anti-virus and temp folder rights the messages are getting fewer but still happen. Would theses changes to triggers work for a remove range operation on table ‘syAreaPageSections’ failed to accessing SQL data?

    Like

    • Hi Connie

      Using GP Power Tools Resource Information window to lookup the syAreaPageSections table, I confirmed that it is a SQL table. This means that the issue is not going to be related to the local workstation temp folder and antivirus software.

      If you can replicate the issue we need to capture a SQL Profile trace to get more details about why the remove range failed. It could be a badly written custom trigger on the SY07140 table.

      You can use the code on this window or look at the triggers on the tree in SQL Management Studio.

      Regards

      David

      Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.