Click here to monitor SSC
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
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 507
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
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6697 Visits: 17691
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
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 777
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45028 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6697 Visits: 17691
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
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 507
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
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6697 Visits: 17691
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
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6697 Visits: 17691
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
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 507
Got it. It works!

Thank you!
Mike
CodeMuddler
CodeMuddler
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 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