PL/SQL - SPLessons

PL/SQL Triggers

Chapter 28

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Triggers

PL/SQL Triggers

shape Description

Trigger is a PL/SQL program which can be executed automatically by the oracle server whenever an event is raised. And, PLSQL Triggers are named PL/SQL DBMS Output units that are stored in the database and these triggers can be invoked repeatedly.

PLSQL Triggers are fired when an appropriate event occurs in a table. PLSQL Triggers are used to perform verification checks on data values for insertion and to perform calculations.

shape Syntax

Create or replace trigger trig_name BEFORE/AFTER INSERT OR UPDATE or DELETE on <table_name>

DECLARE

var declarations;

BEGIN

//logic;

END;

Trigger =>Is a PL/SQL program.

Trigger_name =>Name of the trigger.

Table_name =>Name of the table.

Events

shape Description

Whenever the user performs DML (Data Manipulation Language) operations, oracle server will generate an event.

Inserting

Inserting is a method that returns true if one performs insert operations.

Deleting

Deleting is a method that returns true if one performs delete operations.

Updating

Updating is method that returns true if one performs update operations on a table.

shape Examples

The below example illustrates how to create a trigger.

SQL> create table employee24(emp_id number,e_name varchar(255),sal int);

Table created.
SQL> insert into employee24 values(1001,'mike',12000);

1 row created.

SQL> insert into employee24 values(1002,'jake',13000);

1 row created.

SQL> insert into employee24 values(1003,'made',14000);

1 row created.

SQL> select * from employee24;

    EMP_ID
----------
E_NAME
----------------------------------------------------------------------------

       SAL
----------
      1001
mike
     12000

      1002
jake
     13000

    EMP_ID
----------
E_NAME
----------------------------------------------------------------------------

       SAL
----------

      1003
made
     14000
SQL> create or replace trigger trig1 after update on employee24
  2  begin
  3  dbms_output.put_line('you are performing update operations');
  4  ;
  5  /
SQL>@First.sql;
Trigger created
Update employee24 set sal=sal+1000 where emp_id=1002;
1 ROW Updated
+--------+--------+--------+
| emp_id | ename  | salary |
+--------+--------+--------+
|   1002 | jake   |  14000 |
+--------+--------+--------+

In the above example, the trigger is created for employee24 table, and in the employee24 table, update operation is performed on the salary of an employee by setting sal=sal+100 where emp_id=1002. And, it has updated only one row based on the emp_id.

Types of PLSQL Triggers

shape Description

The types of PLSQL Triggers are:

Before Insert trigger

shape Description

PLSQL Before insert trigger will fire the trigger before performing the insert operation on the record values inside the table.

Trigger Syntax

shape Syntax


create trigger <trigger_name>
before insert
on <table_name> for each row

BEGIN

— variable declarations

— trigger code

END;

Trigger_name =>The name of the trigger to be created.

Table_name =>The accurate table in the database.

shape Conceptual
figure

Trigger Example

shape Examples

By viewing the below example, the concept of before insert PLSQL Triggers can be easily understood.

sql>; select * from employee01;
+--------+----------+-----------+-------------------+--------+-------+
| emp_id | emp_name | city      | designation       | salary | perks |
+--------+----------+-----------+-------------------+--------+-------+
|      1 | david    | delhi     | manager           |  12000 |   855 |
|      2 | shaha    | mumbai    | assistant manager |  13000 |   853 |
|      3 | sha      | puna      | scales manager    |  11000 |   850 |
|      4 | jack     | bangalore | designer          |  14000 |   854 |
|      5 | james    | mangalore | web designer      |  15000 |  1124 |
+--------+----------+-----------+-------------------+--------+-------+
5 rows in set (0.00 sec)


sql>CREATE TRIGGER inst_trigger BEFORE INSERT ON EmpLOYEE01
      FOR EACH ROW
      BEGIN
      UPDATE Employee01 SET Salary=Salary-400 where perks&gt;500;
      end;
      /
   Trigger created
sql> insert into employee01 values(6,'mike','chennai','developer',15500,840);
Query OK, 1 row affected (0.10 sec)

