As far as I remember it just makes his family and friends upset, and anybody who witnesses the death, which is usually always preferable to him going mental and sentencing your master metalworker to death because he refused to make -=* platinum trumpets*=-
He gets sentenced to prison, which is inconvenient, unless you don't have any space for him, then he gets sentenced to hammering (exactly what it sounds like), which is what kills or maims him, but it is possible to survive. Giving important dwarves (or just-sentenced ones) armour, removing Urist McHammerer's hammer or replacing it with a nonlethal one, or just walling him up somewhere are all viable strategies to keep important dwarves alive, but kill^H^H^H^H arranging an Unfortunate Accident[1] for troublesome nobles is arguably easiest.
I think this is one of the few areas of DF where players don't actually choose the most optimal path, but instead choose the most amusing, or dwarfiest, path.
I mean, you see countless arguments about stair shaft vs single floors, trap paths vs artillery paths, optimal pumps, delegation of jobs, etc, but everyone agrees that nobles should go in the lava.
