Friday, April 24, 2015

Instead of Insert, Verify not inserting into identity column.

I am writing an Instead of Insert trigger. I would like to fire an error when inserting into an 'Identity' column.  Since UPDATE([ColumnName]) always returns TRUE for insert statements, is there an easy/fast way around this? I don't want to use: 

IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NULL))

here is my pseudo-code...

CREATE VIEW [org].[Assets]
WITH SCHEMABINDING
AS
SELECT
        [AS].[AS_ID],  -- Identity field in base table
        [AS].[Tag],
        [AS].[Name]
FROM [org].[AS_Assets_Rewind](DEFAULT, DEFAULT, DEFAULT)        [AS];
GO

CREATE TRIGGER [org].[Assets_Insert]
ON [org].[Assets]
INSTEAD OF INSERT
AS
BEGIN

        SET NOCOUNT ON;
        SET XACT_ABORT ON;

        -- How does this statment need to be written to throw the error?
        --      UPDATE([AS_ID]) always returns TRUE
        IF(UPDATE([AS_ID]))
                RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;

        -- Is there a faster/better method than this?
        IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NOT NULL))
                RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;

        -- Do Stuff

END;

-- Should error for inserting into [AS_ID] field (which is an identity field)
INSERT INTO [org].[Assets]([AS_ID], [Tag], [Name])
VALUES(1, 'f451', 'Paper burns'),
        (2, 'k505.928', 'Paper burns in Chemistry');

-- No error should occur
INSERT INTO [org].[Assets]([Tag], [Name])
VALUES('f451', 'Paper burns'),
        ('k505.928', 'Paper burns in Chemistry');


No comments:

Post a Comment