It’s no secret that generative AI is quickly changing how we think about the ways we work.
Recently, I was fielding questions on my SQL Server Database Triggers course on LinkedIn Learning. Since triggers only fire a single time per INSERT, regardless of how many rows are affected, one of the examples I gave in the course returned a single message to the console whenever new rows were added to the table. A learner reached out and asked how to modify the example for INSERTS that added two or more rows to the table, and they wished to return a unique console message for every row inserted. This gave me a perfect opportunity to take the question over to ChatGPT, and see what it had to say about the problem.
After a few minutes of a very conversational back-and-forth, I had a couple of working code samples in hand. Needless to say, I was pretty impressed. Though ChatGPT at one point made a mistake in its logic, after pointing out the flaw in the command, it corrected the error and provided an updated and functional query that perfectly met my learner’s requested goal.
I’ve published the entire transcript of my ChatGPT conversation below so that you can read through and see how it works. I’ve also packaged up the conversation as an Azure Data Studio notebook file, so you can open and execute the code snippets in your own SQL Server instance if you’d like to play around with it. And if you’d like to have your own conversation with a generative AI, you can sign up at https://chat.openai.com/.
Full ChatGPT Transcript
I've created a table in SQL Server with
CREATE TABLE dbo.Products (
ProductID int PRIMARY KEY,
ProductName nvarchar(50)
);
Great! You have created a table named "Products" with two columns:
- ProductID: an integer column that serves as the primary key of the table. The primary key is a unique identifier for each row in the table and is used to enforce data integrity and facilitate table joins.
- ProductName: a variable-length Unicode string column that can store up to 50 characters. This column will store the name of each product.
By defining the "ProductID" column as the primary key of the table, you have ensured that each row in the table has a unique identifier, and you can use this column to link to other tables that reference products. You can now insert data into this table using the INSERT statement.
Using the products table, create a trigger to print a message when a new record is added
Sure, here's an example trigger that will print a message whenever a new record is added to the "Products" table:
CREATE TRIGGER trg_Products_Insert
ON dbo.Products
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
PRINT 'A new record has been added to the Products table.';
END;
This trigger is called "trg_Products_Insert" and is created on the "Products" table. It is set to execute "AFTER INSERT", which means that it will run every time a new record is inserted into the table.
Inside the trigger, we have included the SET NOCOUNT ON statement, which prevents the trigger from returning a count of the number of rows affected by the insert operation.
The PRINT statement will output the message "A new record has been added to the Products table." to the messages tab in SQL Server Management Studio.
You can customize the message to fit your needs by modifying the text in the PRINT statement.
Can the output message display the productID for the newly added row?
Yes, you can modify the trigger to print the ProductID of the newly inserted row along with the message. Here's an example of how you can do that:
CREATE TRIGGER trg_Products_Insert
ON dbo.Products
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ProductID int;
SELECT @ProductID = ProductID FROM inserted;
PRINT 'A new record with ProductID ' + CAST(@ProductID AS varchar) + ' has been added to the Products table.';
END;
This trigger is similar to the previous one, but we have added a DECLARE statement to create a new variable called @ProductID. We then use the SELECT statement to assign the value of the ProductID column of the newly inserted row to the @ProductID variable.
Finally, we modify the PRINT statement to include the @ProductID variable in the message. We cast the @ProductID variable to a varchar data type so that it can be concatenated with the message.
Now, when a new row is inserted into the "Products" table, the trigger will print a message with the ProductID of the newly added row.
What happens if I add multiple rows in one INSERT statement?
If you add multiple rows in one INSERT statement, the trigger will fire once for each row that is inserted.
For example, if you run the following INSERT statement to add three rows to the "Products" table:
INSERT INTO dbo.Products (ProductID, ProductName)
VALUES
(1, 'Product A'),
(2, 'Product B'),
(3, 'Product C');
Then, the trigger will fire three times, once for each row that is inserted. The output messages will look like this:
A new record with ProductID 1 has been added to the Products table.
A new record with ProductID 2 has been added to the Products table.
A new record with ProductID 3 has been added to the Products table.
Each message will include the ProductID of the corresponding row that was inserted.
That doesn't work correctly. The trigger only fires one time for each INSERT statement, not for each row being inserted. How can I modify the trigger to display a separate message for each inserted product?
You're correct, I apologize for the confusion in my previous response. The trigger will only fire once for each INSERT statement, even if multiple rows are being inserted.
To display a separate message for each inserted product, you can modify the trigger to use a cursor to loop through the "inserted" virtual table and print a message for each inserted row. Here's an example:
CREATE TRIGGER trg_Products_Insert
ON dbo.Products
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ProductID int;
DECLARE @ProductName nvarchar(50);
DECLARE ProductCursor CURSOR FOR
SELECT ProductID, ProductName FROM inserted;
OPEN ProductCursor;
FETCH NEXT FROM ProductCursor INTO @ProductID, @ProductName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'A new record with ProductID ' + CAST(@ProductID AS varchar) + ' and ProductName ' + @ProductName + ' has been added to the Products table.';
FETCH NEXT FROM ProductCursor INTO @ProductID, @ProductName;
END;
CLOSE ProductCursor;
DEALLOCATE ProductCursor;
END;
This trigger uses a cursor to loop through the "inserted" virtual table and fetches the values of the "ProductID" and "ProductName" columns for each inserted row. It then uses a PRINT statement to display a separate message for each row that was inserted, including the ProductID and ProductName.
Note that using a cursor can impact the performance of your trigger, especially if you are inserting a large number of rows. You may want to consider alternative methods of handling multiple-row inserts, such as using a temp table or table-valued parameter.
What will the output messages look like if I insert three new products in one INSERT
If you insert three new products in one INSERT statement, the trigger will fire once and display a separate message for each inserted row. Here's an example:
INSERT INTO dbo.Products (ProductID, ProductName)
VALUES
(1, 'Product A'),
(2, 'Product B'),
(3, 'Product C');
-- Output messages:
-- A new record with ProductID 1 and ProductName Product A has been added to the Products table.
-- A new record with ProductID 2 and ProductName Product B has been added to the Products table.
-- A new record with ProductID 3 and ProductName Product C has been added to the Products table.
The trigger will use a cursor to loop through the "inserted" virtual table and display a separate message for each inserted row, including the ProductID and ProductName values for each row. In this example, the trigger will display three separate messages, one for each row that was inserted.
Will the following trigger do the same thing?
CREATE OR ALTER TRIGGER trg_Products_Insert
ON dbo.Products
AFTER INSERT
AS
DECLARE @id int;
DECLARE @name nvarchar(50);
SELECT @id = MIN(INSERTED.ProductID) FROM INSERTED;
SELECT @name = INSERTED.ProductName FROM INSERTED WHERE ProductID = @id;
WHILE @id IS NOT NULL
BEGIN
PRINT 'A new record with ProductID ' + CAST(@id AS varchar) + ' and ProductName ' + @name + ' has been added to the Products table.'
SELECT @id = MIN(INSERTED.ProductID) FROM INSERTED WHERE ProductID > @id
SELECT @name = INSERTED.ProductName FROM INSERTED WHERE ProductID = @id
END
The trigger you provided will display a message for each row that was inserted, but it uses a different approach than the cursor-based trigger I provided earlier.
This trigger uses a WHILE loop to iterate through the rows in the "inserted" virtual table. It starts by selecting the minimum ProductID from the "inserted" table and fetching the corresponding ProductName. It then displays a message for that row and selects the next minimum ProductID that is greater than the current one. The loop continues until there are no more rows to process.
Here's an example of how the trigger would behave if you insert three rows in one INSERT statement:
INSERT INTO dbo.Products (ProductID, ProductName)
VALUES
(1, 'Product A'),
(2, 'Product B'),
(3, 'Product C');
-- Output messages:
-- A new record with ProductID 1 and ProductName Product A has been added to the Products table.
-- A new record with ProductID 2 and ProductName Product B has been added to the Products table.
-- A new record with ProductID 3 and ProductName Product C has been added to the Products table.
As you can see, the trigger displays a separate message for each row that was inserted, including the ProductID and ProductName values for each row.
Note that while this trigger will work for small to medium-sized tables, it may not be the most efficient approach for large tables or for inserting a large number of rows at once. In those cases, a cursor-based approach or other alternatives may be more suitable.