CODEWITHSUNDEEP
sqlsql-serverplsqltriggers
Yara
Yara

Reputation: 25

Why we need trigger to delete from View?

Sometimes I found some code that makes triggers to allow user to delete from View ? But why we need to delete from View ,, because simply we can Go to Table > and execute delete like this simple code :

delete from table where x=y ;

My question why we use deletion on views by using TRIGGERS ؟ in another words what is the advantages of using delete over a View !

Upvotes: 2

Views: 170

Answers (1)

M.Ali
M.Ali

Reputation: 69554

The reason we use view are mainly to hide the complexity of data sources , Hide away the actual tables (For security reasons), saving us writing the same code over and over again.

Now if you havent let your users access the tables directly and they only work through View in this case they will be executing Deletes against views.

Triggers are only used when your View has more than One underlying table. You cannot do update, insert or delete operations on view if it effects multiple underlying tables. Therefore we make use of Instead of Delete/Update/Insert Triggers to do these operations against views.

When you have multiple Underlying tables if you Update,Delete or Insert effects only One underlying table it does allow you to execute the statement but it is not guaranteed that it will be done correctly.

therefore if you have a situation where your view is based on multiple underlying tables you should always do update/delete/inserts

1) Directly to underlying tables.
2) Use Instead of triggers.

Also Read here for more details on why you shouldnt do update/delete/insert operations on views with multiple underlying tables.

Upvotes: 2

Related Questions