What is the difference between triggers and views

Trigger and procedure are two such operations. What is Trigger — Definition, Functionality 2. What is Procedure — Definition, Functionality 3. Oracle engine invokes triggers when certain events occur. A trigger will be invoked when a specific condition is met. There can be triggers on tables, views, schemas or databases, etc. In other words, when executing Data Manipulation Language statements.

In other words, when executing Data Definition Language Statements. Overall, a trigger provides several advantages. Stored procedure: Stored Procedures can't be called from a function because functions can be called from a select statement and Stored Procedures can't be called from. But you can call Store Procedure from Trigger. Store procedure: Stored Procedures can accept any type of parameter.

Stored Procedures also accept out parameter. When the trigger action determines whether the triggering statement should be allowed to complete. Using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.

Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is run, if the trigger restriction was not violated.

AFTER statement trigger. After executing the triggering statement and applying any deferred integrity constraints, the trigger action is run. AFTER row trigger. After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is run for the current row provided the trigger restriction was not violated.

You can have multiple triggers of the same type for the same statement for any given table. Multiple triggers of the same type permit modular installation of applications that have triggers on the same tables. Deleting a row in a view could either mean deleting it from the base table or updating some values so that it is no longer selected by the view. Inserting a row in a view could either mean inserting a new row into the base table or updating an existing row so that it is projected by the view.

Updating a column in a view that involves joins might change the semantics of other columns that are not projected by the view. Object views present additional problems. This operation inevitably involves joins, but modifying joins is inherently ambiguous. As a result of these ambiguities, there are many restrictions on which views are modifiable.

A view is inherently modifiable if data can be inserted, updated, or deleted without using INSTEAD OF triggers and if it conforms to the restrictions listed as follows. Even if the view is inherently modifiable, you might want to perform validations on the values being inserted, updated or deleted.

Here the trigger code performs the validation on the rows being modified and if valid, propagate the changes to the underlying tables. To modify an object materialized by an object view in the client-side object cache and flush it back to the persistent store, you must specify INSTEAD OF triggers, unless the object view is inherently modifiable.

However, it is not necessary to define these triggers for just pinning and reading the view object in the object cache. Oracle Call Interface Programmer's Guide. If the view query contains any of the following constructs, the view is not inherently modifiable and you therefore cannot perform inserts, updates, or deletes on the view:.

If a view contains pseudocolumns or expressions, you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions. The triggers on the nested tables fire if a nested table element is updated, inserted, or deleted and handle the actual modifications to the underlying tables. You can use triggers to publish information about database events to subscribers.

Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include:. Triggers on system events can be defined at the database level or schema level. For example, a database shutdown trigger is defined at the database level:. Triggers on DML statements can be defined on a table or view. A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only when the triggering event involves that schema or table.

Event publication uses the publish-subscribe mechanism of Oracle Streams Advanced Queuing. A queue serves as a message repository for subjects of interest to various subscribers. Each event allows the use of attributes within the trigger text. For example, the database startup and shutdown triggers have attributes for the instance number and the database name, and the logon and logoff triggers have attributes for the user name.

You can specify a function with the same name as an attribute when you create a trigger if you want to publish that attribute when the event occurs. The attribute's value is then passed to the function or payload when the trigger fires. System events that can fire triggers are related to instance startup and shutdown and error messages.

Triggers created on startup and shutdown events have to be associated with the database. Triggers created on error events can be associated with the database or with a schema. Their attributes include the system event, instance number, and database name.

You can use these triggers to make subscribing applications shut down completely when the database shuts down. For abnormal instance shutdown, these triggers cannot be fired. Stored procedures are used for performing tasks. Stored procedures are normally used for performing user specified tasks.

They can have parameters and return multiple results sets. The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.

Trigger and Procedure both perform a specified task on their execution. The fundamental difference between Trigger and Procedure is that the Trigger executes automatically on occurrences of an event whereas, the Procedure is executed when it is explicitly invoked. Store Procedures are manually executed while triggers are implicitly executed whenever we change in database.

Difference between Stored Procedure and Trigger: 1 Stored Procedure is predefined collection of sql statements. Stored Procedure is not automatic Procedure process. Adding one more difference: Triggers will not take any argument while procedure and functions takes arguments.