Open In App

SQL Trigger | Student Database

Last Updated : 12 Apr, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

A trigger is a stored procedure in a database that 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 specific table columns are updated. In simple words, a trigger is a collection of SQL statements with particular names that are stored in system memory. It belongs to a specific class of stored procedures that are automatically invoked in response to database server events. Every trigger has a table attached to it.

Because a trigger cannot be called directly, unlike a stored procedure, it is referred to as a special procedure. A trigger is automatically called whenever a data modification event against a table takes place, which is the main distinction between a trigger and a procedure. On the other hand, a stored procedure must be called directly.

The following are the key differences between triggers and stored procedures:

  1. Triggers cannot be manually invoked or executed.
  2. There is no chance that triggers will receive parameters.
  3. A transaction cannot be committed or rolled back inside a trigger.

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 affected row.
  6. [trigger_body]: This provides the operation to be performed as the trigger is fired

Why Do We Employ Triggers?

When we need to carry out some actions automatically in certain desirable scenarios, triggers will be useful. For instance, we need to be aware of the frequency and timing of changes to a table that is constantly changing. In such cases, we could create a trigger to insert the required data into a different table if the primary table underwent any changes.

Different Trigger Types in SQL Server

Two categories of triggers exist:

  1. DDL Trigger
  2. DML Trigger
  3. Logon Triggers

DDL Triggers 

The Data Definition Language (DDL) command events such as Create_table, Create_view, drop_table, Drop_view, and Alter_table cause the DDL triggers to be activated.

SQL Server

create trigger safety
on database
for
create_table,alter_table,drop_table
as
print 'you can not create,drop and alter tab

Output:

 

DML Triggers

The Data uses manipulation Language (DML) command events that begin with Insert, Update, and Delete set off the DML triggers. corresponding to insert_table, update_view, and delete_table.

SQL Server

create trigger deep 
on emp
for
insert,update ,delete
as
print 'you can not insert,update and delete this table i'
rollback;

Output:

 

Logon Triggers

logon triggers are fires in response to a LOGON event. When a user session is created with a SQL Server instance after the authentication process of logging is finished but before establishing a user session, the LOGON event takes place. As a result, the PRINT statement messages and any errors generated by the trigger will all be visible in the SQL Server error log. Authentication errors prevent logon triggers from being used. These triggers can be used to track login activity or set a limit on the number of sessions that a given login can have in order to audit and manage server sessions.

How does SQL Server Show Trigger?

The show or list trigger is useful when we have many databases with many tables. This query is very useful when the table names are the same across multiple databases. We can view a list of every trigger available in the SQL Server by using the command below:

Syntax:

FROM sys.triggers, SELECT name, is_instead_of_trigger    
IF type = ‘TR’; 

The SQL Server Management Studio makes it very simple to display or list all triggers that are available for any given table. The following steps will help us accomplish this:

Go to the Databases menu, select the desired database, and then expand it.

  • Select the Tables menu and expand it.
  • Select any specific table and expand it.

We will get various options here. When we choose the Triggers option, it displays all the triggers available in this table.

BEFORE and AFTER 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 a schema, create a trigger so that the total and percentage of specified marks are automatically inserted whenever a record is inserted. 

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

Suppose the Database Schema

Query

mysql>>desc Student;

 

SQL Trigger to the problem statement.

img2

 

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, the trigger will compute those two values and insert them with the entered values. i.e.

Output

img3

 

In this way, triggers can be created and executed in the databases.

Advantage of Triggers

The benefits of using triggers in SQL Server include the following:

  1. Database object rules are established by triggers, which cause changes to be undone if they are not met. 
  2. The trigger will examine the data and, if necessary, make changes.
  3. We can enforce data integrity thanks to triggers.
  4. Data is validated using triggers before being inserted or updated.
  5. Triggers assist us in maintaining a records log.
  6. Due to the fact that they do not need to be compiled each time they are run, triggers improve the performance of SQL queries.
  7. The client-side code is reduced by triggers, saving time and labor.
  8. Trigger maintenance is simple.

Disadvantage of Triggers

The drawbacks of using triggers in SQL Server include the following:

  1. Only triggers permit the use of extended validations.
  2. Automatic triggers are used, and the user is unaware of when they are being executed. Consequently, it is difficult to troubleshoot issues that arise in the database layer.
  3. The database server’s overhead may increase as a result of triggers.
  4. In a single CREATE TRIGGER statement, we can specify the same trigger action for multiple user actions, such as INSERT and UPDATE.
  5. Only the current database is available for creating triggers, but they can still make references to objects outside the database.

Frequently Asked Questions

Q1: What is an SQL trigger?

Answer:

