System Triggers FAQS

1. What is an Oracle system trigger?

An Oracle system trigger is a special type of trigger that is executed in response to certain events occurring at the database or system level (e.g., login/logout, database startup/shutdown, DDL operations). Unlike row-level triggers, system triggers affect the overall database environment and can perform actions like logging, auditing, or system resource management.

2. What are the different types of system triggers in Oracle?

The primary types of system triggers in Oracle are:

• BEFORE LOGON and AFTER LOGON: Triggered when a user logs into or out of the database.
• BEFORE LOGOFF and AFTER LOGOFF: Triggered when a user logs off or before the session ends.
• AFTER STARTUP and AFTER SHUTDOWN: Triggered after the database is started or shut down.
• AFTER DDL: Triggered after any DDL operation (e.g., CREATE, ALTER, DROP).
• AFTER COMMIT: Triggered after a transaction is committed.

 

3. How do I create a system trigger?

The syntax for creating a system trigger is:

CREATE OR REPLACE TRIGGER trigger_name

{BEFORE | AFTER} event

ON {DATABASE | SCHEMA | OBJECT}

[FOR EACH ROW]

BEGIN

   -- Trigger action code

END;

/

Example (AFTER LOGON trigger):

CREATE OR REPLACE TRIGGER after_logon_trigger

AFTER LOGON ON DATABASE

BEGIN

   DBMS_OUTPUT.PUT_LINE('User has logged in.');

END;

/

4. What are the common use cases for Oracle system triggers?

Some common use cases for system triggers include:

• Auditing: Tracking login/logout events and DDL operations.
• Resource Management: Performing actions during database startup or shutdown.
• Schema Change Monitoring: Logging or preventing unauthorized DDL operations.
• Notifications: Sending alerts or executing jobs when certain events occur.

 

5. Can system triggers be disabled or enabled?

Yes, system triggers can be enabled or disabled using the ALTER TRIGGER command.

ALTER TRIGGER trigger_name ENABLE;

ALTER TRIGGER trigger_name DISABLE;

However, certain triggers like AFTER STARTUP or AFTER SHUTDOWN are automatically triggered during database startup or shutdown, so they cannot be disabled in the traditional way.

6. What happens if an error occurs inside a system trigger?

If an error occurs during the execution of a system trigger, the operation that fired the trigger (e.g., login, DDL execution) may be rolled back depending on the error type and whether the error is handled within the trigger.

To prevent the operation from being rolled back, it's important to handle exceptions within the trigger code. For example:

CREATE OR REPLACE TRIGGER error_handling_trigger

AFTER LOGON ON DATABASE

BEGIN

   -- Example of exception handling

   BEGIN

      -- Some trigger action

   EXCEPTION

      WHEN OTHERS THEN

         -- Log the error but prevent rollback

         NULL;

   END;

END;

/

 

7. Can I execute DML (Data Manipulation Language) commands in a system trigger?

Yes, but with caution. System triggers can contain DML statements (e.g., INSERT, UPDATE, DELETE), but it's important to be aware of the potential for recursive triggering or performance overhead. For instance, if you create a system trigger that fires on a DDL event and that DDL operation involves DML statements, it could lead to unwanted recursive behavior.

 

8. Can system triggers be used for database security?

Yes, system triggers can be useful in database security for:

• Auditing: Capturing and logging user activity (e.g., logins, schema changes, privilege escalations).
• Preventing unauthorized DDL operations: You can create a system trigger to stop certain DDL operations or alert administrators.

For example, you can create a trigger to prevent dropping tables by monitoring AFTER DDL events.

 

9. What is the difference between system triggers and DML triggers?

• System triggers: They are fired by system-level events, such as user logins, database startup, or DDL operations (e.g., creating or dropping tables). They operate at the database level, not the individual row or table level.
• DML triggers: These are fired by DML operations like INSERT, UPDATE, or DELETE on a specific table. They operate at the row level, meaning they are executed when data in a specific table is modified.

 

10. How can I prevent users from executing specific DDL operations?

You can use system triggers like AFTER DDL to capture DDL operations and prevent certain actions. For example, you can raise an error if a user attempts to drop a table:

CREATE OR REPLACE TRIGGER prevent_drop_table

AFTER DDL ON DATABASE

BEGIN

   IF (ora_sysevent = 'DROP' AND ora_dict_obj_type = 'TABLE') THEN

      RAISE_APPLICATION_ERROR(-20001, 'Dropping tables is not allowed.');

   END IF;

END;

/

This will prevent users from dropping tables by raising an error when the DROP TABLE command is executed.

 

11. How can I capture schema changes (e.g., CREATE, ALTER, DROP) using system triggers?

You can use the AFTER DDL system trigger to capture any DDL operations. For example, to log all schema changes, you can create a trigger like this:

CREATE OR REPLACE TRIGGER log_ddl_changes

AFTER DDL ON DATABASE

DECLARE

   v_ddl_statement VARCHAR2(4000);

BEGIN

   v_ddl_statement := 'DDL Operation: ' || ora_sysevent || ' on ' || ora_dict_obj_name;

   INSERT INTO ddl_audit_log (operation_details) VALUES (v_ddl_statement);

END;

/

This will capture any DDL operation performed and log it into the ddl_audit_log table.

 

12. Are system triggers supported in Oracle Autonomous Database?

Yes, Oracle Autonomous Database supports system triggers. However, certain features might be limited or restricted due to the autonomous nature of the database, and you might not have full administrative control over all the system-level operations.

 

13. Can I use system triggers in Oracle RAC (Real Application Clusters)?

Yes, system triggers are supported in Oracle RAC. However, be cautious of issues like distributed transaction handling and network latency, which might affect trigger execution in a clustered environment.

14. How do I troubleshoot issues with system triggers?

• Check for errors: Use DBMS_OUTPUT or log errors into an audit table to help debug issues.
• Check trigger execution history: Look at the data affected by the trigger, especially if the trigger is executing DML or other operations.
• Monitor trigger performance: Large-scale operations or poorly designed triggers can degrade performance. Use the V$SESSION or DBA_HIST_SQLSTAT views to monitor performance impact.