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.


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

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.

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 
for each row 
set Student.total = Student.subj1 + Student.subj2 + Student.subj3, Student.per = Student.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.

Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

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.

Improved By : SaranshSharma

Article Tags :
Practice Tags :


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