Method of Trigger (Triggering) and Advanced Logging (Recording) in SQL Server

[Lightify]

In this article, we will examine the movements related to the past of the data. Of course, to get to these steps, we need to learn the trigger concept. Undoubtedly, one of the most valuable things in the digital world is the "Data Concept." Data loss is one of the worst parts of this job. At this point, the systems that store the data are primarily databases. Databases come to our rescue at this point, but you may wonder what they offer us in terms of data exchange. At this point, one of the methods in advanced database systems is the Log (Recording) process. Put simply, you can think of it as a paper system where I keep the information of the products in my store such as product name, brand, dimensions, color, etc. In terms of functionality, the color of a product that comes in has been recorded as "white," but then let's say we made a mistake and entered it as blue in the system. So, how will I know the color of this product before the wrong entry, considering the product is blue now? Another example is, let's say we accidentally delete a product in the database. How will I access the information of this completely deleted product? Let's shape this example to get answers to these questions. Products Table CREATE TABLE tblProducts ( product_ID INT PRIMARY KEY NOT NULL IDENTITY(1,1), product_name VARCHAR(100), product_code VARCHAR(100), color VARCHAR(10), brand VARCHAR(100), origin VARCHAR(100), model VARCHAR(100), added_date DATETIME NOT NULL DEFAULT (getdate()), status TINYINT DEFAULT 0 ) tblProducts Table Our table is created. Now, let's create another copy table. Let's name it as tblProducts_Log. But there are a few details that we need to add a few more columns to our table. The reason for this is so that when we log the operation, we also save the information about when and by whom that operation was performed. Products Log Table CREATE TABLE tblProducts_Log ( ID INT PRIMARY KEY NOT NULL IDENTITY(1,1), product_ID INT, product_name VARCHAR(100), product_code VARCHAR(100), color VARCHAR(10), brand VARCHAR(100), origin VARCHAR(100), model VARCHAR(100), added_date DATETIME, status TINYINT DEFAULT 0, log_operation VARCHAR(10), log_date DATETIME NOT NULL DEFAULT (getdate()), log_creator VARCHAR(100), log_ip VARCHAR(20) ) tblProducts_Log Table Insert Scenario Now let's create our scenario. For example, for every insert operation on the tblProducts table, let it also be written to the tblProducts_Log table. This way, we will also have stored the initial values of the records. Insert Operation in Products Table Logged Into Log Table CREATE TRIGGER trgProducts_Insert ON tblProducts AFTER INSERT AS BEGIN INSERT INTO tblProducts_Log SELECT product_ID, product_name, product_code, color, brand, origin, model, added_date, status, 'Insert', GETDATE(), CAST(SERVERPROPERTY('MachineName') AS VARCHAR(50)), CAST(CONNECTIONPROPERTY('local_net_address') AS VARCHAR(50)) FROM INSERTED END Let's share the created trigger visually. trgProducts_Insert Trigger Let's test this trigger by entering some data into the tblProducts table. Inserting Values into tblProducts Table Now let's check in the log table if the trigger we created while inserting data into this table also recorded it in the other log table. Values Added to tblProducts_Log Table During Insert SQL Trigger, Advanced Log Update Scenario Now let's move on to another scenario. For example, let's create a trigger that allows us to access the previous records if any column of existing records is updated. Update in Products Table CREATE TRIGGER trgProducts_Update ON tblProducts AFTER UPDATE AS BEGIN INSERT INTO tblProducts_Log (product_ID, product_name, product_code, color, brand, origin, model, added_date, status, log_operation, log_date, log_creator, log_ip) SELECT product_ID, product_name, product_code, color, brand, origin, model, added_date, status, 'Update', GETDATE(), CAST(SERVERPROPERTY('MachineName') AS VARCHAR(50)), CAST(CONNECTIONPROPERTY('local_net_address') AS VARCHAR(50)) FROM deleted END Let's share the created trigger visually. trgProducts_Update Trigger Let's proceed with an example for the update operation. Let's update the product code of the record with product_ID value 2 from "LG42OLEDTV" to "LG42OLEDTV2020," then update its color from "Brown" to "Orange." Update in Products Table UPDATE tblProducts SET product_code = 'LG42OLEDTV', color = 'Brown' WHERE product_ID = 2 UPDATE tblProducts SET product_code = 'LG42OLEDTV2020', color = 'Orange' WHERE product_ID = 2 Let's see the change in