Background
Break News
How to add local font to Tailwind Css and NextJS? - Tutorial Design Pattern? - Blockchain Technology, How to create own Bitcoin virtual currency - Zustand mordern management state - Design Pattern - Flyweight Pattern? - Docker Full training Topic

[Tips] How to "Monitor Data Changes in SQL Server Tables with Triggers"

Monday 7 August 2023
|
Read: Completed in minutes

[Tips] How to "Monitor Data Changes in SQL Server Tables with Triggers"

SQL Server is a powerful and popular relational database management system that supports various data operations. However, sometimes you may want to keep track of the changes made to your data, such as when, who, and what was inserted, updated, or deleted in a table. This can be useful for auditing, debugging, or reporting purposes.

One way to achieve this is by using triggers, which are special stored procedures that execute automatically in response to certain events. In this article, we will show you how to create and use triggers to monitor data changes in SQL Server tables.

There are three types of triggers in SQL Server: DML, DDL, and Logon triggers. DML triggers fire when an INSERT, UPDATE, or DELETE statement affects a table or view. DDL triggers fire when a CREATE, ALTER, or DROP statement affects a database object. Logon triggers fire when a user logs on to the database.

To create a trigger, you need to use the CREATE TRIGGER statement, which has the following syntax:

How to "Monitor Data Changes in SQL Server Tables with Triggers"
CREATE TRIGGER trigger_name
ON table_name
AFTER | INSTEAD OF {INSERT | UPDATE | DELETE}
AS
{sql_statements}
The trigger_name is the name of the trigger, which must be unique within the database. The table_name is the name of the table or view that the trigger is associated with. The AFTER keyword specifies that the trigger should execute after the triggering action, while the INSTEAD OF keyword specifies that the trigger should execute instead of the triggering action. The INSERT, UPDATE, and DELETE keywords specify the type of data operation that activates the trigger. The sql_statements are the statements that define the logic of the trigger.

For example, suppose we have a table called Employees with the following columns: EmployeeID, FirstName, LastName, and Salary. We want to create a trigger that logs every change made to this table in another table called Employees_Audit with the following columns: AuditID, EmployeeID, Action, OldSalary, NewSalary, and DateTime.

We can use the following code to create the DML trigger:

First you need create one table for stored information, I create one Table with fields: ID (int), TableName (varchar-100), Activity (varchar-20). Date_Time (datetime - get current time)
- ID: primary key
- TableName: what's table will be audit
- Activity: Insert / update / delete
- Data_Time: stored current time when one action active to this table
       
CREATE TRIGGER Employees_Trigger
ON Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- Declare variables
DECLARE @AuditID int;
DECLARE @EmployeeID int;
DECLARE @Action varchar(10);
DECLARE @OldSalary decimal(18,2);
DECLARE @NewSalary decimal(18,2);
DECLARE @DateTime datetime;

-- Get the current date and time
SET @DateTime = GETDATE();

-- Check if it is an INSERT action
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
BEGIN
-- Set the action to 'INSERT'
SET @Action = 'INSERT';

-- Get the inserted employee ID and salary
SELECT @EmployeeID = EmployeeID,
@NewSalary = Salary
FROM inserted;

-- Set the old salary to null
SET @OldSalary = NULL;

-- Insert a record into the audit table
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END

-- Check if it is an UPDATE action
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
-- Set the action to 'UPDATE'
SET @Action = 'UPDATE';

-- Get the updated employee ID and salaries
SELECT @EmployeeID = i.EmployeeID, @OldSalary = d.Salary, @NewSalary = i.Salary
FROM inserted i
INNER JOIN deleted d
ON i.EmployeeID = d.EmployeeID;

-- Insert a record into the audit table
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END

-- Check if it is a DELETE action
IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
-- Set the action to 'DELETE'
SET @Action = 'DELETE';

-- Get the deleted employee ID and salary
SELECT @EmployeeID = EmployeeID, @OldSalary = Salary
FROM deleted;

-- Set the new salary to null
SET @NewSalary = NULL;

-- Insert a record into the audit table
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END

