# 
## What are triggers in databases?

A **database trigger** is a procedure that is saved in a database and is set to execute when a special event occurs. 

For example, the insertion of a row to a table in a database can serve as a trigger event. This will cause the stored process/procedure to be executed.

One commonly used stored procedure is one that evaluates the value of a column using the value from the trigger event.

## Syntax
```MySQL
CREATE TRIGGER [trigger_name] 
[BEFORE | AFTER]  
{INSERT | UPDATE | DELETE}  
ON [table_name]  
[FOR each row]  
[trigger_body] 
```
## Explanation

* **`CREATE TRIGGER[trigger_name]`**: This creates a new trigger named `trigger_name` or replaces the existing one.

* **`[BEFORE | AFTER]`**: This specifies when to execute the trigger action. It determines whether to do it before or after the trigger's activation.

* **`{INSERT | UPDATE | DELETE}: `**: This specifies the DML command to use.

* **`ON [table_name]`**: This is the table on which the trigger action is performed using the `ON` keyword.

* **`[for each row]`**: This option indicates when the trigger action will be executed on the affected rows.

* **`[trigger_body]`**: This contains the actions to be executed when the trigger is activated.

Let's walk through an example of how to create a trigger.

## Example 1
We will create a table named `student_grade` using an SQL query:



CREATE TABLE student_grade (
    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    names varchar(200),
    CA_scores int(11),
    test_scores int(11),
    exam_scores int(11),
    total_scores int(11)
);

We should have something that looks like this after the successful execution of the query above:




student_grade relation

align-center

The table's `total_scores` column is calculated using the sum of the `test_scores*` `CA_scores`, and `exam_scores` columns. 

## Example 2
Will will now write a trigger for the `total_scores` column. This will update the column with the computed values whenever an insert query is run on the table.

The action indicated in the `trigger` block will be performed before the execution of the `INSERT` command:



CREATE TRIGGER stud_tot_grade 
before INSERT 
ON
student_grade
FOR EACH ROW
SET NEW.total_scores=NEW.CA_scores + NEW.test_scores + NEW.exam_scores ; 

If we run the query above, the trigger will be saved as a sub-process in our database. This trigger will be invoked whenever data is inserted into the given table. 

## Example 3

Let's insert some records into the `student_grade` table:

INSERT INTO student_grade
(id,names,CA_scores ,test_scores,exam_scores) 
VALUES
(1, "John Miller", 20, 20, 40),
(2, "Jean Miller", 12, 20, 50),
(3, "Sean Miller", 20, 15, 30); 
 
 SELECT * FROM student_grade

The code above will add three new rows to our table. The value of the `total_scores` column is calculated automatically using the saved trigger sub-process.

Therefore, on successful execution of the query, our table will now look like this:

student_grade

How to create database Triggers in the MySQL database

Database triggers are procedures executed upon specified events like insertion, updating, or deletion of rows. They can automate column value updates.

id	names	CA_scores	test_scores	exam_scores	total_scores
1	John Miller	20	20	40	80
2	Jean Miller	12	20	50	82
3	Sean Miller	20	15	30	65