help with mysql triggers (checking values before insert)

Question

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 sub_max
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.

Answer 1

Caveat - I am also learning triggers.

For the section:

if max>maxmax 
rollback? 

Would the syntax be something like?:

IF max > maxmax THEN
    DELETE the id of the new record?
ELSE
    do nothing?
END IF;