When we talk about database, the things that come to our mind are the underlying tables, query, relations etc. While referring through some of the oracle documents, there came across some interesting topics that would really help you.
Concept of Database View
A ‘view’ can be considered as a logical table based on a table or another view. View is actually a select statement. The data which is retrieved by select statement is represented as a table. We can read or query data from a view just as in a table. The table used to make the view is called the base table of the view. There can be more than one base table for a view.
The advantages in using view are
1) to restrict data access – the data in the view cannot be changed. Then changes in the base tables would reflect in view and also some of the column data
2) to make complex queries easier
3) to provide data independence
4) to present different views of same data
A view can be created using the following syntax
CREATE OR REPLACE VIEW AS
‘DML STATEMENT’;
View can be classified into simple and complex view according to the complexity of the select statement. If the select statement consists of more than one table then the view is called a complex view and also a complex view can contain functions, grouping etc. A view can be also used to create another view.
Database Triggers
From the name itself we get a fair idea about a trigger. Trigger implies something or some event that initiates another event. Database triggers in oracle is a pl-sql coded set of instructions which are executed while an insert, update or delete is done on a table.
The main purpose of database triggers is to keep track of all the insertions, updations and deletions. We can refer the old values and also the new values in the trigger. We can also insert into another table with the new information that has been inserted into the table to which we had written the trigger for. By the use of database triggers we can backup data and also book keeps the transactions done of the particular table. Triggers can be executed before or after any insertion or updation or deletion that may occur in the table. The latest versions of oracle supports ‘Schema Level Triggers” also (Fire meanwhile the user log on or log off and when tables are modified).
The syntax of trigger for oracle is
CREATE OR REPLACE TRIGGER trigger_name
BEFORE|AFTER|INSTEAD OF
INSERT|DELETE|UPDATE ON table_name
[OF column_names]
[REFERENCING [NEW AS new_cols] [OLD AS old_cols]]
[FOR EACH ROW [WHEN (where_condition)]]
[DECLARE]
BEGIN
EXCEPTION
END;
The information is pretty useful to start of with ‘a view or a trigger’. You can explore the vast area of the view and trigger which indeed is very much useful for a database developer.