SQL Server Triggers for Business Rules Automation

Problem

The advantage of using triggers is that the same processing can occur regardless of where or how the data has been inserted, updated or deleted. In this article, we look at several examples of where and why triggers could be useful along with an example use case.

Solution

Using triggers can be helpful to make sure the exact same data operations occur whenever table data is inserted, updated or deleted. This allows you to create one set of logic and be able to use it over and over again.

We will take a look at some real world examples of where triggers could be useful and also look at a sample use case with code and examples.

Some Practical Use Cases of Triggers

Use cases for triggers include those that are state-transitioned, detecting duplicates, or status changes. In no particular order, below are some practical, real-world examples of where a trigger could be useful.

Payment Processing in Banking & Financial Services

Use Case: Bank receives payment messages from external sources. A payment is cancelled by a CANCLMSG, which retires all NEWMSG payment instructions that match on Reference Numbers, Amounts, and Customer Details.
Why It’s Useful: Automating the retirement or archival of previous transactions minimizes manual effort, prevents duplicate payments, and keeps audit trails clean.

Patient Test Orders and Results in Healthcare

Use Case: Orders for medical tests and cancellations of those orders are communicated to a lab system via NEWMSG and CANCLMSG messages. If a CANCLMSG message is received for an order with the same Patient ID, Test Code, and Timestamp, the original order should be marked as “Cancelled” or “Superseded”.
Why It’s Useful: The system can be kept up-to-date with accurate test records, which can increase compliance with medical record-keeping standards and decrease billing errors.

Shipment Tracking System in Logistics

Use Case: A logistics system processes updates to shipments. If a cancellation request is received, the system should flag any existing “in-progress” shipment with a cancellation status and create a new record for the cancellation request.
Why It’s Useful: Record-keeping of shipment events and cancellations can help with traceability and compliance with transport regulations.

Service Provisioning and Deactivation in Telecom

Use Case: Telecom system processes service provisioning requests through NEWMSG and service deactivation requests through CANCLMSG. Service should be marked as retired (RETRDMSG) if a deactivation request matches an active service request. Retain both the service request and the deactivation request information.
Why It’s Useful: History retention aids in preserving accurate billing/service status records, which can be vital for customer disputes.

Permit or License Management in Government

Use Case: NEWMSG is received when a citizen makes an application for a permit or a license. The original request is retired when a CANCELLATION message is received with the same Application ID and Applicant Details.
Why It’s Useful: Status tracking automation and audit trails help ensure accuracy and consistency of government records.

Stock or Inventory Management

Use Case: Inventory management systems can be sent NEWMSG on new restocks and CANCLMSG on purchase order cancellations. The restock instruction is put on a “voided” status when the cancellation message is received.
Why It’s Useful: The system can avoid stocking of cancelled items, which can decrease storage costs and reduce supply chain interruptions.

Customer Communication Updates in CRM Systems

Use Case: A CRM system stores information about customer outreach communications. A “retract” command for a campaign communication should flag the original communication as retracted (RETRDMSG) and log the retract request.
Why It’s Useful: Proper management of customer communications records can help organizations with GDPR and other data privacy requirements, such as the right to be forgotten and communication opt-outs.

Business Use Case Example

Let’s say we have a table that receives various messages to allow for further data processing. The table only allows inserts, but depending on the values that are set along with certain data identifiers, different triggers actions need to occur.

Business Logic

There is a SQL Server table that stores process instructions and the table has a column that identifies if a message instruction as “Current”, “Cancelled”, or “Retired”.

The table includes a column called MsgFunction, which is used to indicate the type of instruction and can have these values:

CURMSG = a new message
CANCMSG = a cancelled message
RETRDMSG = a retired message

The basic rules are as follows:

Data only gets inserted into table RawProcessed
There are two types of insert options “New Message” CURMSG or “Cancelled Message” CANCMSG .
- A CURMSG creates a new row in RawProcessed
- A CANCMSG updates a CURMSG row based on the matching info below and changes it to RETRDMSG if a record exists. This will also create a log record to table MsgFunctionAuditLog.

Additional Rules

When ClearingSystem = “BRITCLEAR” match records as follows:

The 7 digits after the first 3 characters of ‘SenderRef‘
- Example: If SenderRef is ‘ORG7521555333123’, it will be matched using ‘7521555’
It also has to match on all of these values too: Amount, ClearingSystem, SenderCorpRef, and SNINT

