triggers in sql

by

Triggers In SQL-:  Triggers are the part of sql. Active database is a database which provide facility for specifying for active rules.These rules can be automatically triggered by events that occur on database and initiate certain actions that have been specified in the rule declaration. Commercial database provide functionality of active database in the form of triggers.

A triggers is a block that execute implicitly whenever a particular event takes place A trigger can be either a database triggers or an application triggers Database triggers execute simplicity when a INSERT, UPDATE, or DELETE statement is issued against the associated table. No matter which user is connected or which application is used.Application triggered execute implicitly when ever a particular event occurs with in application.

Components of Triggers -: There are four type components of triggers ..

  1. Triggers timing
  2. Triggers Events
  3. Triggers Type
  4. Triggers Body

Triggers Timing -:  In the triggers timing the possibility of when the triggers fires in relation to the triggering event. There are tow type values in this means the condition of triggers fire ..

  1. Before-: Execute the trigger body before the triggering  DML event on a table. this type triggers has some following situation ..
  •  When a trigger action should determine whenever the triggering statement should be allowed to complete.
  • To derive column values before completing a triggering Insert or Update statement.
  • To initialise global variables or flags and   to validate complex business rules.

2. After -: This type triggers used the following situations

  • When we want that triggering statement to complete before executing the trigger actions
  • If a before triggers is already present and an After trigger can perform different action on the same triggering statement.

Trigger Event-:  This give the answer of this question. What DML Statement will cause the trigger to execute ? 

  • INSERT
  • UPDATE
  • DELETE

When the triggering event is an UPDATE we can include a column list to identify which column must be changed to fire the trigger .You can not specify column name list for an INSERT Or Delete statement because they always affect entire now as like

              Update of salary:

The triggering event can contain multiple DML operation

Triggers Types in sql

We can specify that the triggers will be execute once for every row affected by the triggering statement or once for the triggering statement no matter how many row it affects. There are two type

  1.  Statement Triggering-: A statement trigger is fired once on behalf on the triggering event even if no row are affected at all statement triggers are useful if the trigger does not depend on data of rows that are affected or data provide y the trigger event itself  For example a trigger that data provide by the security check on the current user.
  2. Row trigger-: A row trigger fires each time the table is affected by the triggering event.If the triggering event affect no rows a row is not execute at all.

Trigger body -: The Trigger action defines what needs to be done when the triggering event is issued. The PL/SQL block can contain SQL  and PL/SQL statements.

Create Statement Triggers.  

Create [Or Replace] trigger trigger_name timing event [or event 2 or event 3] on table_name trigger body;

Create [Or Replace] trigger customer_emp before insert on emp begin if (to_char(sysdate,’DY’) in (‘sat’,’sun’)) or (to_char(sysdate,’hh24′) not between (’08’ and ’18’) then raise_application_error(-20500,’you may only insert into EMP during business hours.’);

End if; 

End;

 

How can create a row trigger –

Create [Or Replace] trigger trigger_name timing event [or event 2 or event 3] on table_name [referencing old as old/new as new ] for each row [when condition] trigger body;

Uses of triggers-:

  • Triggers are generally used to maintain database consistency.
  • Triggers can ne used to alert users to unusual events.
  • Triggers can generate a log of events to support auditing and security checks.
  • triggers may be used to gather statistical information on table access.

 

 

Leave a Comment