How to enable and disable a trigger in SQL Server

Nightly bulk load into a table carrying an audit trigger takes four hours, and the trigger firing per row is the suspect. I want it off during the load and back on after.

What is the syntax and what should the job script guard against?

Right suspect, common pattern. The syntax is one line each way with two guards that separate a solid job from a future incident.

1

Disable before the load: DISABLE TRIGGER audit_trg ON dbo.BigTable. The variant DISABLE TRIGGER ALL ON dbo.BigTable silences every trigger on the table when several exist. Database level DDL triggers use ON DATABASE instead of a table name.

2

Run the load. Rows now arrive without the per row audit cost, which for four hour loads routinely means finishing in a fraction of the time.

3

Re-enable: ENABLE TRIGGER audit_trg ON dbo.BigTable. Put this in the job's cleanup step that runs even on failure, a TRY CATCH with the enable in the CATCH as well as the success path, because a load that dies at 3am leaving the trigger off means every business hour transaction the next day escapes the audit silently.

4

Verify state rather than assuming it: SELECT name, is_disabled FROM sys.triggers WHERE parent_id = OBJECT_ID('dbo.BigTable'), worth running at the job's end and honestly worth a morning check while the pattern is new.

The audit gap during the load is a real design decision rather than a detail: rows inserted while the trigger sleeps have no audit rows. If the audit matters for loaded data too, have the load itself write the audit entries in bulk afterwards, one INSERT from the loaded set into the audit table costs almost nothing compared to per row firing.

Load dropped from four hours to 40 minutes with the trigger off. The CATCH block enable saved us in week two exactly as predicted, the load failed and the trigger still came back on. Bulk audit backfill added as the final step.