How do create and use SQL Server DML triggers for data auditing?

Nowadays data plays a vital role almost everywhere be it in the private sector or any other organization. Therefore, there is a need to maintain a record of the changes in the data for data analysis or some other purpose, known as data auditing. In SQL DML triggers can be a method for data auditing. To delete, modify and create Data Manipulation Language (DML) triggers, a powerful tool SQL server management studio is used.

In this blog, we will together find answers to some frequently asked questions. such as “How do you audit data in SQL? Further, How to use SQL Server DML Triggers for data auditing? In what way you can view SQL audit files? Tracking of DML changes by SQL Server?”. Let’s take a deeper dive to learn how to create and use SQL server DML triggers for data auditing.

Introduction

SQL Server’s system-versioned temporal tables provide a set of tables that let you view changes made to your tables in the past. This is known as DML (database modification) auditing. The schema of these temporal tables is designed in a way that allows its use for an additional level of security. Because it allows you to use and easily track who has edited or modified the data or added data, and when the changes

What are DML Triggers?

To track the changes made in the database, you can use the SQL Server database audit feature. It can be used for data auditing and other purposes, such as monitoring what happened after an event occurred.

DML triggers in SQL – benefits:

• Triggers play a crucial role by generating an alert, whenever any DML statement is executed on the table or view with which it is associated. You’ll receive an email alert if there’s been any change made since the last checkup time.
• You can set up rules so that only certain rows will trigger this alert
• Triggers can be created at any time during runtime – they won’t have any effect until they’re called later on

Benefits of DML triggers

Benefits of DML trigger

• It is easy to create. You can use the SSMS for data auditing by creating DML triggers. The following steps are required:
• Create a new table in SSMS, add data and then select the “New” button from the SSMS toolbar;
• Enter the name of your table, select the appropriate schema and click OK;
• Select “Trigger” from the Tools menu. If you want to create an audit trail for all changes made on this table then click on the “Audit” option in the above window. Choose whether you want to track inserts/updates using triggers or triggers only using checkboxes marked “Inserts” & “Updates”;
• After selecting desired options go back again to the Tools menu where we see two buttons namely “New Trigger” & “Delete Trigger”.

The types of triggers used in DML

Two main types of triggers that can be used in DML (Data Manipulation Language):

1. Row-level triggers: These triggers are activated every time a row is inserted, updated, or deleted in a table. Row-level triggers can be used to enforce data integrity, update related tables, or perform other actions based on changes to a specific row.
2. Statement-level triggers: These triggers are activated once for each SQL statement that affects one or more rows in a table. Statement-level triggers can be used to enforce data integrity, update related tables, or perform other actions based on changes to multiple rows in a table.

DML Triggers: How to Create Them  

In SQL Server, Triggers are a way to audit changes made in data. Triggers are used for maintaining data integrity(accuracy and completeness of data) and auditing. They are created or fired when you perform any specific action on a table.

In one table you are free to connect one or more triggers. which will give you the ability to monitor various actions that affect those tables.

Here is an example of how to create a DML trigger in SQL that logs changes to the customer table in a customer_audit table:

CREATE TABLE Employee(Id integer PRIMARY KEY, firstName varchar(50), lastName varchar(50), fullName varchar(50));Code language: SQL (Structured Query Language) (sql)

create trigger Employee_name 
after INSERT 
on 
Employee 
for each row 
BEGIN 
   UPDATE Employee set fullName = firstName || ' ' || lastName;
END;Code language: SQL (Structured Query Language) (sql)

INSERT INTO Employee(id, firstName, lastName) VALUES(1,'John', 'Morte');
INSERT INTO Employee(id, firstName, lastName) VALUES(2,'Ursula', 'Luna');
INSERT INTO Employee(id, firstName, lastName) VALUES(3,'prit', 'Ituno');
INSERT INTO Employee(id, firstName, lastName) VALUES(4,'Doe', 'Alonso');
INSERT INTO Employee(id, firstName, lastName) VALUES(5,'Heinz', 'Robert');Code language: SQL (Structured Query Language) (sql)

select * from Employee;Code language: SQL (Structured Query Language) (sql)

The use of SQL Server Management Studio view

To manage the database of SQL Server this server studio tool is used. It provides the ability to perform administrative tasks such as managing databases, managing users and permissions, exporting data from one database into another, setting up alerts and more.

You can use SSMS by clicking the Start button> All Programs > Microsoft SQL Server 2008 R2 > Microsoft SQL Server Management Studio or by typing “SSMS” in the Start menu search box.

Requirements

• Windows 7 or later
• SQL Server Management Studio (SSMS)
• SQL Server 2005 or later

Download – SQL Server Management Studio (SSMS)

SSMS is available for free at Microsoft.com. You can download it from there. It’s a Windows application, and it’s a 32-bit application—so if you have an older computer that doesn’t support 64-bit windows, this software won’t work on your PC at all. If you have a newer machine with 64-bit support and want to use SQL Server Management Studio (SSMS), make sure that you install the right version of the program:

• Download the 32-bit version of SSMS from Microsoft’s website

Install – SQL Server Management Studio (SSMS)

