pl/sql trigger

By ukmodak | June 6th 2022 12:23:03 PM | viewed 195 times

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; 
/ 
bONEandALL
Visitor

Total : 19489

Today :24

Today Visit Country :

  • United States
  • Germany
  • United Kingdom
  • India
  • Switzerland
  • Singapore
  • The Netherlands