Call Stored Procedure within Create Trigger in SQL Server

Question

I have a stored procedure named insert2Newsletter with parameters

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

I want call this stored procedure in an insert trigger. How do I retrieve the corresponding fields from inserted and how do i call insert2Newsletter within the trigger?

I tried without success:

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

thx a lot for your feedback... greetings...

bopapa_1979 · Answer 1 · 2016-02-19T00:50:12.933

I think you will have to loop over the "inserted" table, which contains all rows that were updated. You can use a WHERE loop, or a WITH statement if your primary key is a GUID. This is the simpler (for me) to write, so here is my example. We use this approach, so I know for a fact it works fine.

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

I sure hope this helps you out. Cheers!

Note for this to work you need to change `WHILE @i < @count` to be `WHILE @i <= @count` or change initialization to make `i int = 0` or it will not actually work for one of the entries, I hope you are not using this approach for all your triggers ;-) — enderland, Dec 02 '13 at 16:22
@enderland - You are correct. I have updated the example code. Thanks for catching that! — bopapa_1979, Feb 19 '16 at 00:50

score 12 · Accepted Answer · edited May 20 '22 at 00:23

12

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

edited May 20 '22 at 00:23

CarenRose

1,266
1
12
24

answered Sep 22 '10 at 10:43

user168507

871
4
16
33

10

What happens if you are inserting multiple records? This would break. – starskythehutch Aug 23 '12 at 13:35
@starskythehutch shouldn't the trigger fire on each insert so you don't need to traverse each row from Inserted? Unless the trigger only fires when ALL the Inserts from the Stored Procedure is done (like a batch)? – Raidenlee Jul 23 '20 at 16:02
3

It's an 8 year old comment, but I'll reply anyway. The trigger only fires once per insert, not once per row. An insert statement can operate on multiple rows. – starskythehutch Jul 23 '20 at 23:21

score 3 · Answer 3 · edited Aug 01 '12 at 11:02

The following should do the trick - Only 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

score 0 · Answer 4 · answered Sep 22 '10 at 10:28

0

You pass an undefined rAgent_IP parameter in EXEC instead of the local variable @rAgent_IP.

Still, this trigger will fail if you perform a multi-record INSERT statement.

answered Sep 22 '10 at 10:28

devio

36,858
7
80
143

I adjusted @rAgent_IP. Still it does not work. A multi-record INSERT will not be – user168507 Sep 22 '10 at 10:32

Call Stored Procedure within Create Trigger in SQL Server

4 Answers4

Linked