The SSMS tool is the perfect way to manage the Microsoft SQL server if you have it. You can use it to create, modify and delete databases; view information about a database; create or delete tables; add columns to their default data types; change their storage options (such as whether they’re stored in memory or on disk); and more. You can also use SSMS’ powerful query editor to run SELECT statements against your tables—and even form complex queries without writing any code!

Basic steps in SSMS

• Start SSMS then connect to SQL Server instance.
• Open the database you want to audit (the default is an empty one).
• Open the table or view that you want to audit. For example, if you wish to audit all tables in your AdventureWorks database, open AdventureWorks as shown below:

Auditing is configured in SQL server via SSMS

SQL server management studio configures auditing which is a security feature. It is an important tool for compliance, detecting security breaches and data loss. It provides you with information about user’s actions on your database and enables you to track changes made to tables or views over time.

You can set up auditing for the following:

• All queries run against the database (including sprocs)
• Statements executed against tables in your database (including stored procedures)

Viewing DML triggers dependencies

By using Transact-SQL 

To view the dependencies of a DML trigger in SQL Server, you can use the sys.sql_expression_dependencies catalogue view. This view returns a row for each object that is directly or indirectly referenced by an expression in a stored procedure, function, view, or trigger.

Here is an example of how to use sys.sql_expression_dependencies to view the dependencies of a DML trigger:

SELECT
    OBJECT_NAME(referencing_id) AS referencing_object,
    OBJECT_NAME(referenced_id) AS referenced_object
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('audit_trigger');Code language: SQL (Structured Query Language) (sql)

This query will return a list of objects that are referenced by the audit_trigger DML trigger.

For example, if the audit_trigger trigger references the employees table and the audit_table table, the output of this query might look like this:

referencing_object | referenced_object
-------------------|------------------
audit_trigger      | employees
audit_trigger      | audit_tableCode language: SQL (Structured Query Language) (sql)

Note that this is just an example. You will need to modify it to fit your specific requirements.

View the DML trigger definition

In database management, a trigger is a set of SQL statements that are automatically executed by the database management system (DBMS) in response to certain events on a table or view in the database.

Viewing DML triggers dependencies

Triggers can be defined to execute either before (BEFORE trigger) or after (AFTER trigger) the triggering event. In addition, triggers can be defined to execute for each row affected (FOR EACH ROW trigger) or for the entire statement (FOR EACH STATEMENT trigger).

Here is an example of a trigger in MySQL that updates a summary table every time a row is inserted or updated in the main table:

CREATE TRIGGER update_summary AFTER INSERT OR UPDATE ON main_table
FOR EACH ROW
BEGIN
  UPDATE summary_table SET total = total + NEW.amount WHERE id = NEW.id;
END;Code language: SQL (Structured Query Language) (sql)

This trigger will be activated every time a row is inserted or updated in the main_table, and it will update the summary_table by adding the value of the amount column in the new or updated row to the current value of the total column for the corresponding id.

Viewing information about DML trigger events

DML triggers are used to enforce business rules, maintain data integrity, and audit data changes on a table. These SQL server DML triggers are used for data auditing.

DML triggers can be defined to execute one of the following events:

1. INSERT: This event occurs when a new row is inserted into the table.
2. UPDATE: This event occurs when an existing row in the table is updated.
3. DELETE: This event occurs when an existing row in the table is deleted.

Conclusion

DML triggers are great tools for data auditing. They are used to audit all SQL database changes. It’s very simple to use and there are several ways you can implement it in your SQL database. If you need help implementing these methods or have any questions about this article, then feel free to reach out to us by putting your comment in the comment box.

FAQs

How do you audit data in SQL?

To audit data in SQL, you can use a variety of techniques depending on your specific needs and the version of SQL you are using. Here are some options:

1. Use the AUDIT statement to track changes to specific tables or views in Oracle.
2. Use triggers to capture data changes. For example, you can create a trigger that logs changes to a separate audit table whenever an insert, update or delete operation is performed on a target table.
3. Use the Change Data Capture (CDC) feature in SQL Server to track changes to data in specific tables.
4. Use the Change Tracking feature in SQL Server to track changes to data in specific tables.

How do you create an audit table in SQL?

To create an audit table in SQL, you can use the following steps:

1. Create a new table with the same structure as the table you want to audit.
2. Add additional columns to the audit table to store information about the audit, such as the user who made the change, the time of the change, and the type of change (insert, update, or delete).
3. Create a trigger on the target table that captures changes and writes them to the audit table.

How does SQL Server track DML changes?

SQL Server tracks DML (Data Modification Language) changes using a variety of methods, including triggers and Change Tracking In SQL server DML triggers can be created for data auditing..

How do I audit a SQL Server database?

To audit a SQL Server database, you can use the SQL Server Audit feature, which allows you to specify the actions you want to audit and the target to which the audit will be written (such as the event log or a file). You can also use custom solutions such as triggers or CDC to track changes to specific tables or views.

How do I view SQL audit files?

To view SQL audit files, you can use the fn_get_audit_file function in SQL Server to read the contents of an audit file and display it in a query window. Alternatively, you can use a tool such as SQL Server Management Studio to open and view the contents of the audit file.