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