Tag: T-SQL

  • A Basic Update Trigger: #SQLNewBlogger

    I had someone ask me about using triggers to detect changes in their tables. As I explained a few things, I thought this would make a nice series, so I’ve written a few posts on triggers that can be useful. This one looks at detecting a change to a column in a trigger.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    The Setup

    I’ve got a Customer table that I want to use, which doesn’t have any triggers on it. Here is the schema.

    CREATE TABLE [dbo].[Customer](
         [CustomerID] [int] NOT NULL,
         [CustomerName] [varchar](200) NOT NULL,
         [AddressKey] [int] NULL,
         [CustomerStatus] [int] NULL,
         [CustomerContact] [varchar](100) NULL,
         [ContactEmail] [varchar](100) NULL,
      CONSTRAINT [CustomerPK] PRIMARY KEY CLUSTERED 
    (
         [CustomerID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    I also have a logging table where I can store messages from a trigger, which is better than direct output.

    Let’s create a trigger. I’ll use an UPDATE trigger here to check if a column is changed and then insert a logging message.

    Note: I’m not trying to be efficient here, just create an action based on what changed. THIS IS NOT PRODUCTION QUALITY CODE.

    Here is a basic trigger using the UPDATE() function to check if a column changed. I am inserting into the logger table when I detect a change, but in a real application, I’d likely have some business logic here instead of the insert.

    CREATE TRIGGER Customer_tru ON dbo.Customer FOR UPDATE
    AS
    BEGIN
         IF UPDATE(CustomerName)
             INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerName changed')
         IF UPDATE(AddressKey)
             INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.AddressKey changed')
         IF UPDATE(CustomerStatus)
             INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerStatus changed')
         IF UPDATE(CustomerContact)
             INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerContact changed')
         IF UPDATE(ContactEmail)
             INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.ContactEmail changed')
    END

    Now, I’ll check the time, then run an update, and select from the logger table. This gives me a nice easy way to see what changes were logged. First, let’s update one field.

    We can see that the change was logged. I could add more info, but this is just a check of what happened.

    2025-05_0227

    Let’s check two changes. We can see that both are detected below.

    2025-05_0228

    Now I’ll update all the fields in a row. There are five other than the PK and I can see all changes logged.

    2025-05_0229

    Note that I’m just detecting an update to this field and marking it as changed. If the value were set to the same value, which some apps do, this is still noted as an update by this trigger. To determine if this value was actually changed, I’d need to compare the inserted and deleted rows.

    That’s a quick look at detecting which fields have changed. In the next post, we’ll look at more information in the trigger.

    SQL New Blogger

    Getting started blogging can be hard. I’m sure many of you have used a trigger. Why not write about setting up a trigger, perhaps with some logic like this. Have you used updated()? Tell us how and why.

    This is a great way to showcase how you approach a problem. This is a basic post here, but I’ve shown above I could do something business related instead of the insert to the logger table.

    This was a 15-20 minute post.

  • Using AI to Test a SQL Query

    Someone sent me this code.

    WITH p AS
     ( SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN
     FROM wp_posts
     WHERE post_parent = 94341; )
     UPDATE p SET menu_order = RN

    That’s not great code. In fact, it won’t run. I know that, but does an AI? Let’s see how Copilot does.

    This is part of a series of experiments with AI systems.

    The First Test

    I entered this prompt, but accidentally hit Enter. When I then pasted in the code and hit enter, I got this:

    2025-05_line0012

    That is just about what I would have sent the author to let them know this code doesn’t work. It’s good to see a GenAI LLM can recognize this.

    I then pasted in the entire prompt which was: “give me a table with the schema to match this code: WITH p AS ( SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN FROM wp_posts WHERE post_parent = 94341; ) UPDATE p SET menu_order = RN”

    The Gen AI response was polite, with this answer:

    Certainly! Based on your SQL code, it looks like you need a table schema that matches the structure of wp_posts, particularly including ID, post_parent, and menu_order. Here’s a possible schema:

    I then had this code, with a copy button that I could copy/paste into SSMS.

    CREATE TABLE wp_posts (
         ID INT PRIMARY KEY,      -- Unique identifier for each post
         post_parent INT,         -- Parent post ID (for hierarchical structures)
         menu_order INT           -- Order value for arranging posts
    );

    That’s what I would have done to mock this up for testing. You might guess the next step: data.

    Here’s my prompt: create some test data for the table with Ids that increment but are not necessarily sequential and post_parent values that match the ID values

    That got my this response.

    2025-05_line0016

    That’s not bad, but it’s a limited set of data. Let’s ask for more.

    2025-05_line0031

    Below this the GenAI LLM gave me a summary that explains what it did.

    2025-05_line0032

    Next, I wanted a test. I used this prompt: write a tsqlt test to verify that the update statement works to reset the menu_order to sequential values for a particular post_parent

    I got a nice response and the code I’m showing below the image.

    2025-05_line0033

    Here is the code:

    -- Step 1: Create a test class
    EXEC tSQLt.NewTestClass 'TestMenuOrderUpdate';

    GO

     

    — Step 2: Define the test
    CREATE PROCEDURE TestMenuOrderUpdate.[test_UpdateMenuOrder_Sequential]
    AS
    BEGIN
    — Arrange: Fake the wp_posts table
    EXEC tSQLt.FakeTable ‘dbo.wp_posts’;

     

    INSERT INTO wp_posts (ID, post_parent, menu_order)
    VALUES
    (101, 94341, 5), (102, 94341, 3), (103, 94341, 8),
    (104, 94341, 1), (105, 94341, 7);

     

    — Act: Run the update logic
    WITH p AS (
    SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN
    FROM wp_posts
    WHERE post_parent = 94341
    )
    UPDATE wp_posts
    SET menu_order = p.RN
    FROM p
    WHERE wp_posts.ID = p.ID;

     

    — Assert: Check if menu_order is sequential
    EXEC tSQLt.AssertEqualsTable ‘ExpectedResults’, ‘wp_posts’;

     

    END;
    GO

     

    — Step 3: Create expected results table for assertion
    CREATE TABLE ExpectedResults (
    ID INT PRIMARY KEY,
    post_parent INT,
    menu_order INT
    );

     

    INSERT INTO ExpectedResults (ID, post_parent, menu_order)
    VALUES
    (101, 94341, 1), (102, 94341, 2), (103, 94341, 3),
    (104, 94341, 4), (105, 94341, 5);

     

    — Run the test
    EXEC tSQLt.Run ‘TestMenuOrderUpdate’;

    
    

    This is where I stopped. This code runs, though it’s brittle in that the Expected Results table is a real table and not a temp inside the test. I can easily edit that and likely quicker than the GenAI can. It’s copy/pasting some code for me from one spot to another. I also need to add a CREATE OR ALTER to the proc.

    Summary

    A quick use of an AI that easily saved me 5-10 minutes to mock of a scenario and show someone what they were doing wrong and also testing their code. I mock up things regularly to test them, and often do something like this to answer questions. This is a good place where AI can save some time, and might be worth using. I’m also glad to see it recognize and write some decent SQL code for me, especially the insert statement for test data.

  • Create a Linked Server: #SQLNewBlogger

    I had a customer recently that was asking about Linked Servers and some development advice. I was going to show them a few things and realized I hadn’t created a linked server in my demo environment, so I did it and decided to create a quick post on this.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    The Scenario

    I have a few demo instances of SQL Server in my local environment: Aristotle and Aristotle\SQL2022. In this case I was connected to the named instance, and decided to create a connection to Aristotle. As you can see, I don’t have any linked servers in the named instance.

    2025-04_0125

    To create a linked server, I can use this simple code:

    EXEC master.dbo.sp_addlinkedserver   
         @server = N'Aristotle',   
         @srvproduct=N'SQL Server';  
    GO

    This creates the linked server (as you can see below), with a number of defaults. In this case, the security is made with whatever login queries the linked server.

    2025-04_0126

    You can see the security properties here:

    2025-04_0127

    This might be OK in your enviroment, or it might not be. Perhaps you need to ensure everyone querying the remote server uses the same login. In which case, the sp_addlinkedserver procedure doesn’t do this. You would need to use sp_addlinkedsrvlogin to do that. That’s for another post.

    NOTE: Be sure you understand what a linked server does, how to use it, and the downsides. There are many and this can slow down your application or overload servers

    I can test this connection by right clicking the Linked Server in SSMS:

    2025-04_0128

    This works, as expected.

    2025-04_0129

    I can also run a query through the linked server, using 4-part naming with the linked server, then the database, schema, and table. This also works:

    2025-04_0160

    That’s it to get started. I recommend you be careful when using linked servers as this creates a bit of a tight coupling and makes development harder. I might recommend you get away from querying database server to database server when possible and let an application do this work if it’s possible.

    SQL New Blogger

    Linked Servers aren’t that common, but they aren’t rare. This is a skill that SQL Server people should have and understand a bit about. This post is very basic, but it provides a jumping off point where I could write a number of other posts related to linked servers and perhaps guide an interviewer along a path of asking me about them. I certainly showcase some knowledge here if someone asks me if I’ve ever created one.

    This post took me about 10 minutes to test and write, and you could probably do this in your environment. You don’t even need to servers, as you could create a loopback linked server.

  • Can I Change a Primary Key Value? #SQLNewBlogger

    I heard someone say recently that you can’t change a primary key value in a row. That’s not the case, so I decided to show a quick proof of that.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    The Scenario

    Let’s set up a simple table with some data.

    CREATE TABLE PKChangeTest (
    ImportantNumber VARCHAR(20) NOT NULL CONSTRAINT PKChangeTestPK PRIMARY KEY
    , CustomerName VARCHAR(50)
    , StatusValue INT)
    GO
    INSERT dbo.PKChangeTest
       (ImportantNumber, CustomerName, StatusValue)
    VALUES
       ('1234567', 'Steve', 1)
    ,  ('2345678', 'Andy', 1)
    ,  ('3456789', 'Brian', 1)
    ,  ('1235667', 'Leon', 1)
    ,  ('1265567', 'Dave', 1)
    ,  ('9914567', 'Bill', 1)
    GO

    If I look at this table, I have some unique numbers making up the PKs. If I select from the table, I can see the data.

    2025-04_0117

    Now, let’s change some data. I’ll change the PK values with a few statements. Then I’ll select from the table, and we will see things changed.

    2025-04_0118

    The ImportantNumber for both Bill and Steve have changed. These are PK modifications.

    We can change a PK value. These are not set in stone once inserted.

    SQL New Blogger

    This is a short look at something that’s a myth among some people. When I heard someone say this, I knew I needed to prove this. The scenario took just about 5 minutes to set up (even without AI), and then it was another 10 minutes to structure and write this post.  I actually have 2 more ideas from this on things I can show to prove how PKs work and are malleable.

    You can do the same thing. When you wonder about something, or hear something that isn’t true from others, prove it. And blog about it.