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)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
