What is a Trigger explain tips oracle mysql advantage

Explain Trigger

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Types of PL/SQL Triggers

There are two types of triggers based on the which level it is triggered.

1) Row level trigger - An event is triggered for each row upated, inserted or deleted.

2) Statement level trigger - An event is triggered for each sql statement executed.

PL/SQL Trigger Execution Hierarchy

The following hierarchy is followed when a trigger is fired.

1) BEFORE statement trigger fires first.

2) Next BEFORE row level trigger fires, once for each row affected.

3) Then AFTER row level trigger fires once for each affected row. This events will alternates between BEFORE and AFTER row level triggers.

4) Finally the AFTER statement level trigger fires.

Create Table Stu_Table

Create Table Stu_Table( Stu_Id int, Stu_Name varchar(15),Stu_Class int);

Create Table Stu_Log

create table stu_log( user_id VARCHAR(15), description VARCHAR(100));

Create Trigger Stu_Update
The below Query create a Trigger 'stu_update' on
table stu_table.

delimiter $$ 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$$ delimiter ;

Insert Data Into Stu_Table

insert into stu_table values(1, 'Komal',10); insert into stu_table values(2, 'Ajay',10); insert into stu_table values(3, 'Santosh',10); insert into stu_table values(4, 'Rakesh',10); insert into stu_table values(5, 'Bhau',10);

Stu_Table

+--------+----------+-----------+ | Stu_Id | Stu_Name | Stu_Class | +--------+----------+-----------+ | 1 | Komal | 10 | | 2 | Ajay | 10 | | 3 | Santosh | 10 | | 4 | Rakesh | 10 | | 5 | Bhau | 10 | +--------+----------+-----------+

Update Stu_Table

update stu_table set Stu_Class = stu_class+1;

Stu_Table

+--------+----------+-----------+ | Stu_Id | Stu_Name | Stu_Class | +--------+----------+-----------+ | 1 | Komal | 11 | | 2 | Ajay | 11 | | 3 | Santosh | 11 | | 4 | Rakesh | 11 | | 5 | Bhau | 11 | +--------+----------+-----------+