Mysql Time Trigger

Mysql Time Trigger is fired when a specified event is occurred. The trigger event can be before trigger, after trigger  and instead of trigger.

Understand with Example

The Tutorial illustrate an example from Mysql Time Trigger. To grasp this example we create a table 'Stu_Table1'. The create table keywords is used to create a table Stu_Table1 with field attribute and data type respectively.

Query to create Table:-

mysql> Create Table Stu_Table1( Stu_Id int, Stu_Name varchar(15),Stu_Class int,time Time);

Query to insert data into Table:-

The insert into add the records or rows to the table 'stu_Table1'

currtime : The curtime ( ) function is used to return the current time of records which are added to the table 'stu_table1'. 

mysql> insert into stu_Table1 values('1','Girish',12,curtime());

mysql> insert into stu_Table1 values('1','ABC',12,curtime());
Query OK, 1 row affected (0.02 sec)

mysql> insert into stu_Table1 values('2','DEF',12,curtime());
Query OK, 1 row affected (0.02 sec)

mysql> insert into stu_Table1 values('3','GHI',12,curtime());
Query OK, 1 row affected (0.02 sec)

mysql> insert into stu_Table1 values('4','JKL',12,curtime());
Query OK, 1 row affected (0.02 sec)

Query to View  table data:-

mysql> select * from stu_table1;

Output:-

+--------+----------+-----------+----------+
| Stu_Id | Stu_Name | Stu_Class | time     |
+--------+----------+-----------+----------+
|      1 | Girish   |        12 | 17:15:00 |
|      1 | ABC      |        12 | 17:16:12 |
|      2 | DEF      |        12 | 17:16:23 |
|      3 | GHI      |        12 | 17:16:33 |
|      4 | JKL      |        12 | 17:16:48 |
+--------+----------+-----------+----------+
5 rows in set (0.00 sec)

Query to create another Table:-

The below Query is used to create a back up of table 'Stu_backup'

mysql> Create Table Stu_backup( Stu_Id int, Stu_Name varchar(15),Stu_Class int,time Timelastdeleted);

Query to create trigger:-

Now we use create trigger stu_delete on table stu_table that will fired the trigger automatically before you delete any rows or records from table stu_table1.

mysql> CREATE TRIGGER stu_delete
    ->
    -> before delete ON stu_table1 FOR EACH ROW
    -> BEGIN
    ->
    -> insert into stu_backup values(old.stu_id,old.stu_name,old.stu_class,curtime());
    -> END$$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;

Query to delete data from table named stu_table1 so that trigger will automatically fired:-

The Trigger fired automatically before you delete any records or rows from table 'stu_table1'.

mysql> delete from stu_table1 where stu_name='ABC';
Query OK, 1 row affected (0.03 sec)

Query to view data deleted which is stored in table stu_backup:-

The Query below show you the deleted records from stu_table1 which  is stored in table stu_backup. 

mysql> select * from stu_backup;

Output:-

+--------+----------+-----------+-----------------+
| Stu_Id | Stu_Name | Stu_Class | Timelastdeleted |
+--------+----------+-----------+-----------------+
|      1 | ABC      |        12 | 17:41:06        |
+--------+----------+-----------+-----------------+
1 row in set (0.00 sec)