It just don''t work: Insert last ID into related table (trigger)

Question

Post reply

It just don''t work: Insert last ID into related table (trigger)

wer

Grasshopper

Points: 15
More actions
July 20, 2005 at 7:38 am

#167595

Hello, Hello,
For my SQL Server back-end/ Access front-end Database I have the following
questions:
When a record is inserted into the main table (tbl_REJ) via a Access form, I
also have to insert that number into a numeric field of a related table
(tbl_RSP_PRSNS).
I know this can be done by means of a trigger but not sure how exactly do
this.
This issue has been discussed many times in this forum, but the suggestions I
have found did not seems
to work. I'm completely out of ideas
Please help.
Aad

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Hi - if you want the syntax for a trigger on tbl_REJ it would be something like this:

CREATE TRIGGER InsertNumber ON tbl_REJ

FOR INSERT

AS

DECLARE @NumberID Int

SET @NumberID = SELECT MAX(IDfield) from Inserted

OR

SET @NumberID = Scope_Identity()

Insert into tbl_RSP_PRSNS(NumberField) values(@NumberID)

**ASCII stupid question, get a stupid ANSI !!!**

wer Grasshopper Points: 15 More actions · Answer 2

Hi,

Thanks for your time helping me out.

I add a trigger to my main tabel 'REJ' thanks for Your suggestion.

------------------------------------------------------------

CREATE TRIGGER REJ_Trigger1

ON dbo.REJ

FOR INSERT

AS

DECLARE @NumberID Int

SET @NumberID = @@IDENTITY

Insert into RSP_PRSNS_details(RSP_PRSNS_details_REJ_ID) values(@NumberID)

------------------------------------------------------------

When I manually add a record in table 'REJ' the trigger inserts a new record into table 'RSP_PRSNS_details' and in a int column it's ID number.

When I try to do this in Access (Front-end) directly into table 'REJ' it fails.

When I add some data into in the new record and I then Save that record (shft.+ Enter), an error message appears which says:

"The data was added to the database but the data won't be displayed in the

form because it doesn't satisfy the criteria in the underlying record

source".

Then the record will delete and in the related table 'RSP_PRSNS_details' no record is inserted.

What goes wrong?

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

I changed @@IDENTITY TO SCOPE_IDENTITY() (apologies about that...)

A few options:

a) change to scope_identity and see what happens ?!

Also try:

b) SET @NumberID = IDENT_CURRENT('dbo.REJ')

&

c) SET @NumberID = SELECT MAX(IDfield) from Inserted

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

Hey wer - I just found out that your error is caused when using sql server 7.0 - is this the version you're using ?!

Here's the url that has more info:

http://support.microsoft.com/default.aspx?scid=kb;en-us;291091

**ASCII stupid question, get a stupid ANSI !!!**

wer Grasshopper Points: 15 More actions · Answer 5

wer

Grasshopper

Points: 15

July 26, 2005 at 1:00 am

#577064

Hi,

Thanks for your time. I'll check that out.