Triggers – Views Interview Questions

What is a trigger ? How many types of triggers are supported in Oracle ?

A trigger is a defined procedures that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

Different types of triggers are:

• Row Triggers and Statement Triggers
• BEFORE and AFTER Triggers
• INSTEAD OF Triggers
• Triggers on System Events and User Events

What is a Statement Trigger ?

A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected.

What is a Row Trigger ?

A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.

What are the different components of a PL/SQL trigger ?

Trigger Action, Trigger Restriction and Trigger Action are the different components of a PL/SQL Trigger.

Name some usages of database trigger ?

• Restrict DML operations against a table to those issued during regular business hours.
• Automatically generate derived column values.
• Prevent invalid transactions.
• Enforce complex security authorizations.
• Enforce referential integrity across nodes in a distributed database.
• Enforce complex business rules.
• Provide transparent event logging.
• Provide auditing.
• Maintain synchronous table replicates.
• Gather statistics on table access.
• Modify table data when DML statements are issued against views.
• Publish information about database events, user events, and SQL statements to subscribing applications.

How can you enable or disable a trigger ?

To Enable a Trigger : Alter Trigger Trigger_Name Enable;

To Disable a Trigger : Alter Trigger Trigger_Name Disable;

How to disable multiple triggers of a table at at a time ?

Use query : ALTER TABLE table_name DISABLE ALL TRIGGERS;

What are two virtual tables available during database trigger execution ?

The two virtual tables available during trigger execution are OLD and NEW. These have the same column names as that of base table on which trigger is written.

Triggers for INSERT, since we are inserting a new row, only NEW.column names have value (OLD values are null)

Triggers for UPDATE, since we are updating old values to new values, both OLD.column and NEW.column have values

Triggers for DELETE, since we are deleting the old record, only OLD.column names have value (NEW values are null)

Hence we assign the :old and :new columns as appropriate in a trigger.

Can we have a commit / rollback in trigger ?

Commits, rollbacks and save points are not allowed in the trigger body. But if you want it then you have to declare it as AUTONOMOUS_TRANSACTION.

What is mutating table error and how can you solve it ?

Mutating table error occurs when a trigger/function tried to modify or query a table that is currently being modified by the statement that fired the trigger/function. Options to avoid this error are:

• Don't use triggers
• Use an "after" or "instead of" trigger
• Use compound trigger
• Use autonomous transactions
• Using the temporary tables or by the views

What is a view ?

A view is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves. All operations performed on a view actually affect the base table of the view.

What is Advantages and Disadvantages of views ?
Advantages

Security
A user can be given permission to access the database only through a small set of views that contain the specific data the user is authorized to see, therefore  restricting the user's access to base table.

Query simplicity
A view can turn multi-table queries into single-table queries.

Structural simplicity
Views can give a user a personalized view of the database structure, presenting the database as a set of virtual tables that make sense for that user.

Consistency
A view can present a consistent, unchanged image of the structure of the database, even if the underlying source tables are split, restructured, or renamed.

Data Integrity
If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.


Disadvantages

Performance
If the view is defined by a complex & multi-table query then simple queries on the views may take considerable time since database translate queries against the view into queries against the underlying base tables.

Update restrictions
When a user tries to update rows of a view, the database must translate the request into an update on rows of the underlying source tables. This is possible for simple views, but more complex views are often restricted to read-only.

What is materialized view (snapshots) ?

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views whose contents are periodically refreshed based on a query, either against a local or remote tables.

Syntax for creating materialized views :

CREATE MATERIALIZED VIEW view-name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

[ON PREBUILT TABLE]

AS

SELECT ...;

The BUILD clause options are shown below.

• IMMEDIATE : Specify IMMEDIATE to indicate that the materialized view is to be populated immediately. This is the default.
• DEFERRED : Specify DEFERRED to indicate that the materialized view is to be populated by the next REFRESH operation. The first (deferred) refresh must always be a complete refresh. Until then, the materialized view has a staleness value of UNUSABLE, so it cannot be used for query rewrite.

The following refresh types are available.

• FAST : Specify FAST to indicate the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. If you specify REFRESH FAST, then the CREATE statement will fail unless materialized view logs already exist for the materialized view master tables.
• COMPLETE : Specify COMPLETE to indicate the complete refresh method, which is implemented by executing the defining query of the materialized view. If you request a complete refresh, then Oracle Database performs a complete refresh even if a fast refresh is possible.
• FORCE : Specify FORCE to indicate that when a refresh occurs, Oracle Database will perform a fast refresh if one is possible or a complete refresh if fast refresh is not possible. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.

A refresh can be triggered in one of two ways.

• ON COMMIT : Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.
• ON DEMAND : Specify ON DEMAND to indicate that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures. If you omit both ON COMMIT and ON DEMAND, ON DEMAND is the default.

The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.

The ON PREBUILT TABLE clause lets you register an existing table as a preinitialized materialized view. The table must have the same name and be in the same schema as the resulting materialized view.

Difference between a view and materialized view ?

• In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

• When we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different.

• In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

• Performance of View is less than Materialized view.

• In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table.

• In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.