René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Create trigger in Oracle

create trigger trigger-name before event
create trigger trigger-name after event
create trigger trigger-name instead of event

Event

The code associated with a trigger is fired when a specified event occurs. The events can either be a DML event, a DDL event or a database event. (DDL event and database event triggers are also called system triggers)

DML event

delete on table-name
insert on table-name
update on table-name
update of column-name on table-name
update of column-name-1, column-name-2 on ...

delete or insert on table-name
delete or update on table-name
....

DDL event

ddl-event on schema
ddl-event or ddl-event on schema
ddl-event or ddl-event or ddl-event ... on schema

ddl-event on database schema
ddl-event or ddl-event on database
ddl-event or ddl-event or ddl-event ... on database

Database event

database-event on schema
database-event or database-event on schema
database-event or database-event or database-event on schema ...

database-event on database
database-event or database-event on database
database-event or database-event or database-event on database ...
The following database events can be caught:

Prerequisites

dbmsstdx.sql must have been called. Normally, catalog.sql is run after the creation of a database. Catalog.sql in turn calls dbmsstdx.sql.

before insert or update

create or replace trigger <TRIGGER_NAME>
  before insert or update
on <table_name>
  for each row
declare
  <VARIABLE DECLARATIONS>
begin
    <CODE>
exception
    <EXCEPTION HANDLERS>
end <TRIGGER_NAME>;
/

After Logon on database

create table logon_tbl (who varchar2(30), when date);

create or replace trigger trg_logon_db
  after logon on database
begin
  insert into logon_tbl (who, when) values (user, sysdate);
end;
/

Trigger related functions

So called event attribute functions can be used within a trigger.

Disabling system triggers

System triggers can be disabled by setting _system_trig_enabled to false.