Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle Trigger

In Oracle, Trigger are similar to a stored procedure that is stored in the database and can be invoked automatically by Oracle engine whenever a specified event occurs. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it.

Triggers can be executed in response to any of the following events:

  • For DML statement (DELETE, INSERT, or UPDATE)
  • For DDL statement (CREATE, ALTER, or DROP).
  • For 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.

Oracle Advantages of Triggers

In Oracle Triggers has many advantages. Triggers are fired implicitly on the tables/views on which they are created. Some of the advantages are:

  • Auditing the tables
  • Online transaction
  • Securing from another table
  • Automatic calculations
  • Imposing security authorizations
  • Replicate the tables
  • Enforces referential integrity
  • Preventing invalid transactions

Oracle Trigger Syntax

To create trigger in Oracle:, use the following syntax.

Syntax

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;       

Where:

CREATE TRIGGER trigger_name: To create trigger with sutable name.

REPLACE OR REPLACE TRIGGER trigger_name: To replace trigger with sutable name.

{BEFORE | AFTER | INSTEAD OF}: To specifies when you want the trigger to be executed. The INSTEAD OF clause is used for creating trigger on a view.

{INSERT [OR] | UPDATE [OR] | DELETE}: To specifies DML operation.

[OF col_name]: To specifies column name that would be updated.

[ON table_name]: To specifies the name of the table associated with the trigger.

[REFERENCING OLD AS o NEW AS n]: To refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE.

[FOR EACH ROW]: To specifies a row level trigger, Otherwise the trigger will execute just once when the SQL statement is executed.

WHEN (condition): The condition on which the trigger would fire. This is valid for row level triggers.