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.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s