SQL Trigger – Tutorial with Examples

SQL Trigger Tutorial

This is a detailed Tutorial of SQL Trigger. Learn with the help of examples to create triggers that are auto-invoked according to specific database events.

SQL Trigger

SQL Trigger is basically a Procedure that is stored in the database and is used when a specific event occurs. Let’s say you want to perform an action in the database whenever a new row is being inserted into a particular database table. Then, in this case, you can create a SQL Trigger that executes whenever a new row of data is inserted in a table using the SQL Insert Command. Similarly,  triggers can be created for various other SQL operations such as Update or Delete. (SQL Command Types – DDL, DQL, DML, DCL & TCL)

Syntax

The syntax to create a SQL Trigger is given below and then explained.

Words written in-between Curley-braces are the variables and meant to be replaced by the appropriate statements or conditions. These are described below.

  1. create trigger is the initial syntax that simply tells that you’re going to create a trigger.
  2. {trigger_name} is the name that you want to assign to the trigger to be created.
  3. The second variable just specifies that here you need to tell whether you want the trigger action to be performed just before or after the event occurrence. So, here, you’ve either specify before or after.
  4. {event} specifies the actual event when you wanted this trigger to be executed. Here, you can write INSERT, UPDATE or DELETE.
  5. {table_name} specifies the table on which the current trigger is applicable. The trigger will be executed only if the event, i.e. INSERT, UPDATE or DELETE occurs for this table.
  6. for each row defines that you are going to perform a trigger action at the level of each row in the table.
  7. {trigger_operation} defines the actual trigger operation that you wanted to be performed.

Examples

Let’s understand the concept of SQL Triggers with the help of illustrative examples.

For both of the given examples, we’ll make use of a single database table named scores. This database table contains scores of several candidates that had given three papers in some examination. The structure of this table is shown below in the screenshot.

Scores Database Table Structure

candidate_id is the primary key and contains the unique id of each candidate who has given the three papers. Then, paper_1, paper_2 and paper_3 contains the scores for each paper. In the end, there’s a field for average_score that is simply the average score calculated from the scores of the three papers.

Now, we wanted the average_score field data to be calculated automatically for each row using SQL Triggers. There are two events associated with this table when the value of average_score needs either to be calculated or changed. These two events are INSERT and UPDATE. Whenever the scores of a new candidate are inserted, the avergae_score will be calculated using the SQL INSERT Trigger and incase, scores for any candidate are updated, this field again needs to be recalculated.

Note. Currently, the scores table does not contain any data.

Example 1. SQL INSERT Trigger

Let’s create a Trigger to calculate the average_score field value for each row whenever a new row containing the candidate_id and the scores for three papers is Inserted into the table.

Note. As I’m running this INSERT type Trigger query on MySQL, instead of the table name scores, I’ve to use the word, new to refer to the different table fields.

Once, you’ll execute the above query, you can check your TRIGGER using the query SHOW TRIGGERS. If you’re using the phpMyAdmin interface for MySQL, you can easily view triggers by visiting the Triggers Tab and it also provides the functionality to create triggers using the GUI Interface rather than queries.

SQL Triggers MySQL PhpMyAdmin

Now, we’ll insert a new row in the table and let’s check if our trigger works or not. I executed the following query.

In the query, I’ve skipped the value insertion for the average_score field and now see the following screenshot of table data.

SQL INSERT Trigger Example

You can clearly see that the value for the field average_score is automatically calculated by the Trigger action.

Example 2. SQL UPDATE Trigger

Obviously, the average_score field value needs to be re-calculated if the row data is updated. The entire query remains the same, it’s just that instead of the event INSERT, now we’ll define a new trigger for the UPDATE event. The modified query is written below.

Now, let’s execute a query to update the values for the columns paper_1, paper_2 and paper_3 to check if the UPDATE Trigger is working and updating the value for average_score column as well. The UPDATE query so executed is written below.

Now, see the updated table data screenshot given below.

MySQL UPDATE Trigger Example

Here, you can clearly observe that new average_score is calculated according to the updated values of the columns paper_1, paper_2 and paper_3.

I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.

Also, don’t forget to Subscribe to WTMatter!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

Your email address will not be published. Required fields are marked *