Home Mysql Mysql5 TRIGGERS
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

TRIGGERS

Advertisement
A Trigger is a named database object which defines some action that the database should take when some databases related event occurs. Triggers are executed when you issues a data manipulation command like INSERT, DELETE, UPDATE on a table for which the t

TRIGGERS

     

A Trigger is a named database object which defines some action that the database should take when some databases related event occurs. Triggers are executed when you issues a data manipulation command like INSERT, DELETE, UPDATE on a table for which the trigger has been created. They are automatically executed and also transparent to the user. But for creating the trigger the user must have the CREATE TRIGGER privilege. In this section we will describe you about the syntax to create and drop the triggers and describe you some examples of how to use them.

CREATE TRIGGER

The general syntax of CREATE TRIGGER is :
  CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_statement

By using above statement we can create the new trigger. The trigger can associate only with the table name and that must be refer to a permanent table. Trigger_time means trigger action time. It can be BEFORE or AFTER. It is used to define that the trigger fires before or after the statement that executed it. Trigger_event specifies the statement that executes the trigger. The trigger_event can be any of the DML Statement : INSERT, UPDATE, DELETE.

We can not have the two trigger for a given table, which have the same trigger action time and event. For Instance : we cannot have two BEFORE INSERT triggers for same table. But we can have a BEFORE INSERT and BEFORE UPDATE trigger for a same table.

Trigger_statement have the statement that executes when the trigger fires but if you want to execute multiple statement the you have to use the BEGIN?END compound statement.

We can refer the columns of the table that associated with trigger by using the OLD and NEW keyword. OLD.column_name is used to refer the column of an existing row before it is deleted or updated and NEW.column_name is used to refer the column of a new row that is inserted or after updated existing row.

In INSERT trigger we can use only NEW.column_name because there is no old row and in a DELETE trigger we can use only OLD.column_name because there is no new row. But in UPDATE trigger we can use both, OLD.column_name is used to refer the columns of a row before it is updated and NEW.Column_name is used to refer the column of the row after it is updated.

In the following example we are updating the Salary column of Employee table before inserting any record in Emp table. Example :

mysql> SELECT * FROM Employee;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10300  | 853   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10300  | 853   |
| 3   | Chandan | Banglore | Team Leader       | 15450  | 999   |
| 5   | Tapan   | Pune     | Developer         | 20600  | 1111  |
| 6   | Amar    | Chennai  | Developer         | 16000  | 1124  |
| 7   | Santosh | Delhi    | Designer          | 10000  | 865   |
| 8   | Suman   | Pune     | Web Designer      | 20000  | 658   |
+-----+---------+----------+-------------------+--------+-------+
7 rows in set (0.00 sec)
mysql> delimiter //
mysql> CREATE TRIGGER ins_trig BEFORE INSERT ON Emp
    -> FOR EACH ROW
    -> BEGIN
    -> UPDATE Employee SET Salary=Salary-300 WHERE Perks>500;
    -> END;
    -> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> INSERT INTO Emp VALUES(9,'Rajesh','Delhi','Developer',15000,658);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM Employee;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10000  | 853   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10000  | 853   |
| 3   | Chandan | Banglore | Team Leader       | 15150  | 999   |
| 5   | Tapan   | Pune     | Developer         | 20300  | 1111  |
| 6   | Amar    | Chennai  | Developer         | 15700  | 1124  |
| 7   | Santosh | Delhi    | Designer          | 9700   | 865   |
| 8   | Suman   | Pune     | Web Designer      | 19700  | 658   |
+-----+---------+----------+-------------------+--------+-------+
7 rows in set (0.00 sec)

In the following example we are modifying the salary of Employee table before updating the record of the same table. Example :

mysql> delimiter //
mysql> CREATE TRIGGER updtrigger BEFORE UPDATE ON Employee
    -> FOR EACH ROW
    -> BEGIN
    -> IF NEW.Salary<=500 THEN
    -> SET NEW.Salary=10000;
    -> ELSEIF NEW.Salary>500 THEN
    -> SET NEW.Salary=15000;
    -> END IF;
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> UPDATE Employee
    -> SET Salary=500;
Query OK, 5 rows affected (0.04 sec)
Rows matched: 7  Changed: 5  Warnings: 0
mysql> SELECT * FROM Employee;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10000  | 853   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10000  | 853   |
| 3   | Chandan | Banglore | Team Leader       | 10000  | 999   |
| 5   | Tapan   | Pune     | Developer         | 10000  | 1111  |
| 6   | Amar    | Chennai  | Developer         | 10000  | 1124  |
| 7   | Santosh | Delhi    | Designer          | 10000  | 865   |
| 8   | Suman   | Pune     | Web Designer      | 10000  | 658   |
+-----+---------+----------+-------------------+--------+-------+
7 rows in set (0.00 sec)
mysql> UPDATE Employee
    -> SET Salary=1500;
Query OK, 7 rows affected (0.03 sec)
Rows matched: 7  Changed: 7  Warnings: 0
mysql> SELECT * FROM Employee;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 15000  | 853   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 15000  | 853   |
| 3   | Chandan | Banglore | Team Leader       | 15000  | 999   |
| 5   | Tapan   | Pune     | Developer         | 15000  | 1111  |
| 6   | Amar    | Chennai  | Developer         | 15000  | 1124  |
| 7   | Santosh | Delhi    | Designer          | 15000  | 865   |
| 8   | Suman   | Pune     | Web Designer      | 15000  | 658   |
+-----+---------+----------+-------------------+--------+-------+
7 rows in set (0.01 sec)

