Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

mysql
query
database
communitycreator

How to create database Triggers in the MySQL database

NDUKWE CHIDERA K.

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

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)
);
SQL for student table

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

student_grade relation

id

names

 CA_scores

test_scores

exam_scores

total_scores

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 ; 
Trigger for the 'total_scores' column

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
Insert query

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

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

RELATED TAGS

mysql
query
database
communitycreator
RELATED COURSES

View all Courses

Keep Exploring