Agree for 100% in terms of stored procedure - that is ultimately "last line of defense" 🙂

You would not scare me with Access linked tables to SQL Server -apparently this is curren "state of the art" of legacy systems created as mushrooms after rain since mid 90s. Been there, done that (no free T-Shirt unfortunately).

I usually use Views to retrieve data in SP (the R part of CRUD), and I would use them for reporting and data export during integration. I normally even enforce TOP 100 Percent to enforce sorting in views.

I usually stay shy from using ANY triggers - IMHO unless you implementing certain "JOB CREATION SCHEME". My usage of triggers was limited to populating historical tables when you need a "before and after" snapshots.

Create, Update and Delete SPs would work against table and I prefer this way because I could operate

with a set of records (bulk insert, update and delete via TVP) rather than struggle with RBAR anti-pattern

introduced by INSTEAD OF triggers.