MySQL question

I wish to keep a running total for each row in a MySQL table.

In a BEFORE INSERT or BEFORE UPDATE trigger in MySQL, can the NEW reference participate in a join as if it were a single-row table?  (like this):

USE `CHECKBOOK`;
DELIMITER //

CREATE TRIGGER INS_ENTRY
BEFORE INSERT
ON ENTRY
FOR EACH ROW
BEGIN
 DECLARE ENTRY_TYPE AS SMALLINT;
 DECLARE PREV_TOTAL AS FLOAT;
 SET PREV_TOTAL = 0;
 SELECT `VALUE`
 FROM NEW INNER JOIN TYPE ON NEW.TYPE = TYPE.idTYPE
 INTO ENTRY_TYPE;
 SELECT LINE_TOTAL
 FROM ENTRY
 ORDER BY ENTRY_NO
 LIMIT 1
 INTO PREV_TOTAL;
 IF ENTRY_TYPE = 1
 THEN
 SET NEW.LINE_TOTAL = PREV_TOTAL + NEW.AMOUNT
 END IF;
 IF ENTRY_TYPE = -1
 THEN
 SET NEW.LINE_TOTAL = PREV_TOTAL - NEW.AMOUNT
 END IF;
 IF ENTRY_TYPE = 0
 THEN
 SET NEW.LINE_TOTAL = PREV_TOTAL
 END IF;
END;
//

CREATE TRIGGER UPD_ENTRY
BEFORE UPDATE
ON ENTRY
FOR EACH ROW
BEGIN
 DECLARE ENTRY_TYPE AS SMALLINT;
 DECLARE PREV_TOTAL AS FLOAT;
 SET PREV_TOTAL = 0;
 SELECT `VALUE`
 FROM NEW INNER JOIN TYPE ON NEW.TYPE = TYPE.idTYPE
 INTO ENTRY_TYPE;
 SELECT LINE_TOTAL
 FROM ENTRY
 ORDER BY ENTRY_NO
 LIMIT 1
 INTO PREV_TOTAL;
 IF ENTRY_TYPE = 1
 THEN
 SET NEW.LINE_TOTAL = PREV_TOTAL + NEW.AMOUNT
 END IF;
 IF ENTRY_TYPE = -1
 THEN
 SET NEW.LINE_TOTAL = PREV_TOTAL - NEW.AMOUNT
 END IF;
 IF ENTRY_TYPE = 0
 THEN
 SET NEW.LINE_TOTAL = PREV_TOTAL
 END IF;
END;
//

Or is NEW just an array of values (in which case I need to rewrite the joins to use WHERE NEW.x = ENTRY.x

Thanks in advance!

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: