Sign in Subscribe
Microsoft Dynamics GP

Find Custom SQL Triggers before Dynamics GP upgrade

Tim Wappat

• 3 min read

imageBe aware that any custom created SQL triggers in the Dynamics GP database that have been created outside of Dynamics GP will be dropped at time of upgrade from one version of GP to another. Custom triggers are a technique SQL Server Admins might use to, for example default a field in GP, without using a GP application modification. Introducing triggers in GP can cause application malfunctions, so please read up on the subject before attempting such an action.

Custom eConnect stored procedures also suffer this fate so also grab back ups for those before any upgrade

Pre-Upgrade

  • Identify the custom triggers for each database
  • Script out the custom triggers using SQL Server Management Studio

Post-Upgrade

  • Run the create script from the pre-upgrade step to recreate SQL triggers in the database.

In an ideal world, your documentation will have details of these triggers, and the trigger create scripts are in your server build scripts, locked away safely in source control –right?

For those of us in the real world, the script below can be used to group the create dates of triggers in a database together, look for where the rank changes on the far right column. As the majority of triggers are created together when products are installed, the ad-hoc created triggers stand out as being in their own ranking groups.

 -- Script will list all triggers in the current database
-- Custom SQL triggers are removed at upgrade by the upgrade 
-- Script triggers out to recreate them post upgrade
-- The rank column can be used to find where triggers have been manually addded
-- those triggers will usually be groups together by that ranking
-- Remember to run for each company and the DYANMICS database too 
SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled],
    sysobjects.crdate as [Trigger Created],
    RANK() OVER (  order by DATEADD(dd, 0, DATEDIFF(dd, 0, sysobjects.crdate)) desc) [Rank of Trigger Created Date]
FROM sysobjects 

INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR' 
order by sysobjects.crdate desc

Result

image

How to script the triggers out

Once they have been identified them, use the “generate scripts” by a right click of the database node in SQL Server Management Studio object explorer, to script all triggers out. It is then possible to search through the generated script for the term “CREATE TRIGGER” and extract the triggers of interest into another TSQL file to run after the upgrade.

image

To do this, script out all the tables

image

On the options before you finish the script wizard, in the tables section, select to allow scripting of the triggers.

image