Trigger

Earlier this week I had to disable many triggers just so that we can Restore Databases and Create Databases

The triggers were set to send out alerts on DDL events but 2 issues

1. The DBA db didn't exist on that server but the trigger is relying on that db, so I found the triggers using the below scripts, and disabled them (or a simple SELECT * FROM sys.triggers would work too)

SELECT
             sysobjects.name AS [Trigger Name],
             syscomments.TEXT AS [Trigger Definition],
             --SUBSTRING(syscomments.text, 0, 26) AS [Trigger Definition],
             OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],
             syscomments.encrypted AS [IsEncrpted]
FROM
             sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
WHERE
             (sysobjects.xtype = 'TR')
ORDER BY [Table Name]


2. SharePoint creating database name so long that it broke the XML column used in the DDL trigger for Creating Database, so I disabled the DDL Trigger by

1. Find the trigger name (KEY!)
Server -> Server Objects -> Triggers -> trigger_name

2. DISABLE TRIGGER trigger_name