CODEWITHSUNDEEP
sql-servertriggers
Randy Minder
Randy Minder

Reputation: 48482

Instead Of Triggers on Views - What's the Use Case?

Our production database is 5-7 years old, and many people have had their hands in it making schema modifications during this time. I just discovered there are Instead Of triggers on some of our views. I didn't even know this was possible. For those of you who have used Instead of triggers on views, what use case were you trying to solve? I'm having a hard time seeing the benefit of this, given the maintainability of doing such a thing.

Thanks very much.

Upvotes: 0

Views: 405

Answers (3)

Matt Whitfield
Matt Whitfield

Reputation: 6574

I have used them to provide a schema which is compatible with a table that an existing application expects to find, but actually perform operations on a completely different schema. This was integrating a bug tracking database system into a database which had an existing user membership system. So the users table that the bug tracking system expected to find was faked up as a view, and INSTEAD OF triggers were implemented to make changes to the membership system that already existed.

Upvotes: 1

HLGEM
HLGEM

Reputation: 96600

We could probably guess the use case for you if you shared the code. Besides what Chris Shaffer said, these could be to enforce specific data integrity requirements (although truthfully I would expect these on the table not the view) or to add information to fields the user would not normally be aware of.

Upvotes: 0

Chris Shaffer
Chris Shaffer

Reputation: 32575

I haven't used them, but the I think the primary use is allowing INSERT/UPDATE statements to be performed on the view, and having those statements actually INSERT/UPDATE potentially multiple tables underneath the view.

Upvotes: 5

Related Questions