When ClearingSystem =“CLEARLAKE” match records as follows:

It also has to match on all of these values too: BlockRef, SNINT, Amount, ClearingSystem, and SenderCorpRef

Step-By-Step Guide

To achieve the business requirements in this example, I will break the solution into several steps:

Step 1: Create the RawProcessed table
Step 2: Insert rows of sample data into the RawProcessed table
Step 3: Create an audit log table
Step 4: Create the trigger
Step 5: Insert new records into the RawProcessed table for testing
Step 6: Validate the records in the audit log table and the RawProcessed table

In real life, an existing table is already attached to the trigger, and your business requirements might be different. However, the logic of leveraging a trigger to automate a routine business task or process is the same.

Step 1: Create the RawProcessed Table

In this example, I have created the RawProcessed table in the SQL script below.

-- DROP TABLE if it exists (for clean rerun)
IF OBJECT_ID('dbo.RawProcessed', 'U') IS NOT NULL DROP TABLE dbo.RawProcessed;
GO
 
-- Create the main messages table
CREATE TABLE dbo.RawProcessed
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    MsgFunction VARCHAR(20) NOT NULL,
    ClearingSystem VARCHAR(50) NOT NULL,
    SenderRef VARCHAR(50) NOT NULL,
    SenderCorpRef VARCHAR(50) NOT NULL,
    SNINT VARCHAR(20) NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    BlockRef VARCHAR(50) NULL,
    CreatedBy VARCHAR(100) NOT NULL,
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE()
);
GO

Step 2: Insert Rows of Sample Data into the RawProcessed Table

Once the table is created, populate it with rows of data that reflect those in the use case. The SQL script below is used to populate the table in case you are following along.

-- Insert sample data into RawProcessed table
 
-- Insert CURMSG first (simulate existing record for BRITCLEAR)
INSERT INTO dbo.RawProcessed (MsgFunction, ClearingSystem, SenderCorpRef, SNINT, Amount, SenderRef, BlockRef, CreatedBy, CreatedDate)
VALUES ( 'CURMSG', 'BRITCLEAR', 'ORG7521555333123', 'SN001', 1500.00, 'ORG1234567ABC', NULL, 'TestUser', GETDATE() );
-- Insert CANCMSG for BRITCLEAR
INSERT INTO dbo.RawProcessed (MsgFunction, ClearingSystem, SenderCorpRef, SNINT, Amount, SenderRef, BlockRef, CreatedBy, CreatedDate)
VALUES ( 'CANCMSG', 'BRITCLEAR', 'ORG7632666333123', 'SN002', 1000.00, 'ORG1234567XYZ', NULL, 'TestUser', GETDATE() );
 
-- Insert CANCMSG for CLEARLAKE
INSERT INTO dbo.RawProcessed (MsgFunction, ClearingSystem, SenderCorpRef, SNINT, Amount, SenderRef, BlockRef, CreatedBy, CreatedDate)
VALUES ( 'CANCMSG', 'CLEARLAKE', 'CORPREF9', 'SNINT1003', 40000.00, 'XYZ9876543ABC', 'BLOCKREF125', 'TestUser', GETDATE() );
-- Insert CURMSG first (simulate existing record for CLEARLAKE)
INSERT INTO dbo.RawProcessed (MsgFunction, ClearingSystem, SenderCorpRef, SNINT, Amount, SenderRef, BlockRef, CreatedBy, CreatedDate )
VALUES ( 'CURMSG', 'CLEARLAKE', 'REF456', 'SN002', 2500.00, 'XXX1234567YYY', 'BLOCK001', 'TestUser', GETDATE() );

The initial data is inserted into the table, seen in the image below. As you can see, we have two CURMSG and two CANCMSG on the MsgFunction column, representing for BRITCLEAR and CLEARLAKE ClearingSystem, respectively.

An image showing the output of running a SQL table named RawProcessed after the initial inserting of rows.

Step 3: Create an Audit Log Table

As a standard and best practice, it is essential to create an audit log table to help manage auditing processes. This is necessary since we are performing updates on a production table, and you might want to audit the activities of this trigger process at some point.

The SQL script below creates the audit log table with some basic details. You can add more details as your business requires.

