The Demo Site

Today i will show you how to write your first trigger in mysql5. its very easy just follow the bellows steps:

i am considering you have a table named employee in your mysql database.

Now -

C:\>mysql -u root -p

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)

enjoy :)

now i little more about database trigger comparison:

Which supports the most triggers, Microsoft SQL Server, Oracle or MySQL? How should that affect my decision in regards to choosing a database?

Triggers are fairly new to MySQL and are, therefore, still in an early stage of development. There are a number of limitations you might find too restrictive if you rely heavily on triggers for your application. All three databases support row triggers before and after insert, update and delete statements. Oracle and Microsoft SQL Server also offer statement triggers.

MySQL allows, at most, one trigger per table per event time and type -- a table can only have one BEFORE INSERT trigger, for example. Both Orable and SQL Server permit multiple triggers of each type on a table. The order in which they execute may not be defined.

MySQL requires each of the six possible triggers to be defined separately. If you want to perform the exact same action on INSERT or on DELETE, you must define two separate triggers. Oracle and SQL Server allow trigger definitions to include multiple event types, such as BEFORE INSERT, DELETE

MySQL does not permit dynamic SQL in a trigger, or in a stored procedure called by a trigger. There is no such restriction in Oracle.

Both MySQL and Oracle attempt to prevent mutating table errors where, for example, a trigger attempts to modify the table which called it. MySQL and Oracle try to stop this by preventing such triggers from being created. It is still possible to create such errors, using cascading foreign keys or by other means. SQL Server allows for recursive triggers, and so must be coded carefully to prevent endless recursion.