Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events −

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers can be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions

The syntax for creating a trigger is −

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END; 

CREATE OR REPLACE TRIGGER display_course_id 
BEFORE DELETE OR INSERT OR UPDATE ON ukaccount.ukcourse 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   id_diff number; 
BEGIN 
   id_diff := :NEW.id  - :OLD.id; 
   dbms_output.put_line('Old id: ' || :OLD.id); 
   dbms_output.put_line('New id: ' || :NEW.id); 
   dbms_output.put_line('ID difference: ' || id_diff); 
END; 
/