PL/SQL Exception

PL/SQL Exception

PL/SQL Interview Questions

PL/SQL Interview Questions and Answers


PL/SQL Trigger

In PL/SQL, 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.

PL/SQL Advantages of Triggers

In PL/SQL 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

PL/SQL Trigger Syntax

To create trigger in PL/SQL:, 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.