---

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.

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.

---

---