.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Post New Resource Bookmark and Share   

Triggers basic

Posted By:Sreeraj Nair Mungath       Posted Date: July 05, 2012    Points: 200    Category:    URL: http://www.dotnetspark.com  

Learn SqL 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 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)


A trigger is a kind of stored procedure that automatically executes when any event occurs in the database server. Triggers are mainly classified as two types. one is DML triggers and the other is DDL triggers.

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event like INSERT, UPDATE, or DELETE statements on a table or view.

A DML trigger can contain normal sql statements or stored procedures. The trigger and the statement that fires it are treated as a single transaction. We can rolled back the transaction from within the trigger, If a severe error is found the entire transaction will automatically rolled back.

DML triggers again classified into 3 types. They are 1.After Triggers 2. Instead of Triggers.

After triggers are fired after any DML action done on any of the table which the trigger has been created.

Instead of triggers are fired when any DML action takes place on any of the table or view which the trigger has been created.

After triggers fired after the DML action done on the object
Instead of triggers are fired at the same time when any DML action taking place on the object.

Syntax for cteating a trigger on a table for INSERT, UPDATE, DELETE.

CREATE TRIGGER [trg_triggerName] ON [dbo].[table_Name] AFTER INSERT, UPDATE, DELETE
AS
BEGIN
--Here we can write your sql statements or we can execute your stored procedures.

END

we can write a trigger for each DML action separately. like one trigger for INSERT, one for UPDATE and one for DELETE. No need to write all the DML actions in one trigger. We can define the triggers as per our criteria.

But make sure that your trigger could not include a select query.
Of course for fetching data on a simple table is ok but querying from groupi of tables or on a table having huge data will make the database performance slow.




     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend