Both my parents are pipers and do wear dirks on occasion, though not often. Appropriateness is gauged by the event though I think - wearing a dirk when piping a golf tournament would be a little much, but a Burns night or wedding would be ok.

With regards to weapons in a House of Worship - it's actually quite historical for Scots to wear weapons in church. There is a semi-famous quote describing the Scots as always going about armed, even in church - though I'm afraid I can't recall the source at the moment.