CODEWITHSUNDEEP
sqlsql-servertriggersinsertsubstring
Tequila Scott
Tequila Scott

Reputation: 21

SQL Server Insert Trigger Improvement

NEED: Get data out of "comments-type" text/memo field and put it into separate fields upon record insert. Following example uses field TimeStamp for simplicity but uses Update AFTER record is inserted (inefficient) instead of when record is inserted. Need to do this without update.

SOLUTION: Having never used SQL triggers before, after much wailing and gnashing of teeth, finally came up with something this. It works -- but very inefficiently. Is there a better way?

EXAMPLE: Imagine a table (Castings) with TimeStamp field formatted: ”2017-12-10 18:44:54”. As records are inserted, fields automatically get populated via a trigger using substring on TimeStamp field. In this case YYYY = “2017”, MM = “12”, DD = “10”, HH = “18”, MN = “44”, SS = “54”. Using a trigger called SQLBuddy.

SCHEMA:

ID bigint (Identify Specification YES auto-increment)
TimeStamp char(19)
YYYY char(4)
MM char(2)
DD char(2)
HH char(2)
MN char(2)
SS char(2)

SQL TRIGGER CODE:

USE [SERT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[SQLBuddy] 
   ON  [dbo].[Castings]
   AFTER INSERT
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;
   UPDATE Castings
   SET YYYY = SUBSTRING(TimeStamp,1,4), MM = SUBSTRING(TimeStamp,6,2), DD = SUBSTRING(TimeStamp,9,2), HH = SUBSTRING(TimeStamp,12,2), MN = SUBSTRING(TimeStamp,15,2), SS = SUBSTRING(TimeStamp,18,2);
   SELECT TOP 1 ID FROM Castings ORDER BY ID DESC
END

Upvotes: 2

Views: 57

Answers (2)

Wes H
Wes H

Reputation: 4439

Why are you breaking the timestamp into individual components?

Even worse, why are you storing those components as characters?

To illustrate, what is the order of the following month numbers based on your schema?

'1'
'2'
'10'
'11'

And the answer is:

January
October
November
February

Using integers for the separate components will ensure proper sort order, but you'll still run into a lot of fiddly logic.

Assuming you have

YYYY=2017
MM=12
DD=31

What happens if you add one day?

If your timestamp is coming in as a string, convert it to a datetime data type. You will save so many head-aches later on by having useful, valid timestamps. In addition, you'll have no need for the trigger to separate the pieces. Just insert the whole timestamp into a single field.

Upvotes: 0

Aaron Dietz
Aaron Dietz

Reputation: 10277

Your best option is to avoid a trigger altogether and use computed columns on your table. Like this:

CREATE TABLE YourTable
   (ID bigint IDENTITY (1,1),
    YourDateTime DATETIME,
    dYear as DATEPART(YEAR, YourDateTime),
    dMonth as DATEPART(MONTH, YourDateTime),
    dDay as DATEPART(DAY, YourDateTime),
    dHour as DATEPART(HOUR, YourDateTime),
    dMinute as DATEPART(MINUTE, YourDateTime),
    dSecond as DATEPART(SECOND, YourDateTime)
   );

Click here for SQL Fiddle Example

Upvotes: 2

Related Questions