END; 
Now, whenever we insert, update, or delete a record in the Employees table, the trigger will insert a corresponding record in the Employees_Audit table with the relevant information.

To test the trigger, we can use the following statements:
       
-- Insert a new employee
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (101, 'John', 'Doe', 50000);

-- Update an existing employee's salary
UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 101;

-- Delete an existing employee
DELETE FROM Employees
WHERE EmployeeID = 101;
      
The result of these statements can be seen in the Employees_Audit table:
       

| AuditID | EmployeeID | Action | OldSalary | NewSalary | DateTime |
| ------- | ---------- | ------ | --------- | --------- | -------- |
| 1       | 101        | INSERT | NULL      | 50000.00  | 2023-08-07 12:20:15.123 |
| 2       | 101        | UPDATE | 50000.00  | 60000.00  | 2023-08-07 12:21:23.456 |
| 3       | 101        | DELETE | 60000.00  | NULL      | 2023-08-07 12:22:34.789 |
	


As you can see, the trigger has successfully logged every change made to the Employees table.

In conclusion, triggers are a useful feature of SQL Server that allow you to monitor data changes in your tables. You can use them to perform various tasks, such as auditing, debugging, or reporting. However, you should also be careful when using triggers, as they can affect the performance and integrity of your database if not designed and tested properly.


#SQLServer #Triggers #DataChanges #DML #DDL #Logon #Audit #Debug #Report #Insert #Update #Delete



Another Example:



Track Insert Update Delete on table


Next, create one trigger with structure

--------------------------------------------------------
CREATE TRIGGER trigger_name
      ON table_name
      AFTER [Update] , [Insert], [Delete]
AS
BEGIN
     ...
END
--------------------------------------------------------


inserted is a template table when user insert any row to the table, will be store it
deleted is a template table when user delete any row to the table, will be store it


Track Insert Update Delete on table


 


Thank you for reading this post. I hope you found it helpful and easy to follow. If you have any feedback or questions about How to "Monitor Data Changes in SQL Server Tables with Triggers" , please share them in the comments below. I would love to hear from you and discuss this topic further
✋✋✋✋  Webzone Tech Tips Zidane, all things tech tips web development  - I am Zidane, See you next time soon ✋✋✋✋


🙇🏼🙇🏼 Your Feedback Is Valuable and Helpful to Us - Webzone, all things Tech Tips web development Zidane
Popular Webzone Tech Tips topic maybe you will be like it - by Webzone Tech Tips - Zidane
As a student, I found Blogspot very useful when I joined in 2014. I have been a developer for years . To give back and share what I learned, I started Webzone, a blog with tech tips. You can also search for tech tips zidane on Google and find my helpful posts. Love you all,

I am glad you visited my blog. I hope you find it useful for learning tech tips and webzone tricks. If you have any technical issues, feel free to browse my posts and see if they can help you solve them. You can also leave a comment or contact me if you need more assistance. Here is my blog address: https://learn-tech-tips.blogspot.com.

My blog where I share my passion for web development, webzone design, and tech tips. You will find tutorials on how to build websites from scratch, using hot trends frameworks like nestjs, nextjs, cakephp, devops, docker, and more. You will also learn how to fix common bugs on development, like a mini stackoverflow. Plus, you will discover how to easily learn programming languages such as PHP (CAKEPHP, LARAVEL), C#, C++, Web(HTML, CSS, javascript), and other useful things like Office (Excel, Photoshop). I hope you enjoy my blog and find it helpful for your projects. :)

Thanks and Best Regards!
Follow me on Tiktok @learntechtips and send me a direct message. I will be happy to chat with you.
Webzone - Zidane (huuvi168@gmail.com)
I'm developer, I like code, I like to learn new technology and want to be friend with people for learn each other
I'm a developer who loves coding, learning new technologies, and making friends with people who share the same passion. I have been a full stack developer since 2015, with more than years of experience in web development.
Copyright @2022(November) Version 1.0.0 - By Webzone, all things Tech Tips for Web Development Zidane
https://learn-tech-tips.blogspot.com