DbSchema | SQL Server - How to Create a Trigger?

---

SQL Server • 01-Jul-2023

by Malik Umair Sajjad

---

Table of Contents

• Introduction
• Prerequisites
• What is a Trigger?
• Usage of Triggers
• Advantages and Limitations of Using a Trigger
• Restrictions on Using a Trigger
• Permissions Required for Using a Trigger
• Types of Triggers
• Difference Between INSTEAD OF and AFTER Triggers
• How to Create a Trigger in sqlcmd
• How to Create a Trigger in DbSchema
• Conclusion
• References

Introduction

This article aims to provide comprehensive details about SQL Server Triggers. It will cover what a trigger is, its usage, the advantages and limitations of using a trigger, the restrictions and permissions required, different types of triggers, and the difference between INSTEAD OF and AFTER triggers. The main focus will be on how to create a trigger using two different tools: sqlcmd and DbSchema.

Prerequisites

• Basic knowledge of SQL Server
• Familiarity with SQL commands
• Installed SQL Server Management Studio (SSMS)
• Installed DbSchema

For installation and establishing connection you can read our article SQL Server-How to create a database?

What is a Trigger?

In SQL Server, a trigger is a special type of stored procedure that automatically executes when an event occurs in the database server. Triggers are used to maintain the integrity of the data on the database.

Usage of Triggers

Triggers are commonly used to perform the following operations:

1. Log historical data
2. Enforce business rules and data integrity
3. Replicate data
4. Prevent invalid transactions
5. Maintain complex integrity constraints

Advantages and Limitations of Using a Trigger

Advantages	Limitations
Maintain data consistency	Hard to view the business logic because it’s encapsulated in the trigger
Perform complex checks of updates	Can lead to decreased performance
Can respond to data modifications automatically	Debugging can be difficult
Ensure complex business rules are enforced	Can cause unexpected side effects if not properly managed

Restrictions on Using a Trigger

Triggers must be defined on a table and can’t be defined on views. Also, the functionality of a trigger should not be coded to affect other objects in the database that have related triggers.

Permissions Required for Using a Trigger

To create a trigger, you require ALTER permission on the table or view on which the trigger is being defined. If the trigger is created on a schema or the database, then the CONTROL permission is required.

Types of Triggers

Trigger Type	Description
DML Triggers	Responds to Data Manipulation Language (DML) events
DDL Triggers	Responds to Data Definition Language (DDL) events
LOGON Triggers	Responds to LOGON events

Difference Between INSTEAD OF and AFTER Triggers

	INSTEAD OF	AFTER
Definition	Defined on a table or view and replaces the triggering event	Defined on a table to respond to a triggering event
When it Fires	Fires instead of the triggering event	Fires after the triggering event
Where Used	Commonly used for views	Commonly used for tables

How to Create a Trigger in sqlcmd

sqlcmd is a command-line utility that comes with SQL Server. You can interact with your database by writing SQL queries directly in your terminal.

Syntax

CREATE TRIGGER trigger_name
ON table_name
FOR/AFTER/INSTEAD OF [INSERT/UPDATE/DELETE]
AS
BEGIN
   -- Trigger body
END;

Sample Database:

we will consider an initial Employees table and an initial LogTable. The Employees table and LogTable are represented as follows:

Employees table:

EmpID	**Name**	_Department_	_Salary_
1	Alice	HR	5000
2	Bob	IT	6000

LogTable table:

LogID	LogInfo
1	Record Updated
2	Record Deleted

DML Trigger

Step 1: Connect to your SQL Server instance with the following command:

sqlcmd -S <server_name> -U <username> -P <password>

Replace server name with your server’s name , username with your username and password with your password.

Step 2: Use the database where you want to create the trigger:

USE <database_name>;
GO

Replace with your database’s name

Step 3: Write a query to create a DML Trigger. For instance, to create an AFTER INSERT trigger:

