Using SCOPE_IDENTITY from insert on tableX to insert tableY

[luisdev]

The spUser procedure below will be used to insert records into the
tblAppUsers table.
SCOPE_IDENTITY() then gives me the ID of the record that was inserted
into tblAppUsers.

CREATE TABLE tblAppUsers (
id bigint IDENTITY(1,1) NOT NULL
firstname nvarchar(100),
lastname nvarchar(100)
)

CREATE PROCEDURE spUser
@firstname NVARCHAR(100),
@lastname NVARCHAR(100),
AS
BEGIN
SET NOCOUNT ON

DECLARE @RecordID BIGINT;

INSERT INTO tblAppUsers (
firstname,
lastname
)
SELECT
@firstname,
@lastname
);
SELECT @RecordID = SCOPE_IDENTITY();
SELECT @RecordID;
END

I would like to add a trigger to tblAppUsers that does an insert/
update on another table whenever a record is added to tblAppUsers
using the SCOPE_IDENTITY() value from tblAppUsers.

CREATE TRIGGER trigLoadTableIDs
ON tblAppUsers
AFTER INSERT
AS
BEGIN
INSERT INTO tblNewRecords (recordinfo) VALUES (@RecordID);
END

But the problem is, how do I pass the @RecordID that SCOPE_IDENTITY()
returned to the trigCheckUserProfile trigger?
SCOPE_IDENTITY() seems to be "out of scope" when I try to do the
insert - instead of containing the SCOPE_IDENTITY() value @RecordID is
empty (null).
How do I pass the @RecordID value to the trigLoadTableIDs trigger?

CREATE TABLE tblAppUsers (
id bigint IDENTITY(1,1) NOT NULL
recordinfo bigint
)

[Bob Barrows]

Post by luisdev
returned to the trigCheckUserProfile trigger?
SCOPE_IDENTITY() seems to be "out of scope" when I try to do the
empty (null).

Use the builtin Inserted table in the trigger to get the values of the
inserted record

[rpresser]

On Wednesday, May 30, 2012 10:41:56 AM UTC-4, luisdev wrote:
Do thus:
CREATE TRIGGER trigLoadTableIDs
ON tblAppUsers
AFTER INSERT
AS
BEGIN
INSERT INTO tblNewRecords (recordinfo)
SELECT INSERTED.id from INSERTED;
END


Remember that a trigger fires ONCE for a multirow insert, with the INSERTED table containing all rows that were inserted. Therefore the trigger should handle all inserted rows at once; thus the INSERT..SELECT instead of INSERT..VALUES.