Sql-server – SQL Server 2000 : List and Disable all triggers

sql-server-2000

I have to do a database cleanup for one of our databases on a sql server 2000 environment. The cleanup basically includes the disabling of all triggers, deletion of all old data, enabling of the triggers and the Shrinking of the Transaction Log.

Currently the biggest problem I have is the disabling of the Triggers. This would have been super easy in sql 2005 / 2008 as they included a 'Disable Triggers All' command. I have a query to list all of the Triggers although it doesn't list the status of Trigger (this would be a bonus)

SELECT S2.[name] TableName, S1.[name] TriggerName, CASE WHEN S2.deltrig = s1.id THEN 'Delete' WHEN S2.instrig = s1.id THEN 'Insert' WHEN S2.updtrig = s1.id THEN 'Update' END 'TriggerType' , 'S1',s1.*,'S2',s2.* FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR'

Source

The deleting of the records will be plain delete's one for each table, this probably isnt the most efficient way to do it but its the way which was prescribed.

Shrinking the Transaction Log is also sorted, i found an article on using the DBCC on Microsoft support site

Any advise would be really appreciated

Best Answer

SQL Server 2000 has ALTER TABLE foo ENABLE TRIGGER ALL too

And to generate the required ALTER TABLE script use this

SELECT
    DISTINCT 'ALTER TABLE ' + OBJECT_NAME(parent_obj) + ' /*DISABLE*/ ENABLE TRIGGER ALL'
FROM
    sysobjects
WHERE
    xtype = 'TR'

I wouldn't bother testing status of the triggers, personally. A disabled trigger should not exist IMHO. Not least because OBJECTPROPERTY does not support it in SQL Server 2000

Edit:

My mistake, OBJECTPROPERTY in SQL Server 2000 has ExecIsTriggerDisabled as per comment. So you can track status before and after

Related Question