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.

Gordon Linoff · Accepted Answer · 2014-12-02T22:51:16.883

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.

edited Dec 02 '14 at 22:51

answered Dec 02 '14 at 21:23

Gordon Linoff

1,242,037
58
646
786

Why BEFORE? If any of those values are updated, they need to be added to the total amount after the update. For ease-of-use, I used the query above and when I updated an Item in the table, the trigger gave me this error: "Can't update table 'subscribers_points' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." Ideas? – wUmpage Dec 02 '14 at 21:32
You want a `BEFORE` trigger so all the columns get updated at the same time. I don't know why I left the `update` in the trigger. That was causing the problem to begin with. You just need to set `new.total_points`. – Gordon Linoff Dec 02 '14 at 21:41
Apparently "Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. when trigger fires it may lock the table." So I'm not even sure how to call this trigger now. – wUmpage Dec 02 '14 at 21:41
Gordon thank you for the help so far, I updated my code up top. Trigger no longer loops but it cannot find the columns values to add for some reason. Please view my edits above. – wUmpage Dec 02 '14 at 21:47
@wUmpage . . . At least I know what I was thinking when I left in the `update`. You need to use `new.` for the new values of the columns. – Gordon Linoff Dec 02 '14 at 22:51
I figured it out :) Thank you very much Gordon – wUmpage Dec 02 '14 at 22:55

MySQL Trigger User Declared Variables

1 Answers1