Triggers Insight: Unlocking the Power of SQL Triggers

What are TRIGGERS?

In SQL Server, triggers are database objects, a special kind of stored procedure that “reacts” to certain actions we make in the database. The main idea behind triggers is that they always perform an action in case some event happens.

Triggers are the SQL codes that are automatically executed in the order of occurrence of any event. These are used to maintain the integrity of the data.

It will execute if any of the operations is performed in the database. It might be: DML - Data Manipulation Language (INSERT, UPDATE, DELETE) DDL - Data Definition Language (CREATE, ALTER, DROP) LogOn - It is executed when a logon event occurs.

Now let's look at Basic Examples of Triggers: 

REAL-LIFE EXAMPLE: When watching an emotional scene in a movie, a kid breaks down crying, meaning a trigger was executed at that particular time. When a person was walking in a forest and as soon as he saw a tiger chasing him he started running fast which means a trigger was then executed.

PRACTICAL EXAMPLE: Applying a trigger in the database to send a confirmation notification to all the passengers on a flight for booking his/her ticket with them.

Classification of triggers: 

Row Level Triggers Row-level triggers execute once for every row in the transaction.    Specifically, used for data auditing purposes.  The “FOR EACH ROW” clause is present in the CREATE TRIGGER command.  Example: If 2000 rows are to be inserted into a table, the row level trigger will execute 2000 times.

Statement Level Triggers Statement-level triggers executes only once for each single transaction. Used for enforcing all additional security on the transactions performed on the table. The “FOR EACH STATEMENT” clause is omitted in the CREATE TRIGGER command. Example: If 2000 rows are to be inserted into a table, the statement level trigger will execute only once.

 SQL Trigger Usages: 

We typically use the triggers in the following scenarios:

• Log table modifications. Some tables have sensitive data such as customer email, employee salary, etc., that you want to log all the changes. In this case, you can create the UPDATE trigger to insert the changes into a separate table.

• Enforce complex integrity of data. In this scenario, you may define triggers to validate the data and reformat the data if necessary. For example, you can transform the data before insert or update using a BEFORE INSERT or BEFORE UPDATE trigger.

 DML Trigger Syntax:

 The simplified SQL syntax to define the trigger is as follows:

CREATE trigger[trigger_name]

[before|after]

{insert|update|delete}

On [table_name]

[for each row|for each column]

[trigger_body]

Explanation of Syntax:

• Create trigger[trigger_name]: Creates or replaces an existing trigger with the trigger_name.

• [before|after]: This specifies  when the trigger will be executed

• {insert | update |delete}: This specifies the DML operation

• On [table_name]: This specifies the name of the table associated with the trigger

• [for each row]: This specifies a row level trigger, i.e.,the trigger will be executed for each affected row.

• [trigger_body]: This provides the operation to be performed as the trigger is performed as the trigger is fired.

  
  

• BEFORE INSERT Trigger: 

A BEFORE INSERT trigger is activated before an INSERT event occurs on a table 

 EXAMPLES: 

CREATE TABLE Student(studentID INT NOT NULL AUTO_INCREMENT,  FName VARCHAR(20), LName VARCHAR(20), Address VARCHAR(30), City VARCHAR(15), Marks INT, PRIMARY KEY (studentID) );

After Insert Trigger: 

An AFTER INSERT trigger is a trigger that is fired after an INSERT event occurs on a table.

The AFTER INSERT trigger can access the newly inserted data using the NEW record variable. This NEW variable allows to access the values of columns in the inserted row:

AFTER INSERT triggers for logging changes, updating related tables, or sending notifications based on the inserted

 Before Update and after update Triggers:

A BEFORE UPDATE trigger activates before an UPDATE operation is applied to a table.

These BEFORE UPDATE triggers can be particularly useful when we  want to modify data before an update occurs or enforce certain conditions.

An AFTER UPDATE trigger is a type of trigger that fires after an UPDATE operation is completed successfully on a table.

