Generating Hex on Insert

Question

Generating Hex on Insert

mike 57299

SSCrazy

Points: 2529
More actions
August 4, 2014 at 12:51 pm

#288731

Hi all...
I am trying to generate a hex value automatically at time of insert. The column used for the hex # is an identity column. I am getting an error that the trigger fails because the field doesn't accept nulls. My guess is that the identity column is not populated yet. Any ideas?
Below is the table and trigger.
Thanks,
Mike
CREATE TABLE [dbo].[scancode](
[scancode_id] [int] IDENTITY(1,1) NOT NULL,
[scancode] [varchar](50) NOT NULL,
[parent_object] [varchar](150) NOT NULL,
[parent_object_id] [int] NOT NULL,
[Created] [datetime] NOT NULL,
[CreatedBy] [nvarchar](128) NOT NULL,
[Modified] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](128) NOT NULL,
[RowVersion] [int] NOT NULL,
CONSTRAINT [PK_scancode] PRIMARY KEY CLUSTERED
(
[scancode_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TRIGGER dbo.scancode_scancode_autogen
ON SCANCODE
AFTER Insert
NOT FOR REPLICATION AS
SET NoCount On
SET ARITHABORT ON
DECLARE @id int
SELECT @id = scancode_id from Inserted
UPDATE SCANCODE SET scancode = master.dbo.fn_varbintohexstr(55555+@id) WHERE scancode_id = @id

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

mike 57299 (8/4/2014)
Hi all...
I am trying to generate a hex value automatically at time of insert. The column used for the hex # is an identity column. I am getting an error that the trigger fails because the field doesn't accept nulls. My guess is that the identity column is not populated yet. Any ideas?
Below is the table and trigger.
Thanks,
Mike
CREATE TABLE [dbo].[scancode](
[scancode_id] [int] IDENTITY(1,1) NOT NULL,
[scancode] [varchar](50) NOT NULL,
[parent_object] [varchar](150) NOT NULL,
[parent_object_id] [int] NOT NULL,
[Created] [datetime] NOT NULL,
[CreatedBy] [nvarchar](128) NOT NULL,
[Modified] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](128) NOT NULL,
[RowVersion] [int] NOT NULL,
CONSTRAINT [PK_scancode] PRIMARY KEY CLUSTERED
(
[scancode_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TRIGGER dbo.scancode_scancode_autogen
ON SCANCODE
AFTER Insert
NOT FOR REPLICATION AS
SET NoCount On
SET ARITHABORT ON
DECLARE @id int
SELECT @id = scancode_id from Inserted
UPDATE SCANCODE SET scancode = master.dbo.fn_varbintohexstr(55555+@id) WHERE scancode_id = @id

Quick thought, do not use the undocumented fn_varbintohexstr function, rather use the CONVERT function instead.

😎

autoexcrement SSCertifiable Points: 5956 More actions · Answer 2

Also, would this be something you could do with a persisted, computed column instead of a trigger?

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

Also be aware that the trigger is setup to handle one and only one insert at a time. If you have a multirow insert, only 1 row will ever be updated. This trigger should NOT be deployed until it is reworked.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Here is a modification of the trigger which allows for multi-row inserts and uses the CONVERT function.

😎

CREATE TRIGGER dbo.scancode_scancode_autogen

ON SCANCODE

AFTER Insert

NOT FOR REPLICATION AS

SET NoCount On

SET ARITHABORT ON

UPDATE S

SET S.scancode = CONVERT(VARCHAR(12),CONVERT(VARBINARY(4),I.scancode_id + 55555,0),1)

FROM dbo.scancode S

INNER JOIN inserted I

ON S.scancode_id = I.scancode_id

mike 57299 SSCrazy Points: 2529 More actions · Answer 5

I get the following error with the code from Ten Centeries:

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'scancode', table 'Solovue_Homart.dbo.scancode'; column does not allow nulls. INSERT fails.

The statement has been terminated.

I think it is because the primary key (an identity column) doesn't populate until after the save.

Mike

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

mike 57299 (8/4/2014)
I get the following error with the code from Ten Centeries:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'scancode', table 'Solovue_Homart.dbo.scancode'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I think it is because the primary key (an identity column) doesn't populate until after the save.
Mike

The scancode column is defined as NOT NULL. The insert has to pass any value such as a blank string ('') to bypass this.

😎

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

Eirikur Eiriksson (8/4/2014)
mike 57299 (8/4/2014)
I get the following error with the code from Ten Centeries:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'scancode', table 'Solovue_Homart.dbo.scancode'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I think it is because the primary key (an identity column) doesn't populate until after the save.
Mike
The scancode column is defined as NOT NULL. The insert has to pass any value such as a blank string ('') to bypass this.
😎

Further on the previous post, here is a chopped down sample for demonstration.

😎

USE tempdb;

GO

CREATE TABLE [dbo].[scancodeX](

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

[scancode] [varchar](50) NOT NULL,

CONSTRAINT [PK_scancodeX] PRIMARY KEY CLUSTERED

(

[scancode_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY];

GO

CREATE TRIGGER dbo.scancodeX_scancodeX_autogen

ON dbo.scancodeX

AFTER Insert

NOT FOR REPLICATION AS

SET NoCount On

SET ARITHABORT ON

UPDATE S

SET S.scancode = CONVERT(VARCHAR(12),CONVERT(VARBINARY(4),I.scancode_id + 55555,0),1)

FROM dbo.scancodeX S

INNER JOIN inserted I

ON S.scancode_id = I.scancode_id;

GO

INSERT INTO dbo.scancodeX ([scancode]) VALUES (''),(''),(''),(''),(''),(''),('');

SELECT * FROM dbo.scancodeX;

DROP TABLE dbo.scancodeX;

Results

scancode_id scancode

----------- -----------

1 0x0000D904

2 0x0000D905

3 0x0000D906

4 0x0000D907

5 0x0000D908

6 0x0000D909

7 0x0000D90A

mike 57299 SSCrazy Points: 2529 More actions · Answer 8

mike 57299

SSCrazy

Points: 2529

August 4, 2014 at 11:06 pm

#1734996

Got it. It works!

Thank you!

Mike

CodeMuddler SSC Eights! Points: 925 More actions · Answer 9

I have to agree with what autoexcrement said earlier, why not get rid of the trigger altogether and use a persisted computed column?

USE tempdb

GO

CREATE TABLE [dbo].[scancodeX](

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

, [scancode] AS CONVERT(VARCHAR(12),CONVERT(VARBINARY(4), scancode_id + 55555,0),1) PERSISTED

, [parent_object] varchar(150) NOT NULL

, [parent_object_id] [int] NOT NULL

, [Created] [datetime] NOT NULL

, [CreatedBy] [nvarchar](128) NOT NULL

, [Modified] [datetime] NOT NULL

, [ModifiedBy] [nvarchar](128) NOT NULL

, [RowVersion] [int] NOT NULL

CONSTRAINT [PK_scancodeX] PRIMARY KEY CLUSTERED

(

[scancode_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY];

GO

INSERT INTO dbo.scancodeX ([parent_object], [parent_object_id], [Created], [CreatedBy], [Modified], [ModifiedBy], [RowVersion])

VALUES

('parent_object', 123, CURRENT_TIMESTAMP, 'createdby', CURRENT_TIMESTAMP, 'modifiedby', 1)

,('parent_object', 123, CURRENT_TIMESTAMP, 'createdby', CURRENT_TIMESTAMP, 'modifiedby', 1)

,('parent_object', 123, CURRENT_TIMESTAMP, 'createdby', CURRENT_TIMESTAMP, 'modifiedby', 1);

SELECT * FROM dbo.scancodeX;

DROP TABLE dbo.scancodeX;

blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 10

CodeMuddler (8/5/2014)
I have to agree with what autoexcrement said earlier, why not get rid of the trigger altogether and use a persisted computed column?

I agree, in this case the computed column is not only a cleaner solution but more than 2 times quicker than the trigger.

😎