Cool Ways I've Used SQL Part 1

This is part 1 of a series, where I share cool ways I have used SQL. Most examples come from problems I solved for work, where the queries/scripts provided value for the business. I begin with a SQL feature most people never get a chance to use—triggers.

Triggers are special stored procedures that automatically execute in response to a specific database event, usually on a table insert, delete, or update. They’re cool because they automate processes that an analyst or developer would generally have to do manually. In the 1.5 years I have worked with SQL, I have only used triggers twice—only one of those involved deploying a trigger to production.

Let’s begin with that one. Here’s some background information on the problem…

Years ago, my team automated a commission report for our Finance team. The report says how much commission to pay each salesperson every month. The report combines data from a third-party financial ERP with commission percentage data from an in-house system to calculate the final dollar values. However, last year, the Finance team discovered additional finance credits generated outside of the financial ERP. They were not included in the commission report, meaning we overpaid commissions for some time.

The problem needed fixing, so Finance came to my team.

The solution was to create an upload program in our in-house ERP system that allowed Finance to upload a spreadsheet containing their additional credits. The backend I built then ensured the data flowed into the proper tables within our wholesale data mart.

It was fun, and I was fortunate to be the data lead on the project (especially considering I was only six months into the job at the time).

However, there was a slight issue with these additional credits. Our financial ERP system generated credit invoice numbers for all records, so the external credits had none assigned yet. This meant the program needed to auto-generate credit invoice numbers upon upload.

That’s where SQL triggers come into play.

The program uploaded credit data into a table with an integer identity field, so all records in this column were unique. My team realized we could leverage the identity column to create a unique credit invoice number by appending a predetermined prefix to the front.

The original plan was to have the application call a stored procedure on upload that would populate the invoice number field. But I realized it would be easier to develop a SQL trigger instead.

Below is an example of my trigger logic…

create trigger dbo.GenerateAdditionalCreditInvoiceNumber
on dbo.AdditionalFinanceCredits
after insert
as
begin
 update afc
 set InvoiceNumber = 'PT' + cast(i.AdditionalFinanceCreditsId as varchar)
 from dbo.AdditionalFinanceCredits afc
 join inserted i on i.AdditionalFinanceCreditsId = afc.AdditionalFinanceCreditsId
end

Note: I wrote the above using the T-SQL dialect since I use SQL Server at work. I have played around with other RDBMSs (Relational Database Management Systems) in my free time, but never implemented triggers with them. Although all major RDBMSs support triggers in some fashion.

The original additional finance credits were called Payment Term Discounts, hence the ‘PT’ prefix in my code. The trigger properly functioned with no complaints for a few months. Then the Finance team approached us with a new request.

They discovered another type of credit that also originated from outside our financial ERP. They were called Off-Invoice Deductions. So, we had to enhance our upload program to include the new credits.

The tricky part, however, was that we needed a way to distinguish between the two in the commission report.

Our solution was to assign each credit a unique TransactionTypeId in the database. Accounting for these new requirements, I modified the trigger to look like this:

alter trigger dbo.GenerateAdditionalCreditInvoiceNumber
on dbo.AdditionalFinanceCredits
after insert
as
begin
 update afc
 set InvoiceNumber = case when TransactionTypeId = 5 --Payment Term Discounts
                          then 'PT' + cast(i.AdditionalFinanceCreditsId as varchar)
                          when TransactionTypeId = 6 --Off-Invoice Deductions
                          then 'OI' + cast(i.AdditionalFinanceCreditsId as varchar)
                          end
 from dbo.AdditionalFinanceCredits afc
 join inserted i on i.AdditionalFinanceCreditsId = afc.AdditionalFinanceCreditsId
end

This modification has been in production for over six months now, and the Finance team has been happy. So that’s a win for my team and me.

When finance users upload data into the program, they upload a few hundred records at a time. This matters because if hundreds of thousands or millions of records were uploaded, the trigger’s performance could worsen due to the high load. But a few hundred records are more than a manageable load for the trigger I put in place.

There’s one other thing I want to point out regarding my code. Joining to the inserted temporary table matters. The join ensures we only update InvoiceNumber for new records inserted into the table. If we didn’t have that join, the trigger would update InvoiceNumber for every record in the table, regardless of whether it’s new. If the table scales to 100,000+ records, the trigger would update 100,000+ records every execution. That would hurt the database’s performance.

So, while what I have above is good, the small changes below would hurt the trigger’s performance if implemented:

--Bad trigger that does not join to the "inserted" temp table
create trigger dbo.GenerateAdditionalCreditInvoiceNumber
on dbo.AdditionalFinanceCredits
after insert
as
begin
 update AdditionalFinanceCredits
 set InvoiceNumber = case when TransactionTypeId = 5 --Payment Term Discounts
                     then 'PT' + cast(AdditionalFinanceCreditsId as varchar)
                     when TransactionTypeId = 6 --Off-Invoice Deductions
                     then 'OI' + cast(AdditionalFinanceCreditsId as varchar)
                     end
 from dbo.AdditionalFinanceCredits
end

The second time I used a trigger at work, my solution never went into production, but it still provided value. Here’s what happened…

There was a problem with an API that managed our inventory systems. We required the API because we had multiple applications processing inventory. So, the API ensured inventory levels across systems were in sync.

If you’ve ever worked in a consumer goods company, you understand how critical strong inventory management is to an organization’s success. Poor management leads to underselling or overselling of finished goods. In either case, that’s not good for revenue or growth.

The developer investigating the problem came up with a few theories about the root cause. One theory was that the API properly inserted data into the database but some other background process reverted the changes. To test the hypothesis, the developer suggested we create a trigger that tracked all updates in our inventory table.

I worked with him to execute the idea. I created a new table called dbo.InventoryUpdates that stored before and after snapshots when a system/application updates an item’s inventory. The trigger on the inventory table captured changes into dbo.InventoryUpdates every time a value in the InventoryValue field updated.

Here’s an example of how I designed the trigger…

create trigger dbo.InventoryUpdateTracker
on dbo.Inventory
after update
as
if update(InventoryValue)
begin
 insert into dbo.InventoryUpdates (
	ItemNumber,
	OldInventoryValue,
	NewInventoryValue,
	UpdateTime
 )
 select i.ItemNumber,
 d.InventoryValue,
 i.InventoryValue,
 getutcdate()
 from inserted i
 join deleted d on d.ItemNumber = i.ItemNumber
 where i.InventoryValue <> d.InventoryValue
end

The trigger became useful.

It proved the developer’s hypothesis incorrect, thus providing validation that he could focus on other theories he developed.

This is a good example to show how providing value doesn’t only happen in production. Debugging and troubleshooting in lower environments are inevitable in the software development world. So, helping developers solve problems more efficiently in the staging environment is another way to deliver business value with SQL.

For that, I not only enjoyed the time it took to create this query, but I felt good that it provided insights for the company, even if it was in a small way.

Cool Ways I've Used SQL Part 1 - Triggers