MySQL Trigger User Declared Variables

Question

Hi I'm trying to declare some user defined variables (EX: @var) for a SQL trigger that Sums the values in the selected rows for a given subscriberID. However, I'm getting a Syntax error on my DECLARE statement.

Here is my code:

    DELIMITER //
    CREATE TRIGGER trg_subscribers_points

    AFTER UPDATE ON subscribers_points FOR EACH ROW
    BEGIN

    DECLARE @Sub_ID bigint, @Col1 bigint, @Col2 bigint, @Col3 bigint

    (select @Sub_ID = subscriber_id, @Col1 = action, @Col2 = share, @Col3 = viral FROM subscribers_points)

    update subscribers_points set total_points = @Col1 + @Col2 + @Col3 where subscriber_id = @Sub_ID

    END//

DELIMITER ;

Advice? Thanks in advance.

UPDATED CODE:

DELIMITER //

CREATE TRIGGER trg_subscribers_points

BEFORE UPDATE 
ON subscribers_points 
FOR EACH ROW

BEGIN

SET NEW.total_points = (action + share + viral);

END//
DELIMITER ;

^This returns a "Unknown Column 'action' in 'field list'" Error.

Action, share, and viral are all column names in the table subscribers_points, so I'm not sure why it's returning this error.

Answer 1

I'm not sure what your error is, but you don't need variables for this purpose. You have several issues. For one thing, you shouldn't update the row again after you have updated it. And, your trigger doesn't actually refer to the data being changed. I suspect you want something like this:

DELIMITER //
CREATE TRIGGER trg_subscribers_points
BEFORE UPDATE ON subscribers_points FOR EACH ROW
BEGIN
    set new.total_points = (new.action + new.share + new.viral)
END//

DELIMITER ;

Note that the trigger has been changed to a "before update" trigger.