DbSchema Database Designer

DbSchema | SQL Server - How to Create a Trigger?

Publish on DbSchema Blog >>>

SQL Server alt >

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:

  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

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.


DbSchema can be downloaded for free. No registration is required.