SQL Server Triggers

SQL triggers are procedural code that is automatically executed in response to certain events in a specific table or database view. These events can also be INSERT, UPDATE, DELETE operations, or specific data-modification conditions.

 

Create SQL Trigger

The SQL CREATE TRIGGER statement is used to create SQL Triggers and the ALTER TRIGGER is used to update the created SQL TRIGGERs

Syntax-

USE Database_Name
GO

CREATE TRIGGER trg_Name ON [table_Name]/[view_Name]
BEFORE/AFTER INSERT/UPDATE/DELETE
AS
BEGIN
-- SQL statement here
END

 

Uses of SQL Triggers

Enforcing Business Rules

• Triggers can impose complex business rules and data integrity constraints that cannot be enforced using built-in constraints (such as foreign key and check constraints) provided by the database management system (DBMS).
• For example, if certain conditions are not met, the trigger can inhibit an INSERT or UPDATE operation, ensuring data integrity.

Auditing and Logging Changes

• Triggers are often used to track changes made to data in a database.
• They can log information about who made the change, what data was changed, when the change occurred, and provide a history of changes for compliance or troubleshooting.

Automating repetitive tasks

• Triggers can enable operations whenever a data change occurs.
• For example, if an order is canceled, the trigger can automatically update the relevant tables or send notifications to the relevant parties.

Maintaining Derived Data

• Triggers can automatically maintain derived data or summary information.
• For example, when a new invoice is entered, the trigger can update the total amount due to the customer based on all unpaid invoices.

Complex Integrity Constraints

• Triggers can impose complex integrity constraints with multiple tables or conditions.
• This may include cross-table validation or ensuring that specific conditions are met before data manipulation is allowed.

 

Components of SQL Triggers

Trigger Event A specific event that fires a trigger, such as an INSERT, UPDATE, or DELETE.
Trigger Condition (Optional) A condition that must be true for the trigger to work.
Trigger Action The code or actions executed when the trigger fires. This can be an SQL statement or a call to a stored procedure.

Example-

Creating an SQL Triggers

Here is an SQL Trigger created that inserts that data automatically into UserHistory table when deleted from Users table,

 

USE MyCollegeDb
GO

CREATE TRIGGER trg_UserHistory ON [Users]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;

-- SQL query for insert deleted data from Users table into UserHistory table
INSERT INTO UserHistory(UserID, UserName, UserContact, Email, DateOfBirth, UpdateDate)
SELECT UserID, UserName, UserContact, Email, DateOfBirth, GETDATE() FROM [deleted]

SET NOCOUNT OFF
END

In the example above-

trg_UserHistory is the name of the Trigger.

AFTER DELETE means that this trigger will automatically fire when any record/data will deleted from Users table

SET NOCOUNT ON/OFF is used to ignore the counting of affecting rows in the table by SQL statements.

Note: you can aslo add multiple events in a trigger like- INSERT, UPDATE, DELETE

Syntax-

USE Database_Name
GO

CREATE TRIGGER trg_Name ON [table_Name]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- SQL statement here
END

 

Where to See the Created Triggers

To see the created above trigger in the SQL Server Management System(SSMS), follow the given steps,

Open and Login into SSMS -> Expant the Database folder from the Object Explorer in the left window -> Your Database Name folder (MyCollegeDb) -> Table name on which you created the trigger -> Expand the Triggers folder -> here your created trigger on this table will appear.

 

SQL triggers are powerful tools for enforcing rules, managing changes, automating operations, and maintaining data integrity in database systems. They provide a way to extend DBMS functionality to meet specific application requirements that are not easily met by standard SQL implementations alone

 

Also, Read: Define the PIVOT Table with examples in SQL server