Inserting a Row into a Multiple Table Updatable View with an INSTEAD OF Trigger Defined: Access Project Complains about NULL in the IDENTITY Column

Inserting a Row into a Multiple Table Updatable View with an INSTEAD OF Trigger Defined: Access Project Complains about NULL in the IDENTITY Column

Post by Paul » Mon, 12 Nov 2001 23:29:27



I have an updatable view (made updatable through the use of an INSTEAD OF
trigger) which updates fine in Enterprise Manager save for the fact that I
had to get rid of primary key columns which in my case were IDENTITY ones:
rather than supplying an appropriate value for the field, SQL Server
complained about the presence of NULL in the column. Well, with or without
the primary-key column, the view is updatable (if I included the IDENTITY
column, I could supply any value which would do fine: next time I opened the
view, the value was replaced with the proper one in both columns - the view
consisted of two tables joined by that primary-key column which was IDENTITY
in one of them).

Now, unfortunately I have failed to make things work in an Access project.
Without primary-key fields the view is not updatable but with them Access
complains about the NULL in the IDENTITY column. Funnily enough, unlike
Enterprise Manager, Access does display 'AutoNumber' or something in the
IDENTITY column, it does show the next value when I start typing in the row,
yet when I try to save it, it complains about NULL in the column showing the
next available number.

Any ideas how this can be put right?

Thank you.

Paul

 
 
 

1. Multiple Row Insert with Instead of Insert Trigger

I have created an insert trigger to handle updating a identity-like
 field in a table.  The trigger works for a singleton insert, but not for
 a multiple row insert.  I have read that using server-side cursors is
 bad coding practice.  Can anyone give me a few hints as to how I would
 make this trigger handle the multiple row case.

 CREATE TABLE [DDR] (
  [DDR_Num] [int] NOT NULL ,
  [DDR_Type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [Distribution_Condition] [char] (1) COLLATE
 SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [Type_Code] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  [Day_Code] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [Source_Code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
 ,
  [Form_Code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
  CONSTRAINT [DDR_PK] PRIMARY KEY  CLUSTERED
  (
   [DDR_Num]
  )  ON [PRIMARY] ,
  CONSTRAINT [FK_DDR_Data_Source_Code] FOREIGN KEY
  (
   [Source_Code]
  ) REFERENCES [Data_Source] (
   [Source_Code]
  ) ON UPDATE CASCADE ,
  CONSTRAINT [FK_DDR_Output_Form_Code] FOREIGN KEY
  (
   [Form_Code]
  ) REFERENCES [Output_Form] (
   [Form_Code]
  ) ON UPDATE CASCADE ,
  CONSTRAINT [FK_DDR_Test_Program_Day_Code] FOREIGN KEY
  (
   [Day_Code]
  ) REFERENCES [Test_Program_Day] (
   [Day_Code]
  ) ON UPDATE CASCADE ,
  CONSTRAINT [FK_DDR_Test_Program_Type_Code] FOREIGN KEY
  (
   [Type_Code]
  ) REFERENCES [Test_Program_Type] (
   [Type_Code]
  ) ON UPDATE CASCADE
 ) ON [PRIMARY]
 GO

 SET QUOTED_IDENTIFIER ON
 GO
 SET ANSI_NULLS ON
 GO

 create trigger InsteadDDRTrigger on dbo.DDR
 INSTEAD OF INSERT
 AS
 BEGIN



  from ddr

 INSERT INTO [DDR]

             DDR_Type,
             Distribution_Condition,
         Type_Code,
     Day_Code,
     Source_Code,
     Form_Code
  FROM Inserted

 END

 GO
 SET QUOTED_IDENTIFIER OFF
 GO
 SET ANSI_NULLS ON
 GO

 TIA,
 Peter

 --
 Peter Crickman

 Johns Hopkins University
 Applied Physics Laboratory
 Laurel, MD 20723-6099

2. DISTINCT not allowed on image data types?

3. INSTEAD OF INSERT trigger to insert into 2 tables linked in a view

4. Table changes break views

5. INSTEAD OF Triggers to update multiple base tables in a SQL Server 2000 view

6. Forms stuck in read mode

7. Inserting a Row with an IDENTITY Column into an Multiple Table Updatable View with an INSTEAD OF Trigger Defined Fails: Access Project Complains about NULL in the IDENTITY Column

8. 14636-VA-McLean-UNIX-C-X-windows-Motif-SunOS-SQL-VMS-ORACLE-Database Design-Senior Software Engineer

9. Instead of insert trigger with a null primary key in a view

10. Resolution - Re: ADP uses underlying table for UPDATE, INSERT instead of view

11. ADP uses underlying table for UPDATE, INSERT instead of view

12. TRIGGER INSTEAD OF UPDATE and inserted and deleted tables

13. Trigger - multiple inserted rows