Tsql – Call stored proc from after insert trigger

stored-procedurestriggerstsql

Perhaps a stupid question!

If I call a stored proc from an After Insert trigger (T-SQL) – then how do I get the values of the "just inserted" data?
e.g.

   CREATE TRIGGER dbo.MyTrigger
    ON  dbo.MyTable 
     AFTER INSERT
    AS 
     BEGIN

       EXEC createAuditSproc 'I NEED VALUES HERE!'

I don't have any identity columns to worry about – I just want to use some of the "just inserted" values to pass into my sproc.

Edit: For clarification – I need this to call a sproc and not do a direct insert to the table, since the sproc does more than one thing. I'm working with some legacy tables I can't currently amend to do things 'properly' (time/resource/legacy code), so I have to work with what I have 🙁

Best Answer

You get to the newly 'changed' data by using the INSERTED and DELETED pseudo-tables:

CREATE TRIGGER dbo.MyTrigger     
    ON  dbo.MyTable       
    AFTER INSERT     
AS       
    BEGIN         
        INSERT INTO myTableAudit(ID, Name)
        SELECT i.ID, i.Name
           FROM inserted i;
    END

Given the example tables

create table myTable
(
    ID INT identity(1,1),
    Name varchar(10)
)
GO

create table myTableAudit
(
    ID INT,
    Name varchar(10),
    TimeChanged datetime default CURRENT_TIMESTAMP
)
GO

Edit : Apologies, I didn't address the bit about calling a Stored Proc. As per marc_s's comment, note that inserted / deleted can contain multiple rows, which complicates matters with a SPROC. Personally, I would leave the trigger inserting directly into the audit table without the encapsulation of a SPROC. However, if you have SQL 2008, you can use table valued parameters, like so:

CREATE TYPE MyTableType AS TABLE
(
    ID INT,
    Name varchar(10)
);
GO

CREATE PROC dbo.MyAuditProc @MyTableTypeTVP MyTableType READONLY
AS
    BEGIN
        SET NOCOUNT ON;

        INSERT INTO myTableAudit(ID, Name)
        SELECT mtt.ID, mtt.Name
            FROM @MyTableTypeTVP mtt;
    END
GO

And then your trigger would be altered as like so: 

ALTER TRIGGER dbo.MyTrigger
    ON  dbo.MyTable       
    AFTER INSERT     
AS       
    BEGIN         
        SET NOCOUNT ON;

        DECLARE @MyTableTypeTVP AS MyTableType;

        INSERT INTO @MyTableTypeTVP(ID, Name)
        SELECT i.ID, i.Name
            FROM inserted i;

        EXEC dbo.MyAuditProc @MyTableTypeTVP;
    END

you can then test that this works for both a single and multiple inserts

insert into dbo.MyTable values ('single');

insert into dbo.MyTable 
    select 'double'
union
    select 'insert';

However, if you are using SQL 2005 or lower, you would probably need to use a cursor to loop through inserted passing rows to your SPROC, something too horrible to contemplate.

As a side note, if you have SQL 2008, you might look at Change Data Capture

Edit #2 : Since you need to call the proc, and if you are certain that you only insert one row ...

ALTER TRIGGER dbo.MyTrigger
    ON  dbo.MyTable       
    AFTER INSERT     
AS       
    BEGIN         
        SET NOCOUNT ON;

        DECLARE @SomeInt INT;
        DECLARE @SomeName VARCHAR(10);

        SELECT TOP 1 @SomeInt = i.ID, @SomeName = i.Name
        FROM INSERTED i;

        EXEC dbo.MyAuditProc @SomeInt, @SomeName;
    END;
Related Topic