SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generating Hex on Insert


Generating Hex on Insert

Author
Message
mike 57299
mike 57299
Right there with Babe
Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)

Group: General Forum Members
Points: 737 Visits: 564
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


Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94328 Visits: 20688
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.
Cool
autoexcrement
autoexcrement
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2582 Visits: 956
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
Jeff Moden
SSC Guru
SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)

Group: General Forum Members
Points: 510068 Visits: 44290
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94328 Visits: 20688
Here is a modification of the trigger which allows for multi-row inserts and uses the CONVERT function.
Cool

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
mike 57299
Right there with Babe
Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)

Group: General Forum Members
Points: 737 Visits: 564
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
Eirikur Eiriksson
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94328 Visits: 20688
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.
Cool
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94328 Visits: 20688
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.
Cool


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

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
mike 57299
Right there with Babe
Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)

Group: General Forum Members
Points: 737 Visits: 564
Got it. It works!

Thank you!
Mike
CodeMuddler
CodeMuddler
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 244
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)
,('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)
,('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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search