Unlocking the Power of Triggers in SQL Server

1
Unlocking the Power of Triggers in
SQL Server
FOR MORE INFORMATION https//nareshit.com/courses/
sql-server-online-training
support_at_nareshit.com
2
Title Slide
Introduction to Triggers Types of Triggers in SQL
Server DML Triggers DDL Triggers Logon
Triggers How to Create a Trigger Trigger
Example Benefits Drawbacks of
Triggers Conclusion
Agenda
3
Title Slide

• "Understanding Triggers in SQL Server"
• Sets the focus on explaining the concept of
  triggers in SQL Server.
• "A Comprehensive Overview"
• Indicates a detailed exploration of the topic.
• Your Name / Date
• Add your name and the presentation date for
  professionalism.
• Example John Doe October 2, 2024.
• Design
• Simple, professional background with bold fonts,
  and optionally a small database icon or SQL
  Server logo.

4
Introduction to Triggers
Definition A trigger is a special type of stored
procedure that automatically executes when an
event occurs in the database.
INSERT
DELETE
Events
, UPDATE ,
Purpose To enforce business rules, automate
tasks, and maintain data integrity.
5
Types of Triggers in SQL Server
INSERT
1. DML Triggers Triggered by
, UPDATE, or DELETE.
ALTER
DROP
2. DDL Triggers Triggered by CREATE,
,
3. Logon Triggers Triggered by user logon
events. Visual representation of the types.
6
DML Triggers
AFTER Trigger Fires after the operation INSERT ,
UPDATE , DELETE
INSERT
UPDATE
INSTEAD OF Trigger Fires instead of the operation
DELETE
Example use cases Data validation, logging,
cascading update
7
DDL Triggers
Fires on changes To schema objects like tables,
views, stored procedures, etc. Use Case
Preventing schema changes or auditing them.
Example "Prevent a user from dropping a table."
https//nareshit.com/courses/sql-server-online-tra
ining
8
Logon Triggers
Fires on user login attempts. Use Case Restrict
logins based on time, IP address, or security
rules. Example Blocking login attempts during
maintenance windows.
https//nareshit.com/courses/sql-server-online-tra
ining
9
How to Create a Trigger
Syntax
CREATE TRIGGER trigger_name ON table_name AFTER
INSERT, UPDATE,DELETE AS BEGIN -- Your SQL code
here END
AFTER INSERT
Example of a simple
trigger.
10
Trigger Example
Customers
Scenario Log changes in a
table
CREATE TRIGGER trg_AfterInsert ON
Customers AFTER INSERT AS BEGIN -- INSERT INTO Cu
stomerLog LogDate)
(CustomerID,
SELECT CustomerID, GETDATE() FROM inserted END
Walk through the code and explain each part.
11
Benefits Drawbacks of Triggers
Benefits Automates repetitive tasks. Enforces
complex business rules. Maintains data
integrity. Drawbacks Hidden logic, making
debugging difficult. Performance overhead if
overused. Potential for unintended consequences.
12
Conclusion
Summary Triggers are powerful for automating
tasks and enforcing data rules. They should be
used carefully to avoid performance issues. Best
Practice Keep triggers simple and
well-documented.
https//nareshit.com/courses/sql-server-online-tra
ining
13
Get In Touch
Email support_at_nareshit.com Social
Media _at_nareshitech Call us 8179191999
14
Thank you
https//nareshit.com/courses/sql-server-online-tra
ining
nareshiTech