Triggers in SQL Server: Unlocking the Power of Automation

Introduction
Triggers are essential in the field of database management for automating processes and enforcing business rules. Understanding triggers is essential for effective and reliable database operations, regardless of your experience level with SQL Server development or where you are at in your career. I'll go into the idea of triggers in SQL Server, look at their features, and discover how they can improve your database management experience in this blog post.

What are Triggers?
An automatic response to certain events, such as data alterations (inserts, updates, or deletes) occurring on a particular table, is known as a trigger in SQL Server. When these events happen, triggers give you a mechanism to carry out a series of specified actions, enabling you to uphold data integrity and enforce complicated business rules.

Types of Triggers

SQL Server supports two main types of triggers: "After" triggers and "Instead of" triggers. But I will be discussing "After" trigger as it is the most useful as compared to other one.

After Trigger
These triggers fire after the triggering event has occurred and is completed. After triggers are commonly used to audit changes, update related tables, or perform calculations based on the modified data.
Let's dive into the example.

• Connect SQL Server with LocalDB

(https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5uyexo8gih06lupyexpm.png)

• Store table is created with store id, created date, store name, and phone column by selecting one database.

(https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pktwymzqygrh4eyuhg2r.png)

• Another table named as Triggered table is created for the value insertion after user insert new and unique row in the store table.

(https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9i6gtiuikr8ekecs1190.png)

• Let's write the trigger for inserting each unique row in the new trigger table after any row insertion in store table.

CREATE TRIGGER dbo.fetch_data
   ON  dbo.store
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @store_id int
    declare @store_name varchar(max)
    declare @phone varchar(max)

    select @store_id=[store_id] from inserted;
    select @store_name = [store_name] from inserted;
    select @phone=[phone] from inserted;

   if (
    @_store_name _in (select store_name from trigger_table)
    and @phone in (select phone from trigger_table))
    BEGIN
        rollback
        RAISERROR ('Same store name with same date and phone already exist', 16, 1);
    END
    ELSE
    BEGIN
        insert into  dbo.trigger_table (store_id,inserted_date,store_name,phone)
        values (@store_id,GETDATE(),@store_name,@phone)
    END

END
GO

• Inserting a row into the store table

(https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gplfoilq1atofwxy5jbe.png)

(https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0om99lfpbrw4llmy2nrp.png)

(https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uviv1sfeyonrhheniqct.png)

• Now let's check the trigger table

(https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5lnnmo727haw3p9o602i.png)
Same field is inserted in the trigger table with the current date.
Wow, it automatically knew any new insertion and insert the exact same data in another table without any manual process.

• Let's try to insert duplicate data in store table

(https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gwpmpj4m5c3fevf8yk8u.png)
During the duplicate store name and phone insertion, the trigger executes and display the error message regarding duplicate entry and the query is rollback. As a result the duplicate data won't get inserted neither in the store table nor in the trigger table.

Advantages of Triggers
Data Integrity: Triggers protect your database by making sure that only accurate and reliable data is kept inside. Triggers assist in preserving the quality and correctness of your data by verifying input, carrying out referential integrity checks, or enforcing complicated business rules.

Automation: Triggers eliminate the need for manual intervention by automating repetitive operations. You can save time and effort by using them to automatically update related tables, produce derived data, send notifications, or keep audit trails.

Scalability: By enclosing sophisticated logic within the database itself, triggers can improve the scalability of your database system. This lessens the requirement for recurrent code execution in various application layers and encourages the development of an architecture that is more effective and simplified.

Conclusion
An essential part of SQL Server are triggers, which let you automate processes and enforce business rules inside of your database. You may improve data integrity, automate repetitive procedures, increase scalability, and guarantee consistency in your database administration processes by utilizing triggers efficiently. To get the most of your triggers, follow best practices, properly test them, and record how they work. You'll unlock the power of automation and simplify your SQL Server workflows if you have a firm grasp of triggers.