-- DROP TABLE if it exists (for clean rerun)
IF OBJECT_ID('dbo.MsgFunctionAuditLog', 'U') IS NOT NULL DROP TABLE dbo.MsgFunctionAuditLog;
GO
-- Create audit log table
CREATE TABLE dbo.MsgFunctionAuditLog
(
   LOG_ID INT IDENTITY(1,1) PRIMARY KEY,
   UPDATED_ID INT,
   CANCEL_ID INT,
   ORIGINAL_SENDER_REF VARCHAR(50),
   CANCEL_SENDER_REF VARCHAR(50),
   SNINT VARCHAR(50),
   AMT DECIMAL(18, 2),
   UPDATE_TIMESTAMP DATETIME DEFAULT GETDATE(),
   UPDATED_BY_TRIGGER VARCHAR(100) DEFAULT ''
);

Step 4: Create the Trigger

After creating the Audit Log table, it’s now time to create the trigger on the RawProcessed table. The business requirement logic listed earlier is embedded within the trigger:

CREATE TRIGGER trg_UpdateOrigMsgDemo 
ON dbo.RawProcessed
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @UpdatedRows TABLE (
      UPDATED_ID INT,
      CANCEL_ID INT,
      ORIGINAL_SENDER_REF VARCHAR(50),
      CANCEL_SENDER_REF VARCHAR(50),
      SNINT VARCHAR(50),
      AMT DECIMAL(18, 2)
   );
   -- BRITCLEAR: Matching on 7-digits substring from SenderRef
   UPDATE dbo.RawProcessed
   SET MsgFunction = 'RETRDMSG'
   OUTPUT
      deleted.Id AS UPDATED_ID,
      inserted.Id AS CANCEL_ID,
      deleted.SenderRef AS ORIGINAL_SENDER_REF,
      inserted.SenderRef AS CANCEL_SENDER_REF,
      deleted.SNINT,
      deleted.Amount
   INTO @UpdatedRows
   FROM dbo.RawProcessed
   JOIN inserted
      ON inserted.MsgFunction = 'CANCMSG'
      AND RawProcessed.MsgFunction = 'CURMSG'
      AND inserted.ClearingSystem = 'BRITCLEAR'
      AND RawProcessed.ClearingSystem = inserted.ClearingSystem
      AND SUBSTRING(RawProcessed.SenderRef, 4, 7) = SUBSTRING(inserted.SenderRef, 4, 7)
      AND RawProcessed.SenderCorpRef = inserted.SenderCorpRef
      AND RawProcessed.SNINT = inserted.SNINT
      AND RawProcessed.Amount = inserted.Amount
      AND RawProcessed.Id <> inserted.Id  -- Avoiding self updating if the row is same
   -- CLEARLAKE: Matching on BlockRef (Since this is matching based on a different business rule)
   UPDATE dbo.RawProcessed 
   SET MsgFunction = 'RETRDMSG'
   OUTPUT
      deleted.Id, 
      inserted.Id AS CANCEL_ID,
      deleted.SenderRef AS ORIGINAL_SENDER_REF,
      inserted.SenderRef AS CANCEL_SENDER_REF,
      deleted.SNINT,
      deleted.Amount
   INTO @UpdatedRows
   FROM dbo.RawProcessed
   JOIN inserted
      ON inserted.MsgFunction = 'CANCMSG'
      AND RawProcessed.MsgFunction = 'CURMSG'
      AND inserted.ClearingSystem = 'CLEARLAKE'
      AND RawProcessed.ClearingSystem = inserted.ClearingSystem
      AND RawProcessed.BlockRef = inserted.BlockRef
      AND RawProcessed.SenderCorpRef = inserted.SenderCorpRef
      AND RawProcessed.SNINT = inserted.SNINT
      AND RawProcessed.Amount = inserted.Amount
      AND RawProcessed.Id <> inserted.Id  -- Avoiding self updating if the row is same
   
   -- Insert records into log table
   INSERT INTO dbo.MsgFunctionAuditLog (
      UPDATED_ID, CANCEL_ID, ORIGINAL_SENDER_REF, CANCEL_SENDER_REF, SNINT, AMT
   )
   SELECT 
      UPDATED_ID, CANCEL_ID, ORIGINAL_SENDER_REF, CANCEL_SENDER_REF, SNINT, AMT
   FROM @UpdatedRows;
END;

As shown below, the trigger is now created on the RawProcessed table.

A screenshot to show a trigger created on a table named RawProcessed.

Step 5: Insert New Records into the RawProcessed Table for Testing

