Unanswered: Limit changes to a table through a stored procedure
I am designing a complicated table structure with a lot of triggers (to limit the types of changes that can be made) and stored procedures (to ensure that inserts/updates get done correctly). As I was writing up a trigger for one table, I realized that the trigger could not handle what I was attempting to do. In the stored procedure, I handle a bulk update on that table in a proprietary way, by first deleting all of the rows associated with a given record, then reinserting the rows (with new values). In the trigger, I am trying to restrict deletions from the table. The only time I want to allow deletions to occur is in the "update" stored procedure, as part of the delete/insert process.
Thus, my question: is there any way to restrict the changes that can be made to a table to changes made through a given stored procedure (or a set of stored procedures). The trigger would determine whether the changes were coming through the stored procedure. If so, the changes are allowed, else they are not.
IMO, if you are looking for 100% certainty, I contend that it can be achieved only with a very well conceived design & careful implementation. I suspect that I could usually work around what ever
safe guards you put into place, but that may be good enough for your