T-SQL TRIGGER
Introduction to Trigger
Why TRIGGER?
CREATE, ALTER, DROP and Fire TRIGGER
Types of TRIGGER
ENABLE and DISABLE TRIGGER
Daniel Tesfay
Introduction to Trigger
o A database trigger is a special kind of stored procedure or a stored set
of instructions that automatically execute when a specified event
occurs in a database management system (DBMS).
o These events can include actions like insertions, updates, or deletions
of data within a table.
Introduction to Trigger
o Database triggers provide a powerful mechanism for
automating, enforcing, and customizing database
behavior, enhancing the reliability, integrity, and
efficiency of database systems.
Why Trigger?
Enforcing Data Integrity
Automating Repetitive Tasks
Auditing and Logging
Cascade Operations
Customizing Behavior
Enforcing Business Rules
Notification and Alerts
Why Trigger?
o Enforcing Data Integrity:
• Triggers can be used to enforce data integrity rules, ensuring that only valid data is entered into the database.
• For example, a trigger can prevent the insertion of invalid data or enforce referential integrity constraints.
o Automating Repetitive Tasks
• Database triggers can automate repetitive tasks or business logic that need to be executed whenever certain data changes occur.
• This can help in reducing manual effort and ensuring consistency in data processing.
o Auditing and Logging
• Triggers can be utilized to track changes made to the database, providing an audit trail of who made the changes, when they were
made, and what changes were made.
• This is crucial for compliance, security, and troubleshooting purposes.
Why Trigger?
o Cascade Operations
• Triggers can be used to perform cascade operations,
• where changes made to one table can automatically trigger changes in related tables.
• For example, if a record is deleted from one table, triggers can be used to automatically delete
related records from other tables to maintain referential integrity.
o Customizing Behavior
• Triggers allow developers to customize the behavior of the database system based on specific
requirements or business logic.
• This enables the implementation of complex logic within the database itself, reducing the need for
application-level logic.
Why Trigger?
o Enforcing Business Rules
• Triggers can enforce business rules and policies at the database level, ensuring that these
rules are consistently applied regardless of how data is accessed or manipulated.
o Notification and Alerts
• Triggers can be used to generate notifications or alerts based on certain database events.
• For example, a trigger can send an email notification when a critical data change occurs.
DDL –CREATE / ALTER/ DROP TRIGGERS
o There are three types of triggers in SQL serve
₋ DML : execute when a user tries to modify data using
Insert, Update or Delete statements on a table or view
₋ DDL: execute when a user runs Create, Alter or Drop
statements
₋ Logon: fire in response to the LOGON event that is raised
when a user sessions is being established
CREATE TRIGGER Syntax
o DML Trigger Syntax
CREATE TRIGGER [schema_name.][trigger_name]
ON {table_name | view_name}
[AFTER|FOR|INSTEAD OF] {[INSERT][,][UPDATE][,] [DELETE]}
AS
BEGIN
{ sql_statement [ ; ] [ ,...n ] |
EXTERNAL NAME <method specifier [ ; ] > }
END;
TRIGGER Example To INSERT
CREATE OR ALTER TRIGGER trgAfterInsert
ON [Link]
AFTER INSERT
AS
BEGIN
INSERT INTO [Link](CustomerID, FirstName, LastName)
SELECT CustomerID, FirstName, LastName FROM INSERTED;
END;
o This trigger will be fired when we insert value to Customers table. So,
it will insert the selected values to tblCustomers table also.
Trigger Example
o The following DML trigger prevents a row from being inserted in the [Link]
table -- when the credit rating of the specified vendor is set to 5 (below average).
CREATE OR ALTER TRIGGER [Purchasing].[LowCredit]
ON [Purchasing].[PurchaseOrderHeader]
AFTER INSERT
AS
BEGIN
IF EXISTS (SELECT *
FROM [Link] AS p JOIN inserted AS i
ON [Link] = [Link] JOIN [Link] AS v
ON [Link] = [Link] WHERE [Link] = 5
)
BEGIN
RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
END;
DDL-Trigger
o DDL Trigger-Syntax
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS
{
sql_statement [ ; ] [ ,...n ] |
EXTERNAL NAME < method specifier > [ ; ]
}
DDL-TRIGGER Example
o TRIGGER Fired on Database Creation
USE MASTER
GO
CREATE OR ALTER TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
SELECT 'Database Created on' + ' ' + convert (nvarchar(17), GETDATE())
GO
Trigger Example for Database Mail
USE AdventureWorks2019;
GO
CREATE OR ALTER TRIGGER [Link]
ON [Link]
AFTER UPDATE
AS
BEGIN
-- Check if the PriceList column has been updated
IF UPDATE(ListPrice)
BEGIN
DECLARE @ProductName NVARCHAR(255);
SELECT @ProductName = Name FROM inserted;
-- Compose email message
DECLARE @Subject NVARCHAR(255);
SET @Subject = 'Price list update for product: ' + @ProductName;
DECLARE @Body NVARCHAR(MAX);
SET @Body = 'The price list for the product ' + @ProductName + ' has been updated by.'+ SESSION_USER;
--CURRENT_USER, --LOGIN, --SESSION_USER, --SYSTEM_USER
-- Send email using sp_send_dbmail
EXEC [Link].sp_send_dbmail
@profile_name = 'DABMailing',
@recipients = 'daniel.tesfay21@[Link]; dantech2024@[Link]',
@subject = @Subject,
@body = @Body;
END
END;
Steps to Database Mail
₋ Step 1: Enable Database Mail
₋ Step 2: Configure Database Mail Account
₋ Step 3: Create Database Mail Profile
₋ Step 4: Add the Account to the Profile
₋ Step 5: Grant Access to the Profile
₋ Step 6: Create Trigger for Database Mail.
SMTP Server, Port number and Mail Service
Mail Service SMTP Server Port Number
Hotmail [Link] 587
Gmail [Link] 587
Outlook [Link] 587
Authentication Method Selection
o To send emails through Gmail SMTP server we have to perform
some steps.
1. Enable Less Secure App Access (Not Recommended)
2. Using OAuth Authentication (Recommended)
• The configuration must be done from the email side and 'app password should be
Generated’
Email Configurations
o Step 1: Configure the OAuth consent screen from Google Cloud Consol use URL
₋ [Link]
o Add in the Gmail SMTP Scope :
₋ [Link]
o Step 2: Create OAuth 2.0 Credentials
₋ In the "Authorized redirect URIs" section, add the redirect URI
₋ [Link]
o Step 3: Generate an App Password
₋ Navigate to Google Account Setting → Security → app Passwords
o Step 4: Configure SQL Server Database Mail
Stored Procedure