The next step is to test the trigger to see if it is performing the tasks in the business requirements as needed. To do this, insert some new rows for both BRITCLEAR and CLEARLAKE ClearingSystem. To make this a bit clearer, let’s insert the rows for BRITCLEAR first, which should be one row for CURMSG and one row for CANCMSG. Our expectation is for the ‘CURMSG’ value in the first row in the RawProcessed table with SenderRefas ‘ORG1234567ABC’ and Amount as ‘1500’ to be updated as ‘RETRDMSG’ once the new rows inserted meet the conditions in the trigger.

The code below inserts data to fire the trigger. Note: These new rows of data would usually be populated in the RawProcessed table automatically from either SQL Server Integration Services, Azure Data Factory, pipelines, etc.

-- Insert sample data test data into RawProcessed table to fire the trigger
 
-- Insert CURMSG first (simulate records for BRITCLEAR Only)
INSERT INTO dbo.RawProcessed (MsgFunction, ClearingSystem, SenderCorpRef, SNINT, Amount, SenderRef, BlockRef, CreatedBy, CreatedDate )
VALUES ( 'CURMSG', 'BRITCLEAR', 'ORG7123455333000', 'SN003', 2000.00, 'ORG1234567ABC', NULL, 'TestUser', GETDATE() );
 
-- Insert CANCMSG 
INSERT INTO dbo.RawProcessed (MsgFunction, ClearingSystem, SenderCorpRef, SNINT, Amount, SenderRef, BlockRef, CreatedBy, CreatedDate )
VALUES ( 'CANCMSG', 'BRITCLEAR', 'ORG7521555333123', 'SN001', 1500.00, 'ORG1234567ABC', NULL, 'TestUser', GETDATE() );

As you can see in the table below a few things have happened:

ID = 5 and ID = 6 rows have been added.
ID = 1 had its MsgFunction updated from CURMSG to RETRDMSG based on the trigger logic.

An image showing the output of running a SQL table named RawProcessed after inserting test records.

We can do a similiar update for CLEARLAKE.

-- Insert sample data test data into RawProcessed table to fire the trigger
 
-- Insert CURMSG first (simulate records for CLEARLAKE Only)
INSERT INTO dbo.RawProcessed (MsgFunction, ClearingSystem, SenderCorpRef, SNINT, Amount, SenderRef, BlockRef, CreatedBy, CreatedDate )
VALUES ( 'CURMSG', 'CLEARLAKE', 'CORPREF9', 'SNINT1003', 40000.00, 'ORG9876543ABC', 'BLOCKREF521', 'TestUser', GETDATE() );
 
-- Insert CANCMSG
INSERT INTO dbo.RawProcessed (MsgFunction, ClearingSystem, SenderCorpRef, SNINT, Amount, SenderRef, BlockRef, CreatedBy, CreatedDate )
VALUES ( 'CANCMSG', 'CLEARLAKE', 'REF456', 'SN002', 2500.00, 'XXX1234567YYY', 'BLOCK001', 'TestUser', GETDATE() );

As you can see in the table below a few things have happened:

ID = 7 and ID = 8 rows have been added.
ID = 4 had its MsgFunction updated from CURMSG to RETRDMSG based on the trigger logic.

An image showing the output of running a SQL table named RawProcessed after inserting test records 2

Step 6: Validate the Records in the Audit Log Table and the RawProcessed Table

As mentioned earlier, we have an audit log table where details of these updates are stored for auditing purposes. In this case, the audit table is named ‘MsgFunctionAuditLog’.

When selecting data from this table, this is the output. This shows the two RETRDMSG rows for ID = 1 and ID = 4 were added to this table.

An image showing the output of running a SQL Audit Log table.

Again, you can choose to add more details to your log table depending on your business requirements. I have only included basic information for this demonstration.

Summary

In this article, we have successfully demonstrated how to leverage a SQL trigger to automate database tasks that would have otherwise been done manually in some cases. This trigger logic is useful when updating existing records in a table based on matching criteria when new records are inserted into the table. You can follow the steps outlined in this tip and adapt it to suit your business logic.

Next Steps

Read more about SQL triggers in the documentation.
Read more on triggers in SQL in this guide: Triggers in SQL: A Comprehensive Guide.
Check out this blog on best practices for log tables: Best practices when storing logging information to table.
Check out these additional MSSQLTips.com resources:

Resources

Community

Subscribe