Triggers in PL – SQL Free SQL Tutorials and Online Training

Triggers are a series of PL/SQL statements attached to a database table that executes whenever a triggering event (select, update, insert, delete) occurs. Unlike stored procedures and functions, they not explicitly called, but they are activated when a triggering event occurs.

A trigger is nothing but a PL/SQL code executed automatically in response to a database event, typically DML actions. Like other stored procedures, triggers are stored in the database.

Applications of Triggers:

Enforce complex constraints that restrict data entry into tables. Financial posting is an example of this.
Useful in implementing actions that should be automatically done when an corresponding situation occurs like changing discount depending on the items purchased
Implement auditing “logs”

Some points on Triggers:

Triggers do not issue transaction control statements (such as commit). Triggers are part of the SQL transaction that invoked them.
USER_TRIGGERS provides a data dictionary view of triggers
You can associate up to 12 database triggers with a given table. A database trigger has three parts: a triggering event, an optional trigger constraint, and a trigger action
When an event occurs, a database trigger associated with the event is fired, and a predefined PL/SQL block will perform the necessary action.

Differences between Stored Procedures and Triggers:

Triggers	Procedures
Defined with CREATE TRIGGER statement	Defined with CREATE PROCEDURE statement
Implicitly invoked when triggering actions occur	Explicitly called when required
Commit, Savepoint and rollback are not allowed	Commit, Savepoint and rollback are allowed
USER_TRIGGERS provides dictionary view of triggers	USER_SOURCE provides dictionary view pf stored procedures

Syntax for Trigger

The general syntax of a trigger is as below:

CREATE OR REPLACE TRIGGER <trigger_name>

[BEFORE/AFTER][DELETE/INSERT/UPDATE of <column_name |, column_name… |>

ON <table_name>

|FOR EACH ROW|

|WHEN <triggering condition>|

|DECLARE|

BEGIN

trigger statements

…………

END;

CREATE OR REPLACE TRIGGER is used to create a trigger in PL/SQL
trigger_name is the name of the trigger to be created
BEFORE/AFTER indicates whether the trigger should be called before or after data manipulation command execution occurs
INSERT/UPDATE/ DELETE indicate on which DML command execution, the trigger should be invoked
table_name is the name of the table for which trigger is associated
trigger statements are the body of the trigger

Example of Trigger:

The trigger details are as follows:

Name of Trigger: Increase Discount
Triggering Event: After insertion on orders
Trigger Body or Trigger code that is fired: Update customer discount percentage

Code:

CREATE or REPLACE  TRIGGER IncreaseDiscount AFTER INSERT  on Orders
FOR EACH ROW
BEGIN
UPDATE Customers SET discnt = discnt+.1 WHERE Customers.cid=:new.cid;
END;

The above trigger whenever an order is inserted and updates discount and increased it by 0.1%
: new and : old represent reserved words for Oracle’s PL/SQL. In this example, :new represents the cid of the new row in the orders table that was just inserted.

Types of Trigger:

A trigger may be a ROW or STATEMENT type. If the statement FOR EACH ROW is present in the CREATE TRIGGER clause of a trigger, the trigger is a row trigger. A row trigger is fired for each row affected by an triggering statement.
A statement trigger, however, is fired only once for the triggering statement, regardless of the number of rows affected by the triggering statement

Statement Trigger Example:

The below example shows a trigger that limits the DML actions to the employee table to weekdays from 8.30am to 6.30pm. If a user tries to insert/update/delete a row in the EMPLOYEE table that is against the specified timings, a warning message will be prompted

CREATE OR REPLACE TRIGGER stat_trigger 
  BEFORE DELETE OR INSERT OR UPDATE ON employee
BEGIN    
IF (TO_CHAR(SYSDATE, 'day') IN ('sat', 'sun')) OR     (TO_CHAR(SYSDATE,'hh:mi') 
   NOT BETWEEN '08:30' AND '18:30') THEN           
RAISE_APPLICATION_ERROR(-20500, 'table is secured');    
END IF;
END;

Row Trigger Example:

The below trigger occurs when a record is updated, inserted or deleted and changes are stored to separate databases. If a row in employee table is removed, store that removed employee details in xemployee table. If a row in employee table is updated, store that particular employee details in cemployee table. If a row in employee table is inserted, store that particular employee details in uemployee table.

CREATE OR REPLACE TRIGGER mytrig2
AFTER DELETE OR INSERT OR UPDATE ON employee
FOR EACH ROW
BEGIN        
IF DELETING THEN  
  INSERT INTO xemployee (emp_ssn, emp_last_name,emp_first_name, deldate)          
  VALUES (:old.emp_ssn, :old.emp_last_name,:old.emp_first_name, sysdate);         
ELSIF INSERTING THEN         
   INSERT INTO nemployee (emp_ssn, emp_last_name,emp_first_name, adddate)           
  VALUES (:new.emp_ssn, :new.emp_last_name,:new.emp_first_name, sysdate);    
 ELSIF UPDATING('emp_salary') THEN          
  INSERT INTO cemployee (emp_ssn, oldsalary, newsalary, up_date)         
  VALUES (:old.emp_ssn,:old.emp_salary, :new.emp_salary, sysdate);    
 ELSE         
  INSERT INTO uemployee (emp_ssn, emp_address, up_date)           
  VALUES (:old.emp_ssn, :new.emp_address, sysdate);        
END IF;
END;

Syntax for Enabling, Disabling and Dropping Triggers:

To alter a trigger the syntax is ALTER TRIGGER trigger_name DISABLE;
The syntax to disable all the triggers for a particular table is ALTER TABLE table_name DISABLE ALL TRIGGERS;
The syntax to enable a particular trigger which is disabled for a table is ALTER TABLE table_name ENABLE trigger_name;
The syntax to enable all triggers for a particular table is ALTER TABLE table_name ENABLE ALL TRIGGERS;
The syntax To drop a particular trigger is DROP TRIGGER trigger_name

Join Telegram for Updates