Showing posts with label Trigger. Show all posts
Showing posts with label Trigger. Show all posts

Wednesday, 15 October 2014

Strengths and weaknesses of trigger mechanisms


Strengths and Weaknesses of Triggers / Advantages and Disadvantages of using Triggers / Merits and Demerits of Trigger mechanisms

 

Strengths and Weaknesses of Triggers / Trigger mechanisms


Strengths


  • Used for enforcing referential integrity.
  • Easy to implement business rules through triggers.
  • Event logging is easier.
  • It can control transactions hence lead to safer transactions.
  • Consistency is maintained.
  • Efficiency in performing transactions.
  • Cascading referential integrity.
  • Implicitly called for actions during database changes. Very much helpful in a case where many changes need to be done on different tables for a single action.

Weaknesses

  • Triggers should be written with extra care. Trigger error may lead to failure of insert, delete, or update operations at runtime.
  • Excessive use of triggers can result in complex interdependencies. For example, cascading triggers can induce chain of actions.
  • Increased complexity.
  • May decrease the performance of the database.
  • Every time a trigger is fired, its code must be recompiled.
  • Database triggers can be accidentally disabled or dropped by a person with sufficient privilege.
  • The hidden nature of triggers is another important weakness.



Sunday, 28 September 2014

Database Trigger - Overview

What is Database Trigger? / Why do we need database trigger? / Overview of Database trigger / Database Trigger Example


Triggers

What is Database Trigger?

Database Trigger is a statement or set of statements which would be executed due to the modification in a database. The modification would mean Insertion, Deletion, or Updating of records.

Purpose of Triggers

To maintain database integrity.
To safeguard a database from inconsistency, especially in a large database.
To prevent invalid transactions.

What do we need to have a Trigger?

Event-Condition-Action Model

We need to,
·         Specify “When you need your trigger gets executed?”
·         Specify “What actions to be taken while executing Triggers?” In other words, “you need to specify the actions that need to be caused by the execution of Trigger”.
The above said requirements for having a trigger is called Event-Condition-Action model.
Event – The event which causes the Trigger to be executed
Condition – The condition which need to be satisfied by the event to trigger an action
Action – The actual modification to be done on database due to the Event and Condition.

Type of Triggers
Row-level Trigger – it is fired each time a row (record) is affected. For example, if a DELETE statement deletes 10 rows in a table row-level is fired once for every row. If the DELETE statement affects no rows, then no trigger action executed.
Statement-level Trigger – it is fired once for a statement, regardless of the number of statements get affected. Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, if a DELETE statement deletes 10 rows in a table, statement-level trigger executes the action only once.
Before Trigger – it executes the trigger action before the triggering statement.
After Trigger – it executes the trigger action statements after the triggering statement.

Syntax:
CREATE [OR REPLACE ] TRIGGER trigger_name
 {BEFORE | AFTER | INSTEAD OF }
 {INSERT [OR] | UPDATE [OR] | DELETE}
 [OF col_name]
 ON table_name
 [REFERENCING OLD AS o NEW AS n]
 [FOR EACH ROW]
 WHEN (condition) 
 PL/SQL block

Example
CREATE TRIGGER reorder
    AFTER UPDATE OF balance ON Account /* Triggering Statement / EVENT. WHEN to execute */
    WHEN (NEW.balance <1000) /* Trigger CONDITION */
    FOR EACH ROW
BEGIN
    INSERT INTO Loan VALUES(loan_no, 1000 - NEW.balance, OLD.bname); /* Trigger ACTION */
END;

More on this later ...


Lossless join decomposition one more example

Lossless Join Decomposition Question: Let R = {ssn, ename, pnumber, pname, plocation, hours} and R is decomposed into three re...