CREATE TRIGGER tr_AfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO LogTable (LogInfo)
    VALUES ('New record inserted into Employees table');
END;
GO

In the above query, tr_AfterInsert is a trigger which fires after an INSERT operation on the Employees table.

Results from the Query:

Following result will be obtained by executing the above query on our sample database:

LogID	LogInfo
1	Record Updated
2	Record Deleted
3	New record inserted into Employees table

DDL Trigger

For creating a DDL trigger that logs all CREATE_TABLE operations in the database, follow the same first two steps to connect to your server and select your database. Then, write your DDL trigger creation query:

CREATE TRIGGER tr_DDL
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    INSERT INTO DDLLogTable (Event)
    VALUES ('CREATE_TABLE event has occurred');
END;
GO

Sample Database:

Sure, let’s assume that initially the DDLLogTable is as follows:

DDLLogTable:

EventID	Event
1	Database Created
2	Table Deleted

Results from the Query:

Following result will be obtained by executing the above query on our sample database:

DDLLogTable:

_EventID_	_Event_
1	Database Created
2	Table Deleted
3	CREATE_TABLE event has occurred

This shows that the trigger tr_DDL successfully captured the CREATE TABLE operation and logged it into the DDLLogTable.

LOGON Trigger

Similarly, for creating a LOGON trigger that logs all successful logins to your server:

CREATE TRIGGER tr_Logon
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    INSERT INTO LogonLogTable (Event)
    VALUES ('Successful login');
END;
GO

Sample Database:

Let’s assume we have an initial LogonLogTable that looks like this:

LogonLogTable:

LogID	Event	_EventTime_
1	Unsuccessful login	2023-07-01 08:00:00
2	Successful login	2023-07-01 09:00:00

Results from the Query:

Following result will be obtained by executing the above query on our sample database:

After a successful login, the LogonLogTable will look like this:

LogonLogTable:

LogID	Event	EventTime
1	Unsuccessful login	2023-07-01 08:00:00
2	Successful login	2023-07-01 09:00:00
3	Successful login	2023-07-01 10:00:00

The tr_Logon trigger has successfully captured the successful login event and logged it into the LogonLogTable.

How to Create a Trigger in DbSchema

DbSchema is a visual database designer and management tool that allows you to interact with your database in a more user-friendly manner.

DML Trigger

Step 1: Open DbSchema and connect to your database.

Step 2: In the Schemas panel, select the table where you want to add a trigger.

Step 3: Right-click on the table and select Create Trigger.

Step 4: In the dialog box, provide the necessary details (name, timing, event).

Step 5: Write the SQL statements in the Trigger Code editor:

BEGIN
    INSERT INTO LogTable (LogInfo)
    VALUES ('New record inserted into Employees table');
END

Step 6: Click Apply.

DDL Trigger

In DbSchema, creating DDL triggers follows a similar process, but you’ll need to select Schema or Database in the Create Trigger dialog box. Write your SQL in the editor:

BEGIN
    INSERT INTO DDLLogTable (Event)
    VALUES ('CREATE_TABLE event has occurred');
END

LOGON Trigger

As of the last update in 2021, DbSchema does not natively support the creation of LOGON triggers through its GUI. However, you can still execute a SQL command to create LOGON triggers within DbSchema’s SQL editor, similar to sqlcmd:

CREATE TRIGGER tr_Logon
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    INSERT INTO LogonLogTable (Event)
    VALUES ('Successful login');
END;

And there you have it! You’ve successfully created DML, DDL, and LOGON triggers using sqlcmd and DbSchema. Please note that you must have the appropriate permissions to create and manage triggers in SQL

Visually Manage SQL Server using DbSchema

DbSchema is a SQL Server client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

Conclusion

Triggers play a crucial role in enforcing business rules and maintaining data integrity in SQL Server databases. Despite their advantages, triggers must be used judiciously due to their potential impact on database performance and complexity.

References

1. DbSchema Documentation
2. Sqlcmd utility