SQL Trigger | Student Database



Trigger: A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

Syntax:

create trigger [trigger_name] 
[before | after]  
{insert | update | delete}  
on [table_name]  
[for each row]  
[trigger_body] 

Explanation of syntax:

  1. create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
  2. [before | after]: This specifies when the trigger will be executed.
  3. {insert | update | delete}: This specifies the DML operation.
  4. on [table_name]: This specifies the name of the table associated with the trigger.
  5. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.
  6. [trigger_body]: This provides the operation to be performed as trigger is fired

BEFORE and AFTER of Trigger:
BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is run.

Example:
Given Student Report Database, in which student marks assessment is recorded. In such schema, create a trigger so that the total and average of specified marks is automatically inserted whenever a record is insert.



Here, as trigger will invoke before record is inserted so, BEFORE Tag can be used.

Suppose the database Schema –

mysql> desc Student; 
+-------+-------------+------+-----+---------+----------------+ 
| Field | Type        | Null | Key | Default | Extra          | 
+-------+-------------+------+-----+---------+----------------+ 
| tid   | int(4)      | NO   | PRI | NULL    | auto_increment | 
| name  | varchar(30) | YES  |     | NULL    |                | 
| subj1 | int(2)      | YES  |     | NULL    |                | 
| subj2 | int(2)      | YES  |     | NULL    |                | 
| subj3 | int(2)      | YES  |     | NULL    |                | 
| total | int(3)      | YES  |     | NULL    |                | 
| per   | int(3)      | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+ 
7 rows in set (0.00 sec)

SQL Trigger to problem statement.

create trigger stud_marks 
before INSERT 
on 
Student 
for each row 
set new.total = new.subj1 + new.subj2 + new.subj3, new.per = new.total * 60 / 100;

Above SQL statement will create a trigger in the student database in which whenever subjects marks are entered, before inserting this data into the database, trigger will compute those two values and insert with the entered values. i.e.,

mysql> insert into Student values(0, "ABCDE", 20, 20, 20, 0, 0); 
Query OK, 1 row affected (0.09 sec) 

mysql> select * from Student; 
+-----+-------+-------+-------+-------+-------+------+ 
| tid | name  | subj1 | subj2 | subj3 | total | per  | 
+-----+-------+-------+-------+-------+-------+------+ 
| 100 | ABCDE |    20 |    20 |    20 |    60 |   36 | 
+-----+-------+-------+-------+-------+-------+------+ 
1 row in set (0.00 sec)

In this way trigger can be creates and executed in the databases.



My Personal Notes arrow_drop_up

सर्वशक्तिशाली इकलौता

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.