(1) Triggers In SQL | Triggers In Database | SQL Triggers Tutorial For Beginners | Intellipaat
Introduction to SQL Server Triggers
Triggers are a mechanism in SQL Server that automatically execute in response to database events such as INSERT, UPDATE, or DELETE on tables.
Designed to maintain data integrity and automate specific database actions when events occur.
Agenda Overview
Definition of Triggers
Usage Scenarios for Triggers
Syntax for Creating Triggers
Example Trigger Implementation
Operations Involved with Triggers
Types of Triggers in SQL Server
Discussion of Before Insert and After Insert Triggers
Advantages and Disadvantages of Triggers
Conclusion and Additional Resources
What are Triggers?
SQL triggers are automatic SQL codes that run when specific events occur on a table, aimed at ensuring data integrity.
Analogy: Similar to a gun trigger that fires a bullet; it activates an action when conditions are met.
Hypothetical Example: A marketing representative sends a welcome message for each new customer added to a database. Triggers automate this process, managing responses to large data changes efficiently.
When to Use Triggers
Automatically perform actions based on specific conditions being met in the database.
Helpful in scenarios where updates to a frequently changing table need monitoring; can log changes or make alterations in other tables as needed.
Trigger Syntax
CREATE TRIGGER: Initiates the declaration of a trigger.
TRIGGER NAME: Unique identifier for the trigger.
BEFORE/AFTER: Specifies when the trigger should fire – either before or after the event (INSERT, UPDATE, DELETE).
INSERT, UPDATE, DELETE: Data Manipulation Language (DML) operations that can activate the trigger.
TABLE NAME: Designates which table the trigger applies to.
FOR EACH ROW/FOR EACH COLUMN: Determines whether the trigger acts on each row or specific columns.
TRIGGER BODY: Contains the SQL statements to be executed when the trigger fires.
Example Trigger Implementation
Example Trigger: Designed to calculate a student's percentage automatically when their information is updated in the database.
The triggered action is referred to by the keyword new, indicating the affected row.
Operations and Triggers
Possible actions include simple tasks or complex processes that require analytical decision-making through triggers.
Key commands include:
DROP TRIGGER: Used to remove a trigger by name.
SHOW TRIGGERS: Lists all active triggers in the database.
Types of SQL Server Triggers
Data Definition Language (DDL) Triggers:
Fired by DDL events (CREATE, ALTER, DROP).
Useful for monitoring schema changes and maintaining integrity against unauthorized modifications.
DML Triggers (Data Manipulation Language Triggers):
Triggered by DML actions (INSERT, UPDATE, DELETE).
Can be categorized as BEFORE & AFTER triggers.
Logon Triggers:
Activated during logon events, allowing for auditing of user login activities or restrictions based on specific criteria.
Before Insert and After Insert Triggers
BEFORE INSERT Trigger: Activated when data is inserted; can modify values before they enter a table.
AFTER INSERT Trigger: Activated after data is inserted; can perform operations based on the inserted data.
Advantages of Triggers
Enhances data security by enforcing additional checks before committing data changes.
Provides an automated method of validating data accuracy and preventing incorrect transactions.
Useful for handling failures at the database layer effectively.
Ideal for tracking data changes across tables effectively.
Disadvantages of Triggers
Triggers can only augment existing validations but not replace fundamental constraints (e.g., NOT NULL, UNIQUE).
May contribute to increased overhead on database performance.
Automated execution can create challenges in troubleshooting and debugging.
Conclusion
Triggers serve as powerful tools for automating database operations, ensuring integrity, and providing additional layers of verification.
Understanding the implications of triggers is vital to using them effectively and mitigating potential downsides.