The Red Wedding is, without question, a game-changing moment in the series. It ends the North's war for independence and effectively ends the War of Five Kings as a total Lannister victory. However, the Freys breaking of the guest right (protecting guests who come into your home) is among the most grievous crimes that can be committed in Westeros. It completely destroys their reputation, with opinions about the Freys ranging from antipathy at best to a murderous lust for vengeance at worst. Even those who benefited from the Red Wedding are disgusted by what they did. Also, Robb and his mother were not the only people to die: almost every noble house in the North loses somebody at the Red Wedding. But many lords did not attend the wedding, and those who lost close relatives are extremely unlikely to forget. As Wyman Manderly, the lord of White Harbor, puts it, "The North Remembers"
There are a number of characters who are deeply affected by this as well. Arya Stark was already a very grim person who had a personal hit-list of people she intended to kill before this. Jon Snow and Theon Greyjoy may not be able to do much from their current circumstances, but neither take the news at all well. Petyr Baelish (Littlefinger) loved Catelyn for most of his life, and, as I am sure you know, this is a man who is more than capable of setting up plots that can bring death and ruin to others. Lastly, the Brotherhood Without Banners may have some rough edges, but it tries to fight for the common people and justice, and the Red Wedding is a serious enough crime for them to want to do something in response to it.
The Red Wedding seems like the end for the Starks. It may be the end of their independence and rule of the North, but they are not destroyed and it is a house still greatly revered in the North. The Freys, Boltons, and Lannisters see this as a great victory, and the short-sighted members of those houses will be quite pleased with themselves in its aftermath. But the sin of the Red Wedding is a deadly poison. The Freys gain land and titles, but they are now the most hated house in all of Westeros by both commoners and noble houses to the point that nobody will object if Freys themselves start being murdered. The Boltons gain the North, but they rule over houses that only follow them out of fear and would relish the chance to seek their own revenge. The Lannisters are the most powerful house in Westeros, but they have to walk an extremely fine line now because any association they are perceived as having with the Freys will make them just as big a target for murderous vigilantes. And their power is ultimately dependent upon one man: Tywin Lannister. None of his children, not even Tyrion, would be able to fill his shoes should Tywin himself die or fall from power. Even if he should live, the Lannister's wealth and large armies have been severely depleted by the war.
And there are still men and women out there who, although not directly affected by the Red Wedding, could end up proving to be the undoing of the Lannisters, Boltons, and Freys. Stannis Baratheon may have lost most of his army and navy at the Battle of Blackwater, but he is not out of the game yet and he is just the sort of man who could make common cause with those who want Frey blood and an end to Joffrey's reign. The Tyrells have taken next to no damage from the war, and have plenty of ambitions of their own. The Greyjoys still occupy much of the North, have suffered very few losses, and have a deadly fleet of warships. The Arryns of the Vale, even if they are led by a batshit crazy lady (who just lost her sister) and her infant-like son, are completely untouched by the war - and Littlefinger is heading there. The Martells of Dorne are similarly untouched by the war, but they have an impressive army, strong ties to House Targaryen, and a deep hatred of the Lannisters ever since Princess Elia was murdered during Robert's Rebellion. Finally, as House Stark's star is falling, House Targaryen's under Daenerys is rising in the east, and this Queen who is fighting to liberate slaves probably would not need much convincing to also bring justice to the perpetrators of the Red Wedding.
