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