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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt   count             BEGIN                 -- Get the next rAgent ID                 SELECT TOP 1                          rAgent ID   rAgent ID                 FROM    inserted                 WHERE   rAgent ID  gt   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

[sql-server] Call Stored Procedure within Create Trigger in SQL Server

Examples related to sql-server

Examples related to triggers