This is not USB-only, and causes an annoying ruckus on initial startup, but it works.
This will play attach and detach sounds whenever any device is attached or detached, including USB devices, network interfaces (tun) PCMCIA devices, etc.. Basically anything that generates an 'attach' or 'detach' event will play the appropriate sound.
/etc/devd.conf:
notify 0 { match "system" "ACPI"; match "subsystem" "Lid"; match "notify" "0x00"; action "/usr/local/etc/sounds/hw.sh remove"; }; notify 0 { match "system" "ACPI"; match "subsystem" "Lid"; match "notify" "0x01"; action "/usr/local/etc/sounds/hw.sh add"; }; attach 0 { action "/usr/local/etc/sounds/hw.sh add"; }; detach 0 { action "/usr/local/etc/sounds/hw.sh remove"; };
/usr/local/etc/sounds/hw.sh:
#!/bin/sh play="/usr/local/bin/waveplay"; add="/usr/local/etc/sounds/hw_insert.wav"; rem="/usr/local/etc/sounds/hw_remove.wav"; logger -t "hw.sh" "$(whoami) Honoring ${1}" case "${1}" in add) ${play} ${add} > /dev/null 2>&1 & ;; remove) ${play} ${rem} > /dev/null 2>&1 & ;; esac