I assume you are referring to automated unit tests rather than tests performed by hand. I approach automated unit tests as a means to an end rather than an end in themselves. Some developers get a lot of value out of automated unit tests (I did when I was a developer). Others write great, high-quality code without resorting to automation. You know your own work well enough to know where you are likely to introduce bugs. If you think you are likely to write buggy stored procedures, functions, or triggers, by all means write some unit tests. Otherwise I might test at the persistence layer instead. (Or not at all -- again, you can judge for yourself.)
Another reason to unit test at the persistence layer would be if you expect the database to be queried manually, i.e. by typing in some ad-hoc SQL.