After Insert Trigger using the values from the INSERT

Question

I have the following query:

INSERT INTO LOAN (MemberSSN, VolumeID, LoanDate) 
OUTPUT inserted.MemberSSN, inserted.VolumeID, inserted.LoanDate 
VALUES ('488-40-', 2, GETUTCDATE())

I want a trigger that, upon insertion in the LOAN table, the following query is executed - an INSERT into the VOLUME_VOLUME_STATUS table:

INSERT INTO VOLUME_VOLUME_STATUS (VolumeID, StatusID, DateCreated) 
VALUES (>>previouslyInsertedVolumeID<<, 2, getutcdate())

As you can see, I am unsure how to tell SQL the VolumeID that has just been inserted, which is 2.

Below is the trigger that I wrote:

CREATE TRIGGER LoanStatusUpdate_OnLoaning
AFTER INSERT
ON LOAN
BEGIN
    INSERT INTO VOLUME_VOLUME_STATUS (VolumeID, StatusID, DateCreated) 
    VALUES (>>previouslyInsertedVolumeID<<, 2, getutcdate())
END

Therefore, what should be passed as the value for the VolumeID column so that the parameter is the one from the INSERT prior to the trigger being activated?

Figured some errors in the previous trigger. Here's the one with fixed syntax errors:

CREATE TRIGGER LoanStatusUpdate_OnLoaning
ON LOAN
AFTER INSERT
AS
    INSERT INTO VOLUME_VOLUME_STATUS (VolumeID, StatusID, DateCreated) 
    VALUES (New.VolumeID, 2, getutcdate())
GO

Kop

Answer 1

I think you just want to use inserted:

CREATE TRIGGER LoanStatusUpdate_OnLoaning ON LOAN AFTER INSERT
BEGIN
    INSERT INTO VOLUME_VOLUME_STATUS (VolumeID, StatusID, DateCreated) 
        SELECT i.VolumeId, 2, getutcdate()
        FROM inserted i;
END;

You can also pull the StatusId from inserted as well. And DateCreated could be set as a default value in VOLUME_VOLUME_STATUS.

Harwell

Thanks for the answer! However, in my first example, I provided an errononous syntax and while your answer is very useful, it gives me this error: Incorrect syntax near 'AFTER'. – Kop

I will try to adapt it to the SQL Server syntax, nonetheless,! – Kop

@Kop . . . I just copied that part of the code, because it didn't seem relevant. The ON precedes the AFTER. – Harwell

Thank you very much for your help! I will accept the answer once the cooldown expires. – Kop