Get the inserted row causing the execution of the trigger-CodePudding

I want to get the value of the row causing the execution of the trigger. So I can pass it (as a parameter) to a stored procedure.

The stored procedure accepts as input a table type which is defined in the script below:

CREATE TYPE PersonTableType AS TABLE
(
    Id int primary key,
    FirstName nvarchar(50),
    LastName nvarchar(50)
)

The procedure (insert in the ArchivePerson table the inserted row from the trigger)

Create PROCEDURE sp1
@PersonType PersonTableType Readonly

As
BEGIN
    Insert Into ArchivePerson 
    Select * From @PersonType
END

How do I declare my trigger? I tried something like:

Alter TRIGGER insertPerson 
   ON  Person 
   AFTER Insert
AS 
BEGIN
    declare @PersonType PersonTableType;

    ??

    Exec sp1 @PersonType

END

CodePudding user response：

The inserted table has the rows which were, well, inserted. It has the same columns with your original [Person] table, so use the appropriate columns:

Alter TRIGGER insertPerson 
   ON  Person 
   AFTER Insert
AS 
BEGIN
    declare @PersonType PersonTableType;

    insert @PersonType(Id,FirstName,LastName)
        select <corresponding columns>
        from inserted

    Exec sp1 @PersonType

END