Simple way to stop accidental update and delete in the Database

Today we are going to show you that how you can minimize the accidental update and delete on non-intentional data by using view.

Modhera Sun temple in Gujarat

I have seen in multiple projects where in Production Support Engineers execute multiple patches daily for the long pending issues. Most of the time these patches are very critical and very complex one if anything goes wrong in the patch it can stop the whole application in Production. This particular scenario we can avoid by using view, But let’s first understand what is view.

View in SQL:-

• Views are logical tables which are based on one or more tables, views, functions or synonyms.
• View does not store the data itself.
• The tables which are used in the view are called base table.
• View retrieves the records at the time of execution and displays it in the result set.

Syntax:-

CREATE VIEW <View_Name>

AS

<SQL Query>

Example:-

create  view V_Emp 
as 
select * from Employee s

In the above query you can see that we can have any SQL query in the view.

Actually we can do DML operations like Insert, Update and Delete on the view and it will basically update the base table directly.

Let’s see how we can update the above V_Emp view.

Update statement for the view will be same as we write for the table.

Example:-

update V_Emp 
set "gender"='Female';

All the records got updated with Female as their gender

As you can see in the above output all the records got updated with Female as their gender. Now let’s see another example with complex query.

update V_Emp 
set "gender"='Male',
where "gender" ='Female';

In the above example if you see we can put where statement as well to update the view but here we can make mistake while forming the query executing the query we may forget where clause and then all the records will get updated. Instead of that we can utilize below example to avoid these incidents.

create  view V_Female_Emp 
as 
select * from public."Employee" s
where s."gender"='Female'
and s."Name" ='Aarti';

If you see in the above example we have created a view where we put conditions based on that all the female employees list whose name is ‘Aarti’ will come in the result. Let’s update the above view.

Data before update operation

update V_Female_Emp 
set "gender"='Male',
s."Name" ='Ankush';

Name and gender got changed after updating the view

As you can see in the above example we have updated the view and it updated only a row which satisfies the condition given inside the view.

This way we are minimizing task of putting condition manually or changing the condition and because of that indirectly we are minimizing the risk of manipulating correct data through the patch.

The idea is, we can have multiple views based on the issues and can be executed without putting complex queries.

Note:- There are some restrictions of executing DML statement on view.

We can only update/Insert/Delete records of one table at a time if any type of join is used.

We cannot modify the data if group by or Aggregate functions are used in select statement of the view.

You can only manipulate columns which are given in the select statement of the view.

Thank you for reading please comment your suggestions, share the article, follow me and Abhima Database Technology publication.

Bhagavad Gita: Chapter 14, Verse 19

नान्यं गुणेभ्य: कर्तारं यदा द्रष्टानुपश्यति |
गुणेभ्यश्च परं वेत्ति मद्भावं सोऽधिगच्छति || 19||

Translation

When wise persons see that in all work there is no agent of action other than the three guṇas, and they know Me to be transcendental to these guṇas, they attain My divine nature.

जब बुद्धिमान व्यक्ति को यह ज्ञात हो जाता है कि सभी कार्यों में प्रकृति के तीनों गुणों के अलावा कोई कर्ता नहीं है और जो मुझे इन तीन गुणों से परे देखते हैं, वे मेरी दिव्य प्रकृति को प्राप्त करते हैं।