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 = BEFORE / AFTER.
Trigger_event = statement that executes the trigger.
The trigger_event = DML Statement : INSERT, UPDATE, DELETE.
We cannot 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 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
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)
|
In addition,
triggers are commonly used to
- automatically generate derived column values
- prevent invalid transactions
- enforce complex security authorizations
- enforce referential integrity across nodes in a distributed database
- enforce complex business rules
- provide transparent event logging
- provide sophisticated auditing
- maintain synchronous table replicates
- gather
statistics on table access
No comments:
Post a Comment