Because the AFTER UPDATE triggers can access the row after the update, we can perform tasks such as logging changes, updating data in related tables, or sending notifications based on the modified data.

 Before Delete and after Delete :

BEFORE DELETE trigger is activated before one or more rows are deleted from a table.

In practice, we use BEFORE DELETE triggers for tasks such as logging deleted data, updating data in related tables, or enforcing complex business rules.

BEFORE DELETE trigger is activated before one or more rows are deleted from a table.

In practice, we use BEFORE DELETE triggers for tasks such as logging deleted data, updating data in related tables, or enforcing complex business rules.

AFTER DELETE trigger is activated after one or more rows are deleted from a table.

An AFTER DELETE trigger can be particularly useful in some scenarios such as logging deleted data, updating data in related tables, or enforcing complex business rule

 Instead of Trigger:

INSTEAD OF triggers are a special type of triggers that intercept insert, update, and delete operations on views.

It means that when you execute an INSERT, UPDATE, or DELETE statement on a view, PostgreSQL does not directly execute the statement. Instead, it executes the statements defined in the INSTEAD OF trigger

Before Truncate Triggers:

PostgreSQL allows you to create a trigger that fires before a TRUNCATE event occurs.

BEFORE TRUNCATE trigger is a statement-level trigger because the TRUNCATE statement deletes all the rows from the table, not individual rows.

Although the TRUNCATE operation deletes rows from a table, it does not activate the DELETE trigger including BEFORE and AFTER DELETE triggers.

 Disable Triggers: 

To Disable / Deactivate Triggers of a table – Use ‘ALTER TABLE…DISABLE TRIGGER’

Syntax:

To Disable a Single Trigger:

ALTER TABLE table_name

DISABLE TRIGGER trigger_name;

To Disable all Triggers of a table:

ALTER TABLE table_name

DISABLE TRIGGER ALL;

Note: When a Trigger is disabled, it remains in the database but it won’t activate when an event associated with the trigger occurs

 To list out all the Triggers in the table: 

Syntax:

SELECT event_object_table AS table_name,

Trigger_name FROM information_schema.triggers

WHERE event_object_table = ‘table_name’

GROUP BY table_name, trigger_name

ORDER BY table_name, trigger_name;

 Advantages of Triggers:

To automate the execution of the code when an event occurs

We can enforce data integrity

Data is validated using triggers, before being inserted or updated

Triggers assist us in maintaining a records log

Trigger maintenance is simple

Database object rules are established by triggers, which cause changes to be undone if they are not met.

Disadvantages of Triggers:

Triggers can increase the execution time of the original statement, hence can increase the overall performance or run time of a Query, especially in DML queries

Triggers require changes to the Database schema, hence, throwing complexity and maintenance challenges

Triggers can be hard to debug, especially for new developers

Triggers come with a risk of inconsistencies due to redundancy, impacting performance and visibility

Note: The Decision to use Triggers should be based on specific use-case and trade-off between automation and performance challenges

 When to Use SQL Triggers?

Triggers share a lot in common with stored procedures. Still, compared to stored procedures they are limited in what you can do. Therefore, I prefer to have one stored procedure for insert/update/delete and make all checks and additional actions there.

Still, that is not always the option. If you inherited a system or you simply don’t want to put all the logic in the stored procedures, then triggers could a solution for many problems you might have.

 Conclusion:

In conclusion, SQL triggers are powerful tools that can greatly enhance data integrity, automate workflows, and enforce business rules within your database. By understanding the different types of triggers and their appropriate use cases, you can leverage them to streamline operations and maintain a high level of data quality. However, it’s crucial to implement them thoughtfully to avoid potential pitfalls such as performance issues and complexity. As you continue your journey with SQL, remember that mastering triggers can significantly impact your database management skills, making your applications more efficient and reliable.

Stay curious and keep exploring the vast world of SQL!

Thank you for Reading my Blog!