gpt4 book ai didi

sql-server - 在 SQL Server 中的创建触发器中调用存储过程

转载 作者:行者123 更新时间:2023-12-02 03:59:49 26 4
gpt4 key购买 nike

我有一个名为 insert2Newsletter 的存储过程,带有参数

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

我想在插入触发器中调用此存储过程。如何从插入中检索相应的字段以及如何在触发器中调用 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

非常感谢您的反馈...问候...

最佳答案

我认为您必须循环遍历“插入”表,其中包含所有已更新的行。如果您的主键是 GUID,则可以使用 WHERE 循环或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

我当然希望这对您有所帮助。干杯!

关于sql-server - 在 SQL Server 中的创建触发器中调用存储过程,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3768278/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com