Triggers

• a DML operation on a database or certain kinds of view
• some DDL operations (such as table creation)
• some system operations like (a DB startup or shutdown)

Using triggers we can

• maintain a complex integrity constraints not possible through declarative constraints enabled at table creation;
• audit information in a table by recording the changes made and who made them;
• automatically signal other programs that action needs to take place when changes are made to a table;
• publish information about various events in a publish-subscribe environment.

Creating triggers

create [or replace] trigger trigger_name
 {before | after | instead of} triggering_event
 [referencing_clause]
 [when trigger_condition]
 [for each row]
 trigger_body;

Creating DML triggers

instead of triggers

Order of DML trigger firing

1. Execute the before statement-level triggers, if present.
2. For each row affected by the statement:
   1. Execute the before row-level triggers, if present.
   2. Execute the statement itself.
   3. Execute the after row-level triggers, if present.
3. Execute the after statement-level triggers, if present.

Correlation identifiers in row-level triggers

If desired, we can use the referencing clause to specify a different name for :old and :new. This clause has the following syntax:

referencing [old as old_name] [new as new_name]

create or replace trigger GenerateProfID
 before insert on Professors
 referencing new as new_prof
 for each row
begin
  if :new_prof.pid is null then
    select profids.nextval into :new_prof.pid from dual;
  end if;
end GenerateProfID;

The when clause

when trigger_condition

create trigger InsertStudent
 after insert or update on Student
 for each row
 when (new.mid = 'DB')
begin
  /* put code here */
end InsertStudent;

Trigger predicates

• inserting - is true if the triggering statement is insert; false otherwise.
• updating - is true if the triggering statement is update; false otherwise.
• deleting - is true if the triggering statement is delete; false otherwise.

System triggers

create [or replace] trigger [schema.]trigger_name
  { before | after }
  { DDL_event_list | DB_event_list}
  on { database | [schema.]schema }
  [when condition]
  trigger_body

A system trigger can be defined at the database level trigger or a schema level trigger (keywords database and schema). A database-level trigger will fire whenever the triggering event occurs, whereas a schema-level trigger will fire only when the triggering event occurs for the specified schema. If the schema name is not specified with the schema keyword, it defaults to the schema that owns the trigger. The following trigger monitors all logons to the current schema

create or replace trigger MonitorLogons
  after logon on schema
begin
  insert into danil.logons values (user, sysdate);
end MonitorLogons;

System privileges related to triggers

Privilege	Description
create trigger	Allows the grantee to create a trigger in his or her own schema.
create any trigger	Allows the grantee to create triggers in any schema except SYS.
alter any trigger	Allows the grantee to enable, disable, or compile database triggers in any schema except SYS.
drop any trigger	Allows the grantee to drop database triggers in any schema except SYS.
administer database trigger	Allows the grantee to create or alter a system trigger on the database. The grantee must also have either create trigger or create any trigger privilege.

Event attribute function

create or replace trigger MonitorCreation
 after create on database
declare
  msg TestCreateTrig.event%type;
begin
  if sys.dictionary_obj_type = 'USER' then
    msg := 'User ' || sys.login_user;
    msg := msg || ' created user ' || sys.dictionary_obj_name;
	msg := msg || ' identified by ' || sys.des_encrypted_password;
    insert into system.TestCreateTrig values (user, sysdate, msg);
  end if;
end MonitorCreation;

Restrictions on triggers

• A trigger may not issue any transaction control statements.
• That also means that any procedure or a function called in the body of the trigger may not issue any transaction control statements.
• The trigger body cannot declare long or long raw variables. Also, :new and :old variables cannot refer to a long or long raw columns in the table for which trigger is defined.
• In Oracle8 and higher, code in a trigger body may reference and use lob columns, but it may not modify the values of the columns.

• startup and shutdown triggers cannot have any conditions
• servererror triggers can use errno test to check for a specific error only
• logon and logoff triggers can check the user ID or username with the userid or username tests
• DDL triggers can check the type and name of the object being modified, and can check the useris or user name

Other trigger issues

To drop a trigger user needs to execute the following command:

drop trigger trigger_name;

alter trigger trigger_name disable;

alter trigger trigger_name enable;

alter table table_name  {disable | enable} all triggers;