从插入触发器后调用存储的proc [英] Call stored proc from after insert trigger

问题描述

也许是一个愚蠢的问题！

Perhaps a stupid question!

如果我从插入后触发器（T-SQL）调用存储的proc，那么如何获得刚刚插入的数据？
例如

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.

编辑：为澄清起见-我需要此方法来调用sproc而不是直接插入表中，因为sproc的作用不止一件事。我正在处理一些旧表，目前无法修改它们以适当地处理（时间/资源/旧版代码），所以我必须使用已有的表：（

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 :(

推荐答案

您可以使用插入并删除伪表：

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

给出示例表

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

编辑：抱歉，我没有提到有关调用存储过程的问题。根据marc_s的评论，请注意，插入/删除的内容可能包含多行，这会使SPROC的事务复杂化。就个人而言，我将触发器直接插入审核表中，而无需封装SPROC。但是，如果您具有SQL 2008，则可以使用表值参数，如下所示：

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';

但是，如果您使用的是SQL 2005或更低版本，则可能需要使用游标进行循环

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 2008，则可以查看更改数据捕获

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

编辑＃2 ：由于您需要调用proc，并且可以确定只插入一行...

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;

这篇关于从插入触发器后调用存储的proc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！