Database: Triggers

Triggers in databases are special stored procedures that are automatically executed or fired when certain events occur in a database. These events can include inserting, updating, or deleting records in a table. Triggers are used to enforce business rules, perform complex calculations, maintain data integrity, and automate tasks.

Syntax of Triggers

A trigger is defined using the following syntax:

CREATE [OR REPLACE] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE} 
ON table_name 
[FOR EACH ROW]
BEGIN
-- trigger logic here
END;

Example of a Trigger

Let's consider a simple example to understand triggers better. Suppose we have a table called 'employees' with columns 'emp_id' and 'salary'. We want to create a trigger that automatically updates the salary of an employee if their salary exceeds a certain amount.

CREATE OR REPLACE TRIGGER update_salary_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary > 100000 THEN
  :NEW.salary := 100000;
END IF;
END;

In this trigger, we are checking if the new salary being inserted is greater than 100000. If it is, we are setting the salary to 100000.

Common Use Cases

Triggers are commonly used in the following scenarios:

• Enforcing data integrity constraints
• Auditing changes to data
• Automating tasks such as sending notifications

Importance in Interviews

Triggers are a fundamental concept in databases and are often asked about in interviews for database-related roles. Understanding triggers demonstrates a strong understanding of database concepts and the ability to automate tasks and maintain data integrity.

Conclusion

Triggers are a powerful feature in databases that allow you to automate tasks, enforce business rules, and maintain data integrity. By understanding triggers and their syntax, you can enhance the functionality of your database and streamline your workflows.

Tags

Database, Triggers, SQL, Stored Procedures, Data Integrity