SQL Trigger | Student Database - GeeksforGeeks (2024)

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.

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

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.

Disadvantageof 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.