sql> select * from employee01;
+--------+----------+-----------+-------------------+--------+-------+
| emp_id | emp_name | city      | designation       | salary | perks |
+--------+----------+-----------+-------------------+--------+-------+
|      1 | david    | delhi     | manager           |  11600 |   855 |
|      2 | shaha    | mumbai    | assistant manager |  12600 |   853 |
|      3 | sha      | pune      | scales manager    |  10600 |   850 |
|      4 | jack     | bangalore | designer          |  13600 |   854 |
|      5 | james    | mangalore | web designer      |  14600 |  1124 |
|      6 | mike     | chennai   | developer         |  15100 |   840 |
+--------+----------+-----------+-------------------+--------+-------+
6 rows in set (0.00 sec)

In the above example, before insert trigger will fire the condition before inserting a record inside the table or on the table, and update operation is performed on the column salary(Set Salary=Salary-400 where perks>500). Then, the salary of all employees changes as all the employee perks are greater than the applied condition.

Before update trigger

shape Description

PLSQL Before update trigger will fire the PLSQL Triggers before performing the update operation on the record values inside the table.

Trigger Syntax

shape Syntax

create trigger <trigger_name>
before update
on <table_name> for each row

BEGIN

— variable declarations

— trigger code

END;

Trigger_name =>The name of the trigger to be created.

Table_name =>The accurate table in the database.

Trigger Example

shape Examples

By viewing the below example, the concept of before update trigger can be easily understood.

sql> select * from employee01;
+--------+----------+-----------+-------------------+--------+-------+
| emp_id | emp_name | city      | designation       | salary | perks |
+--------+----------+-----------+-------------------+--------+-------+
|      1 | david    | delhi     | manager           |  12000 |   855 |
|      2 | shaha    | mumbai    | assistant manager |  13000 |   853 |
|      3 | sha      | puna      | scales manager    |  11000 |   850 |
|      4 | jack     | bangalore | designer          |  14000 |   854 |
|      5 | james    | mangalore | web designer      |  15000 |  1124 |
|      6 | mike     | chennai   | develpoer         |  15500 |   840 |
+--------+----------+-----------+-------------------+--------+-------+
6 rows in set (0.00 sec)

sql CREATE TRIGGER updtrigger BEFORE UPDATE ON Employee01
     FOR EACH ROW
     BEGIN
     IF NEW.Salary=500 THEN -&amp;gt; SET NEW.Salary=10000;
     ELSEIF NEW.Salary&gt;500 THEN
     SET NEW.Salary=15000;
     END IF;
     END;
     /
Trigger created
sql UPDATE Employee01
    SET Salary=500;
Query OK, 5 rows affected (0.04 sec)
Rows matched: 7  Changed: 5  Warnings: 0
sql> select * from employee01;
+--------+----------+-----------+-------------------+--------+-------+
| emp_id | emp_name | city      | designation       | salary | perks |
+--------+----------+-----------+-------------------+--------+-------+
|      1 | david    | delhi     | manager           |  10000 |   855 |
|      2 | shaha    | mumbai    | assistant manager |  10000 |   853 |
|      3 | sha      | puna      | scales manager    |  10000 |   850 |
|      4 | jack     | bangalore | designer          |  10000 |   854 |
|      5 | james    | mangalore | web designer      |  10000 |  1124 |
|      6 | mike     | chennai   | develpoer         |  10500 |   840 |
+--------+----------+-----------+-------------------+--------+-------+
6 rows in set (0.00 sec)

In the above example, before update trigger will fire the condition before updating a record inside the table or on the table, and update operation is performed on the column salary(update if Salary=Salary>11000 and perks>500 then change to 10000).Then, the salary of all employees changes as the perks of all employees are greater than the applied condition.

After insert trigger

shape Description

PLSQL After Insert trigger will fire the PLSQL Triggers after performing the insert operation on the record values inside the table.

Trigger Syntax

shape Syntax


create trigger <trigger_name>
AFTER INSERT
ON <table_name> FOR EACH ROW

BEGIN

— variable declarations

— trigger code

END;
Trigger_name =>The name of the trigger to be created.

Table_name => The accurate table in the database.

Trigger Example

shape Examples

By viewing the below example, the concept of after insert trigger can be easily understood.

sql>create table student_table(stu_id int,stu_name varchar(255),stu_class int);
Query OK, 0 rows affected (0.38 sec)

sql>create table student_log1(user_id varchar(255),description varchar(255));

