Skip to content
Advertisements

DBMS/U3 Topic 7 PL/SQL: Triggers

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 −

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

Triggers can be written for the following purposes −

  • 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

Creating Triggers

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;

Where,

  • CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name.
  • {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view.
  • {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
  • [OF col_name] − This specifies the column name that will be updated.
  • [ON table_name] − This specifies the name of the table associated with the trigger.
  • [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE.
  • [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.
  • WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers.

 

Advertisements

1 Comment »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: