Tag: 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.

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

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

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.

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.

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.

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.

There is a SET command in SQL Server that changes how much data is returned from some fields. This short post shows what I learned about the SET TEXTSIZE command.

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 start with a little code. I actually created a table that looks like this:

CREATE TABLE [dbo].[Beer]

(

[BeerID] [int] NOT NULL IDENTITY(1, 1),

[BeerName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[brewer] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[beerdescription] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

I added data so that I have something in here:

INSERT INTO dbo.Beer
   (BeerName, brewer, beerdescription)

VALUES
  (1,    'Becks', 'Interbrew', 'Beck''s is a German-style pilsner beer known for its golden color, full-bodied taste, and a crisp, clean finish with floral and fruity hop aromas, brewed according to the German purity law')

,(2,    'Fat Tire', 'New Belgium    Toasty malt, gentle sweetness, flash of fresh hop bitterness. The malt and hops are perfectly balanced.')

,(3,    'Mac n Jacks', 'Mac & Jack''s Brewery', 'This beer erupts with a floral, hoppy taste, followed by a well rounded malty middle, finishing with a nicely organic hop flavor. Locally sourced two row grain and a blend of specialty malts give our amber its rich taste.')

,(4,    'Alaskan Amber', 'Alaskan Brewing', 'Alaskan Brewing Amber Ale is an "alt" style beer, meaning it''s fermented slowly and at colder temperatures, resulting in a well-balanced, richly malty, and long-lasting flavor profile with a clean, pleasing aftertaste.')

,(8,    'Kirin', 'Kirin Brewing', 'Kirin Ichiban is a Lager-type beer, which means it is fermented at low temperatures and offers a light and refreshing texture with a smooth and balanced flavor.')

Now, let’s see what this setting does.

SET TEXTSIZE

This command changes the behavior of SELECT queries and controls how much data is returned in bytes. The setting is the command with an integer after it. The max value is 2GB.

I’ll run a normal query, then I’ll set a smaller size and repeat the query. Notice how the results differ below. I get less data in the second query.

What this setting does is limit the number of bytes from some fields. I only have 20 characters from each description.

Let’s do one more query. I’ll lower the value to 5.

Note that while the description is very low, the name and brewer are not cut off.

The explanation is that this works on the max types: varchar(max), nvarchar(max), varvinary(max), text, ntext, and image. Non-max fields aren’t affected.

Also note that the default setting from the SQL Native Client and ODBC driver is –1, for unlimited data. That explains why I haven’t noticed this as I’m often using an app that uses one of those. IF you set this to 0, then it defaults to 4KB.

A nice way to prevent apps from grabbing tons of data unless they need it, though you’d certainly need to help users understand why they weren’t getting all the data expected. I think long fields (or image/audio/etc. data) would need a “get more” or “get all” item in software to reset this and return the full value in the table.

SQL New Blogger

This was a function I ran across, whose purpose I had no idea about. I read it, experimented, and in about 30 minutes had put together this demo and post. Easy to do and quick.

I learned something, and I’m sharing this with potential employers. You could as well, with a little effort.