The TL;DR is the title, but I'll add some detail below.
I've got a conundrum. There's a table where I've got unusually delimited data. In one field are the names and delimiter definition, and in another is the data to be parsed. Below is a truncated example of the fields:
| RecordID | PropertyNames | PropertyValues |
<SomeGUID> |
dateFormat:S:0:10:enableEmoticons:S:10:4:updateDate:S:14:87:bio:S:101:42:isEnabled:S:143:4: |
d MMM yyyyTrue<?xml version="1.0" encoding="utf-16"?> <dateTime>2024-06-07T04:00:00+00:00</dateTime><p>A fully-documented bio goes in here</p>True |
In my database, I've got 10 or so "properties" for each record, but I've only shown five in the above example.
To help with retrieving these individual properties, I've got a function I shamelessly stole from an online source. It extracts out the property's value based on the Record ID and the property name.
SELECT RecordID
, [GetPropertyValue]([RecordID], 'updateDate') AS [updateDate]
FROM [myTable]
RecordID updateDate ------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 67041d13-fd39-42ee-92c7-429b7121c24e <?xml version="1.0" encoding="utf-16"?><dateTime>2024-06-07T04:00:00+00:00</dateTime>
The function works great, but it's expensive. Let me rephrase: I built a Table-Value function and ran with a CROSS APPLY to do this for all records and it takes F-O-R-E-V-E-R. Plus it can't be indexed, so that slows down future queries. But I like the output it gives me (below), so I started investigating moving this to an actual table in the database.
| RecordID | dateFormat | enableEmoticons | updateDate | bio | isEnabled |
| 67041d13-fd39-42ee-92c7-429b7121c24e | d MM yyyy | True | <?xml version="1.0" encoding="utf-16"?> <dateTime>2024-06-07T04:00:00+00:00</dateTime> | <p>A fully-documented bio goes in here</p> | True |
The original table will frequently get new records, but existing records rarely change. I thought about using a MERGE to do the work, but the original "Source" query (like the one above but with all records and all property names) takes forever to run. Essentially, the query never gets to the INSERT/UPDATE portion.
I can run it for a handful of records at a time, so I know it works, which got me thinking: "Can I have the server do this work itself when changes are detected?" This read very much like a trigger, but I've never worked with them before.
So that's the question:
- Can I write a trigger (or three) that watches the "Source" table and when it detects an INSERT/UPDATE/DELETE, does an INSERT/UPDATE/DELETE on a different table?
- If yes, what would that trigger definition look like? I've already read CREATE TRIGGER (Transact-SQL) - SQL Server | Microsoft Learn, but it's over my head. I need the K-I-S-S example.
- Does this functionality exist in most recent versions of SQL Server? I'm running a mix of SQL Server 2019 and 2022.
Thanks in advance for taking a moment to read through my question.
