Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Generating Hex on Insert Expand / Collapse
Author
Message
Posted Monday, August 4, 2014 12:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:51 PM
Points: 138, Visits: 370
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

Post #1599367
Posted Monday, August 4, 2014 1:03 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 2,003, Visits: 5,475
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.
Post #1599371
Posted Monday, August 4, 2014 1:05 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:14 PM
Points: 153, Visits: 590
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
Post #1599372
Posted Monday, August 4, 2014 2:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1599389
Posted Monday, August 4, 2014 9:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 2,003, Visits: 5,475
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

Post #1599457
Posted Monday, August 4, 2014 10:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:51 PM
Points: 138, Visits: 370
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
Post #1599459
Posted Monday, August 4, 2014 10:06 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 2,003, Visits: 5,475
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.
Post #1599460
Posted Monday, August 4, 2014 10:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 2,003, Visits: 5,475
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
Post #1599466
Posted Monday, August 4, 2014 11:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:51 PM
Points: 138, Visits: 370
Got it. It works!

Thank you!
Mike
Post #1599470
Posted Tuesday, August 5, 2014 10:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 10:19 AM
Points: 36, Visits: 238
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
Post #1600033
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse