Can you set a variable in a trigger in SQL to equal a variable in another table?

Question

I'm trying to automatically update a table when new rows have been inserted but my trigger is just putting null results into the table.

Basically I want to make CampaignID = CampaignID in the Signals table

ALTER TRIGGER UpdateSignalCounts
ON Signals
AFTER INSERT 
AS 
    DECLARE @CampaignID INT

    SET @CampaignID FOR SELECT CampaignID FROM Signals
BEGIN
    INSERT INTO SignalCounts (NumberRecieved, NumberOpened, NumberClicked, NumberForwarded, NumberUnsubscribed, NumberBounced, CampaignID)
       (SELECT 
            COUNT(Recieved), COUNT(Opened), COUNT(Clicked), COUNT(Forwards), 
            COUNT(Unsubscribed), COUNT(Bounced), @CampaignID 
        FROM 
            Signals 
        WHERE 
            CampaignID = @CampaignID);
END

am surprised that sql server allowed you to create a trigger with that syntax — Pரதீப்
– Pரதீப், Commented Apr 13, 2018 at 15:57
this assumes lots of things....one being that you always insert one row into that table — Lamak
– Lamak, Commented Apr 13, 2018 at 15:57

Thom A · Accepted Answer · 2018-04-13 16:02:53Z

2

Assuming that I understand your trigger correctly, then a far better solution would be:

ALTER TRIGGER UpdateSignalCounts ON Signals
AFTER INSERT AS 
BEGIN

    INSERT INTO SignalCounts(NumberRecieved,NumberOpened,NumberClicked,NumberForwarded,NumberUnsubscribed,NumberBounced, CampaignID)
    SELECT COUNT(Recieved),
           COUNT(Opened),
           COUNT(Clicked),
           COUNT(Forwards),
           COUNT(Unsubscribed),
           COUNT(Bounced)
           S.CampaignID
    FROM Signals S
    WHERE S.CampaignID IN (SELECT i.CampaignID
                           FROM inserted)
    GROUP BY S.CampaignID;

END

answered Apr 13, 2018 at 16:02

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can you set a variable in a trigger in SQL to equal a variable in another table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related