An SQL trigger is a database object that is associated with a table and automatically executes a set of SQL statements when a specific event occurs on that table. Triggers are used to enforce business rules, maintain data integrity, and automate certain actions within a database. They can be triggered by various events, such as inserting, updating, or deleting data in a table, and they allow you to perform additional operations based on those events.

Q2: How do SQL triggers work? 

Answer: 

SQL triggers are defined using SQL statements and are associated with a specific table. When the defined trigger event (e.g., INSERT, UPDATE, DELETE) occurs on that table, the associated trigger code is executed automatically. The trigger code can consist of SQL statements that can manipulate data in the same or other tables, enforce constraints, or perform other actions. Triggers are executed within the transaction scope, and they can be defined to execute either before or after the triggering event.

Q3: What are the benefits of using SQL triggers? 

Answer: 

The benefits of using SQL triggers include:

Data integrity: Triggers allow you to enforce complex business rules and constraints at the database level, ensuring that data remains consistent and accurate.

Automation: Triggers can automate repetitive or complex tasks by executing predefined actions whenever a specified event occurs. This reduces the need for manual intervention and improves efficiency.

Audit trails: Triggers can be used to track changes made to data, such as logging modifications in a separate audit table. This helps in auditing and maintaining a history of data changes.

Data validation: Triggers can perform additional validation checks on data before it is inserted, updated, or deleted, ensuring that only valid and conforming data is stored in the database.



Similar Reads

SQL Trigger | Book Management Database
Prerequisite - SQL Trigger | Student Database For example, given Library Book Management database schema with Student database schema. In these databases, if any student borrows a book from library then the count of that specified book should be decremented. To do so, Suppose the schema with some data, mysql> select * from book_det; +-----+-----
2 min read
Capturing Deleted Rows in SQL Server Without Using a Trigger
Introduction : Working with triggers can be difficult and time-consuming at times. Triggers can slow down the server's output and place additional pressure on the system's resources. Many people use triggers to keep track of all the data that has been removed from tables. Instead of using triggers, we might use another process, as seen in the examp
2 min read
How to Find Average Marks of Each Student in SQL?
In SQL, sometimes we need to find the average value of a column based on another column of the table such as finding the student-wise average marks scored by him/her in all the subjects. This involves the use of the GROUP BY clause along with the AGGREGATE function like AVG. The same is depicted in the below article. For this article, we will be us
2 min read
Difference between Trigger and Procedure in DBMS
1. Procedures: A procedure is a combination of SQL statements written to perform specified tasks. It helps in code re-usability and saves time and lines of code. Advantages of Procedures: A Stored Procedure can be used as modular programming, which means that it can be created once, stored, and called multiple times as needed. This allows for speed
2 min read
Difference between Cursor and Trigger in DBMS
1. Cursor in PL/SQL : A cursor can be basically referred to as a pointer to the context area.Context area is a memory area that is created by Oracle when SQL statement is processed.The cursor is thus responsible for holding the rows that have been returned by a SQL statement.Thus the PL/SQL controls the context area by the help of cursor.An Active
3 min read
Check if Table, View, Trigger, etc present in Oracle
Sometimes while working in SQL we often forget the names of the view or sequence or index or synonyms or trigger we earlier created. Also it may happen that we want to verify them in future. Verifying means that we are checking for all the present database object or Trigger in that particular schema. This could be done for above all using the below
2 min read
Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
Structured Query Language (SQL): Structured Query Language (SQL) has a specific design motive for defining, accessing and changement of data. It is considered as non-procedural, In that case the important elements and its results are first specified without taking care of the how they are computed. It is implemented over the database which is drive
2 min read
Configure SQL Jobs in SQL Server using T-SQL
In this article, we will learn how to configure SQL jobs in SQL Server using T-SQL. Also, we will discuss the parameters of SQL jobs in SQL Server using T-SQL in detail. Let's discuss it one by one. Introduction :SQL Server Agent is a component used for database task automation. For Example, If we need to perform index maintenance on Production ser
7 min read
SQL vs NO SQL vs NEW SQL
SQL stands for Structured Query Language. Which is based on relational algebra and schema is fixed in this which means data is stored in the form of columns and tables. SQL follows ACID properties which means Atomicity, Consistency, Isolation, and Durability are maintained. There are three types of languages present in SQL : Data Definition Languag
2 min read
How to pre populate database in Android using SQLite Database
Introduction : Often, there is a need to initiate an Android app with an already existing database. This is called prepopulating a database. In this article, we will see how to pre-populate database in Android using SQLite Database. The database used in this example can be downloaded as Demo Database. To prepopulate a SQLite database in an Android
7 min read
Article Tags :