search
Login | Signup | Support
  • 0
  • ×

    Add as FriendTriggers

    by: Naalain

    Current Rating : Rate It :

    616

    Views

    Download
     
    1 : DATABASE Presentation Topic: Triggers
    2 : PRESENTERS Usman Jutt (61) Rana Arslan (58) Sohail Shouket (40) Fazeela Hussain (52)
    3 : What is Trigger? A database trigger is a stored PL/SQL program unit associated with a specific database table. ORACLE executes (fires) a database trigger automatically when a given SQL operation (like INSERT, UPDATE or DELETE) affects the table. When an event occurs, a database trigger is fired, and an predefined PL/SQL block will perform the necessary action.
    4 : Trigger Trigger: procedure that starts automatically if specified changes occur to the DBMS Three parts: Event (activates the trigger) Condition (tests whether the triggers should run) Action (what happens if the trigger runs)
    5 : trigger Events could be : BEFORE|AFTER INSERT|UPDATE|DELETE ON e.g.: BEFORE INSERT ON Professor Condition is SQL expression or even an SQL query (query with non-empty result means TRUE) Action can be many different choices : SQL statements ,
    6 : Syntax for creating triggers in SQL Trigger name - unique within one database schema Timing - depends on the order of controlled events (before or after or instead of) Triggering event - event which fires the trigger. Filtering condition - checked when the triggering event occurs. Target - table (or view) against which the trigger is fired; they should be both created within the same schema Trigger Parameters - parameters used to denote the record columns; preceded by colon :new, :old for new and old versions of the values respectively Trigger action - SQL statements, executed when the trigger fires; surrounded by Begin ... End (A)
    7 : Syntax for Creating Statement Triggers CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name BEGIN SQL statements; END; The trigger body consisting of SQL statements will be executed only once according to the prescribed timing, when the event1 (event2, event3) occurs against the monitored table in question table_name.
    8 : (Scenario 1) We have a employee table whenever a employee salary increases or decreases then we record in table employee_log what is employee current salary and old salary employee_log (emp_id , old_salary , new_salary) CREATE OR REPLACE TRIGGER EMP_SAL_LOG  AFTER INSERT ON EMPLOYEES FOR EACH ROW DECLARE BEGIN INSERT INTO employee_log VALUES (:NEW.EMPLOYEE_ID , :OLD.SALARY , :NEW.SALARY) ; END;
    9 : (scenario 2) We want to delete all employees from employee table whose row number is greater than sixty CREATE OR REPLACE TRIGGER CHECK_EMP  BEFORE INSERT ON EMPLOYEES FOR EACH ROW DECLARE  NUMBER_OF_EMP NUMBER ; BEGIN    SELECT COUNT(*) INTO NUMBER_OF_EMP  FROM EMPLOYEES ; IF NUMBER_OF_EMP > 60 THEN DELETE FROM EMPLOYEES WHERE ROWNUM > 60 ;  END IF ; END;
    10 : Types of trigger There are four main types 1.Row level trigger This gets executed before are after any column value for row changes 2.Column level trigger This gets executed before are after the specified column changes. 3.For each row type This trigger gets executed once for each row of the result set affected by by an insert/update/delete.
    11 : 4.For each statement type This trigger gets executed only once for entire result set but fires each time the statement is executed.
    12 : Example of trigger using sql Developer and Form Builder
    13 : First of all you have to apply this command on worksheet of database user select 'create sequence sq_'||table_name ||';' from user_tables; After this apply select sq_CUSTOMER.nextval from dual;
    14 : After clicking on the trigger select pre-insert
    15 : Pl/sql code Begin select sq_CUSTOMER.nextval Into :customer.customer_id; From dual; End;
    16 : PL/sql editor code
    17 : WE ARE GOING TO APPLY TRIGGER ON CUSTOMER_ID WHICH IS A PRIMARY KEY
    18 : Now as you insert data in form and save it then you can see in detail table it automatically stored in new row of detail table

    Presentation Tags

    Copyright © 2019 www.slideworld.com. All rights reserved.