Triggers are not only for DML. System triggers fire on events in the database or a schema: a user logging on or off, DDL such as CREATE and DROP, errors, startup and shutdown. A logon trigger can record who connects, or set up the session; a DDL trigger can keep a history of every structural change to a schema.
Code for This Guide
The main examples are in the examples/triggers folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create [or replace] trigger name
{after logon | before logoff | after servererror | {before | after} ddl | ...}
on {schema | database}
begin
...
end;ON SCHEMA fires for the trigger owner's own sessions or objects; ON DATABASE for everyone, and needs the ADMINISTER DATABASE TRIGGER privilege.
A Logon Trigger
The trigger records each logon to the NIMBUS schema with the user and program, then a new connection fires it. Replace &nimbus_password with the password of your NIMBUS user.
Example:
create or replace trigger nimbus_logon_trg
after logon on schema
begin
insert into logon_log values (localtimestamp, user, sys_context('USERENV', 'MODULE'));
end;
/
connect nimbus/"&nimbus_password"@localhost:1521/FREEPDB1
select username, program from logon_log;
drop trigger nimbus_logon_trg;Output:
Trigger NIMBUS_LOGON_TRG compiled USERNAME PROGRAM ___________ __________ NIMBUS SQLcl Trigger NIMBUS_LOGON_TRG dropped.
The log shows the new session of NIMBUS from SQLcl. The example drops the trigger, then the log table.
A DDL Trigger
An AFTER DDL ON SCHEMA trigger records every CREATE, ALTER, and DROP in the schema, using event attribute functions such as ORA_SYSEVENT and ORA_DICT_OBJ_NAME.
Example:
create or replace trigger schema_ddl_trg after ddl on schema begin insert into ddl_log values (localtimestamp, ora_sysevent, ora_dict_obj_type, ora_dict_obj_name); end; / create table temp_notes (n number); drop table temp_notes purge; select event, object_type, object_name from ddl_log order by logged_at; drop trigger schema_ddl_trg;
Output:
Trigger SCHEMA_DDL_TRG compiled Table TEMP_NOTES created. Table TEMP_NOTES dropped. EVENT OBJECT_TYPE OBJECT_NAME _________ ______________ ______________ CREATE TABLE TEMP_NOTES DROP TABLE TEMP_NOTES Trigger SCHEMA_DDL_TRG dropped.
Manage Triggers
Triggers can be disabled and enabled one by one, or all triggers of a table at once, and USER_TRIGGERS shows their type, event, and status. The two row triggers listed here, EMPLOYEES_SALARY_TRG and CUSTOMERS_NORMALIZE_TRG, are created by other examples in the same repository folder; the example drops them at the end.
Example:
alter trigger employees_salary_trg disable; alter table customers disable all triggers; select trigger_name, trigger_type, triggering_event, status from user_triggers order by trigger_name; alter table customers enable all triggers; drop trigger employees_salary_trg; drop trigger customers_normalize_trg;
Output:
Trigger EMPLOYEES_SALARY_TRG altered. Table CUSTOMERS altered. TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT STATUS __________________________ __________________ ___________________ ___________ CUSTOMERS_NORMALIZE_TRG BEFORE EACH ROW INSERT OR UPDATE DISABLED EMPLOYEES_SALARY_TRG AFTER EACH ROW UPDATE DISABLED Table CUSTOMERS altered. Trigger EMPLOYEES_SALARY_TRG dropped. Trigger CUSTOMERS_NORMALIZE_TRG dropped.
Things to Know
- An error in an AFTER LOGON ON DATABASE trigger can stop users from connecting; users with ADMINISTER DATABASE TRIGGER can still log on to fix it.
- Keep logon triggers fast: they run for every connection, including every connection a pool opens.
- Event attribute functions such as ORA_LOGIN_USER, ORA_DICT_OBJ_TYPE, and ORA_SQL_TXT describe what fired the trigger.
Related Guides
Conclusion
System triggers fire on logons, DDL, errors, and other database events. Use AFTER LOGON to record or prepare sessions and AFTER DDL to log schema changes, keep them fast and safe, and manage them with ALTER TRIGGER and USER_TRIGGERS.
