在SQL Server中创建触发器时调用存储过程

Question

我有一个名为insert2Newsletter的存储过程，其中包含参数。

(@sex nvarchar(10),
@f_name nvarchar(50),
@l_name nvarchar(70),
@email nvarchar(75),
@ip_address nvarchar(50),
@hotelID int,
@maArt nchar(2))

我想在插入触发器中调用这个存储过程。如何从"inserted"表中获取相应的字段，并在触发器中调用insert2Newsletter？

我尝试了以下方法，但没有成功：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER RA2Newsletter 
   ON  [dbo].[Reiseagent] 
   AFTER INSERT
AS 
DECLARE @rAgent_Name nvarchar(50),
DECLARE @rAgent_Email nvarchar(50),
DECLARE @rAgent_IP nvarchar(50),
DECLARE @hotelID int

BEGIN
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    Select @rAgent_Name=rAgent_Name, @rAgent_Email=rAgent_Email, @rAgent_IP=rAgent_IP, @hotelID=hotelID  From Inserted
    EXEC insert2Newsletter '','',@rAgent_Name,@rAgent_Email,rAgent_IP,@hotelID,'RA' 


END
GO

非常感谢您的反馈...祝好...

- user168507

Answer 1

我认为你需要循环遍历“inserted”表，该表包含所有已更新的行。你可以使用一个WHERE循环，或者如果你的主键是GUID，可以使用WITH语句。这种方法对我来说比较简单，所以这是我的示例。我们使用这种方法，所以我知道它可以正常工作。

ALTER TRIGGER [dbo].[RA2Newsletter] ON [dbo].[Reiseagent]
    AFTER INSERT
AS
        -- This is your primary key.  I assume INT, but initialize
        -- to minimum value for the type you are using.
        DECLARE @rAgent_ID INT = 0

        -- Looping variable.
        DECLARE @i INT = 0

        -- Count of rows affected for looping over
        DECLARE @count INT

        -- These are your old variables.
        DECLARE @rAgent_Name NVARCHAR(50)
        DECLARE @rAgent_Email NVARCHAR(50)
        DECLARE @rAgent_IP NVARCHAR(50)
        DECLARE @hotelID INT
        DECLARE @retval INT

    BEGIN 
        SET NOCOUNT ON ;

        -- Get count of affected rows
        SELECT  @Count = Count(rAgent_ID)
        FROM    inserted

        -- Loop over rows affected
        WHILE @i < @count
            BEGIN
                -- Get the next rAgent_ID
                SELECT TOP 1
                        @rAgent_ID = rAgent_ID
                FROM    inserted
                WHERE   rAgent_ID > @rAgent_ID
                ORDER BY rAgent_ID ASC

                -- Populate values for the current row
                SELECT  @rAgent_Name = rAgent_Name,
                        @rAgent_Email = rAgent_Email,
                        @rAgent_IP = rAgent_IP,
                        @hotelID = hotelID
                FROM    Inserted
                WHERE   rAgent_ID = @rAgent_ID

                -- Run your stored procedure
                EXEC insert2Newsletter '', '', @rAgent_Name, @rAgent_Email,
                    @rAgent_IP, @hotelID, 'RA', @retval 

                -- Set up next iteration
                SET @i = @i + 1
            END
    END 
GO

我希望这对你有所帮助。干杯！

- bopapa_1979

---

请注意，为了使其正常工作，您需要将“WHILE @i < @count”更改为“WHILE @i <= @count”，或更改初始化以使“i int = 0”，否则它实际上不适用于其中一个条目。希望您不会对所有触发器都使用此方法;-) - enderland

@enderland - 你是正确的。我已经更新了示例代码。感谢你的发现！ - bopapa_1979

Answer 2

Finally ...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO
ALTER TRIGGER [dbo].[RA2Newsletter] 
   ON  [dbo].[Reiseagent] 
   AFTER INSERT
 AS
    declare
    @rAgent_Name nvarchar(50),
    @rAgent_Email nvarchar(50),
    @rAgent_IP nvarchar(50),
    @hotelID int,
    @retval int


BEGIN
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    Select 
        @rAgent_Name = rAgent_Name,
        @rAgent_Email = rAgent_Email,
        @rAgent_IP = rAgent_IP,
        @hotelID = hotelID
    From Inserted
    
    EXEC insert2Newsletter 
        '',
        '',
        @rAgent_Name,
        @rAgent_Email,
        @rAgent_IP,
        @hotelID,
        'RA',
        @retval
END

- user168507

---

如果您正在插入多个记录，那么会发生什么？这将导致错误。 - starskythehutch

@starskythehutch，触发器不应该在每次插入时触发，这样您就不需要遍历Inserted中的每一行了。除非触发器仅在存储过程完成所有插入（如批处理）时才触发？ - Raidenlee

虽然这是一条八年前的评论，但我还是要回复。触发器只针对插入操作执行一次，而不是每行数据执行一次。一个插入语句可以操作多行数据。 - starskythehutch

Answer 3

以下方法可以解决问题 - 仅限于SqlServer。

Alter TRIGGER Catagory_Master_Date_update ON Catagory_Master AFTER delete,Update
AS
BEGIN

SET NOCOUNT ON;

Declare @id int
DECLARE @cDate as DateTime
    set @cDate =(select Getdate())

select @id=deleted.Catagory_id from deleted
print @cDate

execute dbo.psp_Update_Category @id

END

---

Alter PROCEDURE dbo.psp_Update_Category
@id int
AS
BEGIN

DECLARE @cDate as DateTime
    set @cDate =(select Getdate())
    --Update Catagory_Master Set Modify_date=''+@cDate+'' Where Catagory_ID=@id   --@UserID
    Insert into Catagory_Master (Catagory_id,Catagory_Name) values(12,'Testing11')
END 

---

- Nikhilesh Kumar Patidar

Answer 4

在 EXEC 中传递了未定义的 rAgent_IP 参数，而不是本地变量 @rAgent_IP。

但是，如果执行多记录 INSERT 语句，则此触发器仍将失败。

- devio

---

我调整了@rAgent_IP。但仍然不起作用。多记录插入将不会。 - user168507