In Oracle, system triggers are special types of triggers that automatically execute in response to certain events related to the database's operation. These events could be things like logging in to the database, schema changes, DDL operations, or system-level activities. System triggers are typically associated with actions that affect the database at a global or system-wide level rather than individual tables or rows.
Here is a detailed look at Oracle system triggers, their types, and examples:
1. Types of System Triggers in Oracle
Oracle provides several system-level trigger events. Below are the common ones:
a. BEFORE LOGON / AFTER LOGON
These triggers execute when a user logs into or logs out of the database.
- BEFORE LOGON: Fired before the user is authenticated and granted access to the database.
- AFTER LOGON: Fired after the user has successfully logged in and been authenticated.
Usage Example:
CREATE OR REPLACE TRIGGER after_logon_trigger
AFTER LOGON ON DATABASE
BEGIN
-- Action when a user logs into the database
DBMS_OUTPUT.PUT_LINE('User has logged in');
END;
/
b. BEFORE LOGOFF / AFTER LOGOFF
These triggers fire before or after a user logs off from the database.
- BEFORE LOGOFF: Fired just before a user session is ended.
- AFTER LOGOFF: Fired after a user has logged off.
Usage Example:
CREATE OR REPLACE TRIGGER before_logoff_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- Action before a user logs off
DBMS_OUTPUT.PUT_LINE('User is logging off');
END;
/
c. AFTER STARTUP / AFTER SHUTDOWN
These triggers are fired when the database is started up or shut down.
- AFTER STARTUP: Fired after the database has been started.
- AFTER SHUTDOWN: Fired after the database has been shut down.
Usage Example:
CREATE OR REPLACE TRIGGER after_startup_trigger
AFTER STARTUP ON DATABASE
BEGIN
-- Action after the database has started up
DBMS_OUTPUT.PUT_LINE('Database has started');
END;
/
d. AFTER DDL (Data Definition Language)
These triggers execute after a DDL operation is performed, such as creating or dropping a table, altering a schema, or changing database objects.
- AFTER DDL: Used for system-level tracking of schema modifications.
- BEFORE DDL: Executes before a DDL operation happens.
Usage Example:
CREATE OR REPLACE TRIGGER after_ddl_trigger
AFTER DDL ON DATABASE
BEGIN
-- Action after any DDL command is executed
DBMS_OUTPUT.PUT_LINE('A DDL operation was performed');
END;
/
e. AFTER COMMIT
This trigger fires after a COMMIT operation is executed in a transaction.
Usage Example:
CREATE OR REPLACE TRIGGER after_commit_trigger
AFTER COMMIT ON DATABASE
BEGIN
-- Action after a commit is performed
DBMS_OUTPUT.PUT_LINE('Transaction has been committed');
END;
/
2. Trigger Timing: BEFORE vs. AFTER
- BEFORE triggers are fired before the specified event occurs.
- AFTER triggers are fired after the specified event occurs.
For example:
- BEFORE LOGON: Executes before the user logs in.
- AFTER LOGON: Executes after the user logs in.
3. Scope of System Triggers
System triggers in Oracle are designed to be used in the entire database context, not specific to any individual table. They are invoked by global events that affect the state of the database or sessions, rather than by changes to individual rows or columns in tables.
4. Common Use Cases for System Triggers
Some common scenarios where you might use Oracle system triggers include:
- Auditing User Logins/Logoffs: Tracking user activity by capturing when users log in and out of the system.
- Resource Management: Automatically managing session-level resources or actions after database startup/shutdown.
- Schema Change Alerts: Monitoring DDL operations to log or prevent unauthorized schema changes.
- Notification and Logging: Sending notifications or generating logs when certain events happen at the system level.
5. Enabling and Disabling Triggers
System triggers can be enabled or disabled using the ALTER TRIGGER command. However, note that some triggers, like AFTER STARTUP, automatically execute when their conditions are met and can't be disabled in the traditional sense.
Example:
ALTER TRIGGER after_logon_trigger DISABLE;
ALTER TRIGGER after_logon_trigger ENABLE;
6. System Trigger Syntax
The basic syntax for creating a system trigger is as follows:
CREATE OR REPLACE TRIGGER trigger_name
{BEFORE | AFTER} event
ON {DATABASE | SCHEMA | OBJECT}
[FOR EACH ROW]
BEGIN
-- Trigger action code
END;
/
For example:
CREATE OR REPLACE TRIGGER after_startup_trigger
AFTER STARTUP ON DATABASE
BEGIN
DBMS_OUTPUT.PUT_LINE('Database has started successfully');
END;
/
7. Example of Using AFTER DDL Trigger
Here’s an example of how you might use the AFTER DDL trigger to log DDL activities:
CREATE OR REPLACE TRIGGER log_ddl_operations
AFTER DDL ON DATABASE
DECLARE
v_ddl_statement VARCHAR2(4000);
BEGIN
v_ddl_statement := 'DDL Operation Performed';
INSERT INTO ddl_audit_log (operation_details) VALUES (v_ddl_statement);
END;
/
This trigger logs any DDL operations into an audit table (ddl_audit_log).
8. Considerations for System Triggers
- Performance Impact: Triggers add overhead to database operations. Use them wisely, particularly for system-wide events, as it could degrade performance if not managed properly.
- Privileges: Certain system triggers (e.g., AFTER DDL) require specific privileges to create. Ensure that the user creating the trigger has the necessary system privileges.
- Triggers on DDL: While system triggers like AFTER DDL are helpful for auditing, they may not capture all types of DDL events like those performed by the Oracle administrator (DBA). Custom solutions might be needed for comprehensive auditing.
No comments:
Post a Comment