PL/SQL System triggers – Oracle PL/SQL Tutorial

In Oracle database, a trigger is a stored program that automatically executes in response to certain events, such as insertions, updates, and deletions, on a specified table or view. A system trigger is a special type of trigger that is created on the system level and executed whenever certain database-wide events occur, rather than on specific tables or views. In PL/SQL, a system trigger is defined using the CREATE TRIGGER statement with the BEFORE or AFTER keyword.

Here are some of the common system triggers in Oracle PL/SQL:

LOGON trigger: This trigger fires after a user has successfully logged on to the database. It is commonly used to perform actions such as setting session parameters, initializing global variables, and auditing user activity.

LOGOFF trigger: This trigger fires when a user disconnects from the database. It is used to perform cleanup actions such as releasing locks, closing cursors, and saving session state.

STARTUP trigger: This trigger fires when the database is started up. It is commonly used to perform initialization tasks such as loading data into memory, setting configuration parameters, and creating temporary tables.

SHUTDOWN trigger: This trigger fires when the database is shut down. It is used to perform cleanup tasks such as flushing buffers, closing files, and saving data to disk.

SERVERERROR trigger: This trigger fires whenever an error occurs on the server. It is used to capture error information and perform corrective actions such as rolling back transactions, sending alerts, and logging messages.

AFTER STARTUP trigger: This trigger fires after the database has started up and is available for use. It is used to perform post-startup tasks such as running maintenance scripts, sending notifications, and performing health checks.

AFTER SUSPEND trigger: This trigger fires after the database has been suspended, typically due to maintenance or backup activities. It is used to perform post-suspend tasks such as updating statistics, clearing caches, and restarting background processes.

Below are some examples of PL/SQL system triggers in Oracle database:

AFTER LOGON Trigger:

CREATE OR REPLACE TRIGGER after_logon_trigger
AFTER LOGON ON DATABASE
BEGIN
  DBMS_OUTPUT.PUT_LINE('User ' || USER || ' has logged on.');
END;

AFTER STARTUP Trigger:

CREATE OR REPLACE TRIGGER after_startup_trigger
AFTER STARTUP ON DATABASE
BEGIN
  DBMS_OUTPUT.PUT_LINE('Database has been started.');
END;

BEFORE SHUTDOWN Trigger:

CREATE OR REPLACE TRIGGER before_shutdown_trigger
BEFORE SHUTDOWN ON DATABASE
BEGIN
  DBMS_OUTPUT.PUT_LINE('Database is about to shut down.');
END;

AFTER SERVERERROR Trigger:

CREATE OR REPLACE TRIGGER after_servererror_trigger
AFTER SERVERERROR ON DATABASE
BEGIN
  DBMS_OUTPUT.PUT_LINE('An error has occurred.');
END;

AFTER CREATE Trigger:

CREATE OR REPLACE TRIGGER after_create_trigger
AFTER CREATE ON SCHEMA
BEGIN
  DBMS_OUTPUT.PUT_LINE('Object has been created.');
END;

In conclusion, PL/SQL system triggers in Oracle database allow you to automate various database-wide events and perform specific actions in response to those events. By using system triggers, you can enhance the functionality, reliability, and performance of your database and ensure that it runs smoothly and efficiently.

Learn PL/SQL