从插入触发器中调用存储的proc。

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.

如果我从插入触发器(T-SQL)之后调用存储的proc，那么如何获取“刚刚插入”的数据的值呢?如。

   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.

我没有任何身份列需要担心——我只是想用一些“刚刚插入的”值传递到我的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 :(

编辑:为了澄清——我需要它来调用一个sproc，而不是直接插入到表中，因为sproc做了不止一件事。我正在处理一些遗留表，目前不能正确地修改它们(时间/资源/遗留代码)，所以我必须使用我所拥有的:(

1 个解决方案

#1

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:

编辑:抱歉，我没有提到调用一个存储的Proc。根据marc_s的注释，插入/删除可以包含多个行，这使SPROC变得复杂。就个人而言，如果没有SPROC的封装，我将把触发器直接插入到审计表中。但是，如果您有SQL 2008，可以使用表值参数，比如:

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.

但是，如果您使用SQL 2005或更低的版本，您可能需要使用游标来循环插入到您的SPROC中，这是非常可怕的事情。

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

顺便说一下，如果您有SQL 2008，您可能会查看更改数据捕获。

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

编辑#2:因为你需要调用proc，如果你确定只插入一行…

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;

#1