question

Anette avatar image
Anette asked

Should I implement a view or a stored procedure?

Hello,

I have a denormalized database, where there are done frequent changes from different users and I don't want users to change tables directly. What do you suggest to implement for better? I am considering to use view or stored procedure, or maybe table-valued function. What do you suggest?

viewstored proceduresdenormalize
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

A function can not be updateable. A view can be updateable, but not if it's a complex view (eg containing joins between different tables). You can create an insteadof-trigger on a view, but if I were you, I'd implement stored procedures to make changes.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

Stored procedures are better for this type of thing. They can do some seriously complex things, especially when in comes to inserting or updating a "record", which may actually live across multiple tables.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.