Query OK, 0 rows affected (0.48 sec)
sql>CREATE TRIGGER student_insert AFTER insert ON student_table FOR EACH ROW
BEGIN INSERT into student_log1(user_id,description) VALUES (user(),CONCAT('INSER
T student records',new.stu_id,' ',new.stu_name,' ',new.stu_class)); END$$
Query OK, 0 rows affected (0.15 sec)


sql>insert into student_table values(1,'mike',10);
Query OK, 1 row affected (0.10 sec)

sql> insert into student_table values(2,'mad',20);
Query OK, 1 row affected (0.11 sec)

sql> insert into student_table values(3,'mack',30);
Query OK, 1 row affected (0.10 sec)

sql> select * from student_log1;
+----------------+---------------------------------+
| user_id        | description                     |
+----------------+---------------------------------+
| root@localhost | INSERT student records1 mike 10 |
| root@localhost | INSERT student records2 mad 20  |
| root@localhost | INSERT student records3 mack 30 |
+----------------+---------------------------------+
3 rows in set (0.00 sec)

In the above example, after insert trigger will fire the condition after inserting a record inside the table or on the table i.e, after creating student_insert table it will fire on the student_log1 table and all the values will be displayed in the student_log1 table which was inserted in the student_table. 

After update trigger

shape Description

PLSQL After update trigger will fire the PLSQL Triggers after performing the insert operation on the record values inside the table.

Triggers Syntax

shape Syntax

create trigger <trigger_name>
AFTER UPDATE
ON <table_name> FOR EACH ROW

BEGIN

— variable declarations

— trigger code

END;
Trigger_name =>The name of the trigger to be created.

Table_name => The accurate table in the database.

Trigger Example

shape Examples

By viewing the below example, the concept of after update trigger can be easily understood.

Create Table Student_Table02( Stu_Id int, Stu_Name varchar(255),Stu_Class int);

create table stu_log02( user_id VARCHAR(255), description VARCHAR(255));

sql>CREATE TRIGGER stu_update
AFTER UPDATE ON stu_table FOR EACH ROW
BEGIN
INSERT into stu_log(user_id, description)
VALUES (user(), CONCAT('Update Student Record 
(',old.stu_id,' ',old.stu_name,' ',old.stu_class,
') to (',new.stu_id,' ',new.stu_name,' ',new.stu_class,')'));
END;
/

sql> select * from student_table02;
+--------+----------+-----------+
| stu_id | stu_name | stu_class |
+--------+----------+-----------+
|      1 | david    |         9 |
|      2 | shah     |         9 |
|      3 | mike     |         9 |
|      4 | james    |         9 |
+--------+----------+-----------+
4 rows in set (0.00 sec)

sql> update student_table02 set stu_class=stu_class+1;
Query OK, 4 rows affected (0.20 sec)
Rows matched: 4  Changed: 4  Warnings: 0

sql> select * from student_table02;
+--------+----------+-----------+
| stu_id | stu_name | stu_class |
+--------+----------+-----------+
|      1 | david    |        10 |
|      2 | shah     |        10 |
|      3 | mike     |        10 |
|      4 | james    |        10 |
+--------+----------+-----------+
4 rows in set (0.00 sec)
sql>select * from stu_log02;
+----------------+--------------------------------------------------------+
| user_id        | description                                            |
+----------------+--------------------------------------------------------+
| root@localhost | Update Student Record (1 david 9) to (1 david 10)      |
| root@localhost | Update Student Record (2 shah  9) to (2 shah 10)       |
| root@localhost | Update Student Record (3 mike  9) to (3 mike 10)       |
| root@localhost | Update Student Record (4 james 9) to (4 james 10)      |
+----------------+--------------------------------------------------------+      

In the above example, after update trigger will fire the condition after performing the update operation on a record inside the table or on the table. i.e., after creating the student_update table it will fire on the student_log2 table and all the values will be displayed on the student_log2 table which was inserted in student_table02.

Summary

shape Key Points

  • PLSQL Triggers –  PL/SQL Trigger gets automatically executed when an event is raised.
  • Before Insert Trigger – Before Insert Trigger fires before inserting the values.
  • Before Insert Trigger – Before Insert Triggers fires before updating the values.
  • After Insert Trigger –  After Insert Triggers fires after inserting the values.
  • After Update Trigger – After Update Triggers fires after updating the values.