help with mysql triggers (checking values before insert)
- by user332817
hi I'm quite new to mysql and I'm trying to figure out how to use triggers.
what I'm trying to do:
I have 2 tables, max and sub_max, when I insert a new row to sub_max I want to check if the SUM of the values with the same foreign_key as the new row are less than the value in the max table. I think this sounds confusing so here are my tables:
CREATE TABLE max(
number INT ,
MaxAmount integer NOT NULL)
CREATE TABLE sub_max(
sub_number INT ,
sub_MaxAmount integer NOT NULL,
number INT,
FOREIGN KEY ( number ) REFERENCES max( number ))
and here is my code for the trigger, I know the syntax is off but this is the best I could do from looking up tutorials.
CREATE TRIGGER maxallowed
after insert on submax
FOR EACH ROW
BEGIN
DECLARE submax integer;
DECLARE maxmax integer;
submax = select sum(sub_MaxAmount) from sub_max where sub_number = new.sub_number;
submax = submax + new. sub_MaxAmount;
maxmax = select MaxAmount from max where number = new.number ;
if max>maxmax
rollback?
END
I wanted to know if I'm doing this remotely correctly. Thanks in advance.
