Home Sql Trigger Mysql Time Trigger
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Mysql Time Trigger

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

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)
Advertisements

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: June 11, 2009

Ask Questions?    Discuss: Mysql Time Trigger  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
Uma.S
July 11, 2011
Trigger

I created this trigger Based on above Example .. but it showing Error..Please Solve my problem.. delimiter $$ CREATE TRIGGER Employee_Trigger AFTER UPDATE ON Emp_dtl FOR EACH ROW BEGIN INSERT into Emp_Log (Emp_id, First_Name,Last_Name,start_Date,End_Date,City,Description ) VALUES (New.Emp_id, New.First_Name,New.Last_Name,New.start_Date,New.End_Date,New.City,New.Description); END$$
DMCA.com