VB recordset and SQL trigger primary key mix-up 1

[MustangPriMe]

I have a form with some code which adds a row to a SQL table and then uses the ID of the newly created row to go on to do other things.
The code for adding the row and getting the new ID is along the lines of:

rs.Open "tblName", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rs.AddNew
rs("fldOne") = varOne
rs("fldTwo") = varTwo
rs.Update
varID = rs("fldID")

This worked fine until I added a Insert trigger on tblName which itself creates a row in an audit table:

CREATE TRIGGER trgCreateAuditRecord ON dbo.tblName 
FOR INSERT
AS

BEGIN
	DECLARE @varNote NVARCHAR(2000)

	SET @varNote = 'New record created' + GETDATE()

	INSERT INTO tblAudit (fldDate, fldUserID, fldNote)
	VALUES (GETDATE(), dbo.fnZenithUserID(), 'Name entered - ' + @varNote)
END

Now what happens in the VB code is that after the rs.Update line, instead of rs("fldID") containing the ID of the newly inserted row in tblName, it actually contains the ID of the row inserted into tblAudit via the trigger, even despite the fact that a field called fldID does not exist in tblAudit.
I require a trigger due to that fact that some people need direct access to the tables, but need a way of getting the ID number from a newly added row in a VB recordset.

Can anyone help?

Paul

 

[cmmrfrds]

There are 2 hidden tables in sql server that are typically used in triggers. Read up on these 2 tables inserted and deleted. It is a before and after image of the inserted, update or deleted record. In your case, you probably want to pull the id from the inserted table.

Example.
Example of Mulitple record trigger.
Parent/Child relationship

create trigger trig_ModDate_InvoiceDetail
on InvoiceDetail
for update
as
declare @ID int
select @ID = invoiceID from deleted
if @@rowcount = 1
BEGIN
UPDATE InvoiceDetail
SET modifyDate = getdate()
WHERE invoiceID = @ID
END
ELSE
BEGIN
UPDATE InvoiceDetail
SET modifyDate = getdate()
WHERE InvoiceDetail.invoiceID IN
(Select InvoiceDetail.invoiceID from inserted)
END

Example of Single record trigger.

create trigger trig_ModDate_InvoiceHeader
on dbo.InvoiceHeader
for update
as
declare @ID int
select @ID = invoiceHeaderID from deleted
UPDATE InvoiceHeader
SET modifyDate = getdate()
WHERE invoiceHeaderID = @ID

 

[cmmrfrds]

I reread your question and it looks like an identity scope problem.
See this article for confirmation.

http://ericgharrison.com/Blog/archive/2004/03/30/177.aspx

Select SCOPE_IDENTITY()

See this article to see how to do a "select @@identity".

http://support.microsoft.com/kb/232144/EN-US/

It appears that ADO is giving you the last @@identity not the one in the inserted record, which you should be able to get with the Select SCOPE_IDENTITY(). You may need to send another right after the insert query to retrieve the Select SCOPE_IDENTITY().

 

[MustangPriMe]

Thanks!

I was struggling with this one but those links look like they're describing the cause.
Next problem is trying to incorporate that into my script.
I'm using an rs.Open to add the record in the first place. If I use another rs.Open "SELECT Scope_Identity()" straight afterwards, it returns a Null - presumably because it's run within another scope?

Any idea how to link the two into one?
Am I going to need to use a stored procedure to sort this one out?

Many thanks,
Paul

 

[cmmrfrds]

A stored procedure is one way to do it. You could send the id back in an output variable or another recordset.

Check out IDENT_CURRENT(yourtable)
See.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_82i1.asp

It may be worth trying this. I have not checked this, but Sql server will return multiple recordsets. It may be that if you loop after the first rs.open that an additional recordset will have the id. If that does not work, then put the update/insert in a string with the select scope afterwards in the same rs.open and then check either for a 2nd recordset or something in the return code.

rs.open "insert etc....; select scope_identity()", cn,

A semi colon will separate multiple sql statements.

These are some ideas. Let me know how you come out on this.

 

[cmmrfrds]

I did a test and this works. The 1st column is an identity column in the shippers table.

sql1 = " insert into shippers values ('arnold ','999-8888');"
Set rs.ActiveConnection = cn
rs.Open sql1, cn, 3, 3
sql1 = "select scope_identity();"
rs.Open sql1, cn, 3, 3
Debug.Print "return = "; rs(0)

 

[MustangPriMe]

Works like a dream!

Thank you - this has saved so may headaches for me.

Just to check - there's no possibility of someone else inserting a record in the time between my INSERT statement executing, and my SELECT scope_identity() line executing is there? Presumably the statements are executed within the same scope as long as there's a semi-colon separating them?
Wouldn't want to pick up the ID of someone elses inserted record!

Many thanks,
Paul

 

[cmmrfrds]

Scope means to me within the same user/ same session. I am fairly confident that you will only pick up only your identity, but you could post that question on the sql server forum.