Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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

share|improve this answer
    
Thank you for the quick response, I tested the script and it works perfectly. The triggers will only be disabled until the cleanup has been completed where after they will be re-enabled. –  fluf Feb 1 '11 at 12:23
    
@fluf If you're going to re-enable to triggers, you'll need to keep track of which one's were disabled before you scripted them out. see: OBJECTPROPERTY (id, ExecIsTriggerDisabled) –  Andrew Bickerton Feb 1 '11 at 12:39
    
Though I also agree with gbn's response of "A disabled trigger should not exist IMHO" –  Andrew Bickerton Feb 1 '11 at 12:41
1  
@Andrew Bickerton: I'd rather suppress a trigger with SET CONTEXT_INFO or such, so it's controlled per action rather than universally. Day to day (outside of special mainteance) you shoulnd't have a disabled trigger. It's redundant code. –  gbn Feb 1 '11 at 13:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.