Triggers and Views in Oracle

Triggers and Views in Oracle

1.What is the trigger?Write syntax for triggers?

A Trigger is a PL/SQL block which is executed automatically basing on an event.
Triggering events are inserted, update, delete.
Trigger timing can be before, after, instead of
Create or replace trigger <TRIGGER_NAME><TIMMING><EVENT> ON <OBJECT_NAME>

2.Different types of triggers.

Triggers are divided into two types
1. Statement level trigger
2. Row level trigger

*Statement level trigger:
These triggers are executed only once irrespective of number of rows affected by the event. By default every trigger is a statement level.

*Row level trigger:
These triggers are executed for every row which is affected by the event (multiple numbers of times). We can create a row level trigger by using “FOR EACH ROW” clause.

3.Why we are using triggers?

Triggers are used to enforce business rules.
We can enforce business rules by using :OLD and :NEW qualifiers.

*Query to see list of all the triggers:
Select object_Name from user_objects where object_TYPE = ‘Trigger’;

4.Abstract datatypes

Abstract datatypes:
Abstract data types are consists of one or more subtypes. Rather than being constrained to the standard oracle data types of number, date and varchar2 data types can more accurately describe your data.

SQL>create type address_ty5 as object
                                     (street varchar(20),
                                        city varchar2(10),
                                             state char(10),
                                               pin number);

5.Nested table:

 Nested table is a collection of rows, represented as a column with in the main table.
For each record with in the main table, the nested table may contain multiple rows. In one sense, it’s a way of storing a one-to-many relationship with in one table.

SQL> create or replace type emp_ty5 as object
        (desg varchar2(20),
         dname varchar2(20),
         doj date);
6.What is view in SQL Server?

View is one of the database objects and act as virtual table. View contains SQL statements with specifics and stores in SQL Server.

7.What is the syntax to create view in SQL Server?

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH [ ,...n ] ]
AS select_statement [ ; ]

8.What are the advantages of views?

Views enable the developers to write common queries once as view use them in application.
User could able to access subset of data contained base tables (restricted tables) by using view.
Performance would be increased to complex queries by creating indexes on views.

9.What are the restrictions to view select statement?

Developer should not use INTO keyword
We should not Reference to a temporary table or table variable
We should not use COMPUTE or COMPUTE BY clause

10.What is the difference between a regular view and an indexed view?

A regular view is a SELECT statement that is referenced by a name and stored in SQL Server. It does not contain any data.

An indexed view is a view that has a clustered index created against it, which causes SQL Server to store the results of the query defined in the view on disk. An indexed view must meet very stringent requirements for the view, the base tables that the view references, and the index on the view.

11.What is Database Trigger ?

A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in, update to, or delete from a table.

12. uses of Database Trigger ?

Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.

13.Difference between database triggers and form triggers?

-Data base trigger(DBT) fires when a DML operation is performed on a data base table. Form trigger(FT) Fires when user presses a key or navigates between fields on the screen
-Can be row level or statement level No distinction between row level and statement level.
-Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms.
-Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger.
-Can cause other database triggers to fire. Can cause other database triggers to fire, but not other form triggers.


Post a Comment