How I came across SQL Triggers?

Recently, I was questioned about SQL Triggers in an interview and I was totally unaware of this term. So, obviously, after the interview, I learned about them. And did some practice. After learning about triggers, I came to know that this is a very nice solution to one of my biggest problems of keeping data at every cost in some specific scenarios.

Let's discuss the problem and then we will have the triggers in the form of a solution.

Problem: To save data which user deletes from his end

In the company where I work, we started developing web applications for accounting purposes of different industries after seeing a lot of potential in this field. At the very start, a client asked us to restore some of the deleted data but we were unable to do so. Because we were not keeping any backup or anything.

What we did after this problem:

We placed a status field in all the necessary tables and then started changing that field to delete status. This forced us to apply filters in all the application against this field to retrieve only that data that was not deleted. In the end, It was a total mess and was not a fine thing. But we had to keep it that way to fulfill the client's demand as the application is in the initial process of development.

Solution to the problem:

And here comes the SQL triggers for my help. After the interview, I learned about them and studied them completely. I did some practice on the SQL server and after applying them practically in one of my own application, I was like, "OH MY GOD? I should have learned it months ago" .

Now I can easily keep my deleted records with the help of SQL triggers. Let's see how.

What is a SQL Trigger:

It is a database object, which automatically invokes whenever a special event in the database occurs.

It is basically a set of stored SQL statements in SQL Server which gets invoked whenever a special event happens in the database. SQL trigger is also called a special stored procedure.

What are those special events which fire a trigger in SQL Server?

• INSERT, UPDATE or DELETE events on tables.
• CREATE, ALTER or DROP events on the database
• LOGON events

So, keeping in view the above events, we have the following types of triggers available to use in SQL Server.

Types of triggers:

1. Data Manipulation Language Triggers (INSERT, UPDATE, or DELETE)
2. Data Definition Language Triggers (CREATE, ALTER, or UPDATE)
3. LOGON Trigger

(Logon trigger fires whenever a user session is established with SQL Server Instance, I have not tried this trigger yet)

So, after learning the triggers, I prepared a solution to my problem of saving data after the deletion or keeping a record of insertions through triggers.

Below is the code which I wrote to fire a trigger on my table cities whenever an insertion or deletion happens.

Cities table has an Id, Code, and Name.

Step 1:

I created a new table to keep a record of the changes made in cities.

CREATE TABLE cities_history(
    id INT IDENTITY PRIMARY KEY,
    city_id INT NOT NULL,
    code VARCHAR(20),
    city_name VARCHAR(50) NOT NULL,
    updated_at DATETIME NOT NULL,
    operation CHAR(1) NOT NULL,
    CHECK(operation = 'I' or operation='D')
);

Step 2:

Now creating the trigger using its syntax on table cities, so that whenever an INSERTION or DELETE operations happen, a new record is inserted into the new table cities_history.

Create Trigger trg_cities 
on Cities 
AFTER INSERT, DELETE 
AS 
Begin 

SET NOCOUNT ON 

INSERT INTO cities_audit (
city_id, 
code, 
city_name, 
updated_at,
operation
)

Select 
i.Id, 
Code, 
Name, 
GETDATE(),
'INS'

FROM inserted i 

UNION ALL 

Select 
d.Id, 
Code, 
Name, 
GETDATE(),
'DEL'

from deleted d 

END

When I inserted a row in the cities table, the above trigger fired and inserted a row in cities_history. The same thing happened when I deleted a record from the cities table.

👉 I tried to explain the usage of triggers through a simple use case that I faced and I have only written what I have learned so far about triggers. :)

Thank you for reading, Will love to hear your valuable feedback.

If anything is confusing or incorrect then let me know in the comment section. Thanks from my side, this is Usama, keep learning and exploring !!

See you in the next article!