Deleting and Disabling Triggers
When a trigger is no longer needed, you can disable it or delete it.
Disabling a trigger does not drop it. The trigger still exists as an object in the current database. However, the trigger will not fire when any INSERT, UPDATE, or DELETE statement on which it was programmed is executed. Triggers that are disabled can be reenabled. Enabling a trigger causes it to fire in the same manner as when it was originally created. Triggers are enabled by default when they are created.
When a trigger is deleted, it is dropped from the current database. The table and the data upon which it is based are not affected. Deleting a table automatically deletes any triggers on the table. Permissions to delete a trigger default to the owner of the table upon which the trigger is defined.
To disable a trigger
To enable a trigger
To delete a trigger