Find Custom SQL Triggers before Dynamics GP upgrade
Be 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
- Identify the custom triggers for each database
- Script out the custom triggers using SQL Server Management Studio
- 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
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]
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
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.
To do this, script out all the tables
On the options before you finish the script wizard, in the tables section, select to allow scripting of the triggers.