DbSchema | SQL Server - How to Create a Trigger?
Table of Contents
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:
Log historical dataEnforce business rules and data integrityReplicate dataPrevent invalid transactions- 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
- DbSchema Documentation
- Sqlcmd utility