SQL - Is it possible to have a Trigger statement with a WHERE clause that is an equality measure
In Oracle triggers, you can access the modified table using :NEW (for inserted and after an update data) and :OLD (for deleted and before the update). 

Also there is noUPDATE .. FROM syntax. Your trigger can be written:

CREATE TRIGGER team_no_inc 
    BEFORE INSERT ON Heroes
    FOR EACH ROW
BEGIN
    UPDATE Teams SET no_of_members = no_of_members + 1 
    WHERE Teams.team_id = :NEW.team_id;
END;


Tested at dbfiddle.uk.