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.
😎