Greetings...can someone please help me resolve this problem (I'm new to SQL):
I have a table (RotaryDrilling) with an identity field (Record_Number) that uses
CREATE Trigger insert_key ON RotaryDrilling
FOR Insert AS
INSERT INTO RotaryDrillingCA (Record_Number) SELECT Record_Number FROM inserted
Order by Record_Number
to update Record_Number to a second table (RotaryDrillingCA). Using an Access (2000) form to input a new record, I get the following error:
ODBC--insert on a linked table 'dbo_RotaryDrilling' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Explicit value must be specified for identity column in table 'RotaryDrilling' when IDENTITY_INSERT is set to ON. (#545)
I don't want to specify an explicit value...what do I need to do? This is very urgent.
Thanks...
SET IDENTITY_INSERT OFF
in your session or add it prior to the INSERT statement in your trigger ..