DROP TRIGGER

The general syntax of DROP TRIGGER is :
  DROP TRIGGER trigger_name

This statement is used to drop a trigger. Example of Dropping the Trigger :

mysql> DROP TRIGGER updtrigger;
Query OK, 0 rows affected (0.02 sec)
Advertisement

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: March 13, 2008

Ask Questions?    Discuss: TRIGGERS   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
veeranagu
April 14, 2011
triggers

Hello Sir, This is veeranagu from banagalore.
Naresh
June 27, 2011
trigger

its very good
muhammed seid
October 28, 2011
advanced database system

I always appreciate you.please continue improving your service to the World.......
muhammed seid
October 28, 2011
advanced database system

I'm an IT student. I want to know more about database. so, please help me.as beginning let me give you a question : by using the following table structure shown like this: stud_course(studId,corseNo,courseTitle,crhr) Write a trigger to prevent insertion of record at a point where the total sum of credits taken by the student exceeds 144 credit hours.
karthikeyan p
November 7, 2011
java,oracle,j2ee

goood
anu
December 20, 2011
trigger

why we are using triger and what is the usage?
Ghulam Yassen
January 14, 2012
Database Triggers

Very Usefull information about Database Triggers. Beofre Reading This ARTICLE I were did't have any Info about Database Trigger But Now I have Created many more and Complicated and Complex Triggers after reading this article. Thanks to the Great Instructor.
mohan.o
February 9, 2012
trigger statement

please send me a trigger statement with best example and also a cursor and view concept.......................
indhumathi
March 29, 2012
query is not running

DELIMITER $$ DROP PROCEDURE IF EXISTS dcb_collect_interest$$ CREATE PROCEDURE dcb_collect_interest() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE interest1 DOUBLE; DECLARE lref VARCHAR(30); DECLARE cur1 CURSOR FOR --- if i run this select stat i ll get output--- select iterestrate,loanref from loan where loanref in (select loanaccno from month_dcb where month1=2 and year1=2012); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur1; START TRANSACTION; read_loop: LOOP FETCH cur1 INTO interest1, lref; IF done THEN LEAVE read_loop; END IF; -- here no updation ---0 rows affected update month_dcb set interest=interest1 where loanaccno=lref and year1=2012 and month1=2; END LOOP; COMMIT; CLOSE cur1; END;
akshath niranjan
April 21, 2012
mysql

u r examples are excelent
pranav
April 11, 2012
Triggers

In this example "mysql> CREATE TRIGGER ins_trig BEFORE INSERT ON Emp -> FOR EACH ROW -> BEGIN -> UPDATE Employee SET Salary=Salary-300 WHERE Perks>500; -> END; -> // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> INSERT INTO Emp VALUES(9,'Rajesh','Delhi','Developer',15000,658);" what happens to 9th row which is being inserted?
sachin Thapa
May 19, 2012
plz correct the material

in the above material you are using emp as insert table and employee as the dispay table. Please use only one name
SIVA R
August 3, 2012
experience

it is very useful and easiest way to learn about trigger... really it is very useful to me.. thank you..
dopedroid
August 11, 2012
MYSQL TUTORIAL ON ANDROID

Hi Buddies, Well now we have a handy reference of SQL in android market named SQL guru for free .......go through tweaks in the tips and walkthroughs given.....its pretty cool. https://play.google.com/store/apps/details?id=com.dopdroid.mysqlguru
Sourav Roychowdhury
August 13, 2012
MY SQL TRIGGERING

I am trying this program of setting salary 10000 if salary<=500 and 15000 if salary>500. when i am fireing the trigger it get executed but after updating the salary SET to 500.all the salary is setting to 500.that means trigger is not workinng.why this happend..please reply...
Pradeep Kumar Singh
August 31, 2012
Congratulate

Hi, Congratulates to giving the so simple example the clear description.
mahipatsinh mori
September 8, 2012
to get triggers for my project

i currentlly working on a banking system based on sql so plz recommend me triggers if any idea please...
kamal
September 30, 2012
compliment

oh really it is very nice and short note on trigger i like it................. a lot of thanx.
mallikharjunarao
October 4, 2012
PL/SQL

VERY GOOD EXAMPLES CLEAR PRACTICAL EXPLANATION. THANK YOU FOR THIS.PLEASE FORWARD THIS TYPE CLEAR EXPLANATION EXAMPLES TO MY EMAIL FROM BASIC ONWARDS.PLEASE FORWARD THANK YOU.
Sarfraz Ali
December 12, 2012
PHP

this is very useful tutorial,specially for new developer.. i glad to see these tutorial on MYSQL thanks for that
munna
October 23, 2012
oracle

how to find 2nd largest salary or element in oracle.
priya
January 3, 2013
not satisfied

not up to the level
Varuna
October 25, 2013
Triggers

Not understand
zekria
December 21, 2013
Hello

I need some information about Tirgger (SQL) if it is possible send as information about that thanks you
Piyush Shrivastava
January 6, 2014
solve the problem of time stamp triggers

If we want to allow trigger for update or store TimeIn an TimeOut in employees table to calculate total working hours of an employee.
prince rokney
May 10, 2014
getting knowledge

very good service
DEEPAK NEHE
November 29, 2011
about trigger

siri want moreinformation on trigeer with some more example of trigger
DMCA.com