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