ALTER and DROP Trigger FAQS

1. What is ALTER TRIGGER used for?

ALTER TRIGGER is used to enable, disable, or compile an existing trigger.

Examples:

Disable a trigger

ALTER TRIGGER trigger_name DISABLE;

Enable a trigger

ALTER TRIGGER trigger_name ENABLE;

Compile a trigger

ALTER TRIGGER trigger_name COMPILE;

2. Can ALTER TRIGGER modify trigger code?

No.

·        You cannot change the logic with ALTER TRIGGER.

·        To modify code, use:

CREATE OR REPLACE TRIGGER trigger_name

...

This replaces the trigger while preserving its name and privileges.

3. What privileges are required for ALTER TRIGGER?

·        Schema-level trigger: ALTER privilege on the trigger or table

·        Database-level trigger: ADMINISTER DATABASE TRIGGER

·        To recompile triggers in another schema: DBA privileges

4. What is DROP TRIGGER used for?

DROP TRIGGER permanently removes a trigger from the database.

Syntax:

DROP TRIGGER trigger_name;

Notes:

·        Cannot recover the trigger unless you have the DDL saved

·        Dropping a table automatically drops its triggers

5. Can you drop database-level triggers?

Yes.

DROP TRIGGER logon_audit_trigger;

·        Requires ADMINISTER DATABASE TRIGGER privilege

6. What privileges are required to drop a trigger?

·        Own schema trigger: DROP TRIGGER

·        Another schema or database trigger: DBA privileges

7. Difference between ALTER TRIGGER DISABLE and DROP TRIGGER

Action	Effect
ALTER TRIGGER ... DISABLE	Temporarily disables the trigger; definition remains; can be re-enabled
DROP TRIGGER	Permanently removes the trigger; must recreate to use again

8. Can triggers be compiled with ALTER TRIGGER?

Yes. If a trigger becomes invalid due to schema changes, you can recompile it:

ALTER TRIGGER trigger_name COMPILE;

9. Best practices when using ALTER and DROP triggers

·        Keep DDL backups before dropping triggers

·        Disable triggers temporarily for bulk operations

·        Avoid dropping critical triggers in production

·        Recompile triggers after schema changes

·        Use proper privileges for database-level triggers