ch09 - P L S Q L Oracle10g Developer PL/SQL Programming...

Info iconThis preview shows pages 1–10. Sign up to view the full content.

View Full Document Right Arrow Icon
P L / S Q L Oracle10 g Developer: PL/SQL Programming Chapter 9 Database Triggers
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
P L / S Q L Oracle10g Developer: PL/SQL Programming 2 Chapter Objectives After completing this lesson, you should be able to understand: Database triggers and syntax How to create and test a DML trigger in SQL*Plus How to create and test an Instead Of database trigger Using system triggers
Background image of page 2
P L / S Q L Oracle10g Developer: PL/SQL Programming 3 Chapter Objectives (continued) After completing this lesson, you should be able to understand (continued): Identifying when triggers should be used Identifying trigger restrictions Using the ALTER TRIGGER statement Deleting a trigger Using data dictionary information relevant to triggers
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
P L / S Q L Oracle10g Developer: PL/SQL Programming 4 Database Trigger Defined Triggers are similar to procedures and functions but will execute automatically based on an event Events are either DML statements or database system actions Triggers will fire regardless of the source of the event DML triggers are specifically associated with a table or view
Background image of page 4
P L / S Q L Create database Load and run “c9Dbcreate.sql” Oracle10g Developer: PL/SQL Programming 5
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
P L / S Q L Oracle10g Developer: PL/SQL Programming 6 Create DML Trigger Syntax
Background image of page 6
P L / S Q L Oracle10g Developer: PL/SQL Programming 7 Example Trigger 1 CREATE OR REPLACE TRIGGER product_inventory_trg 2 AFTER UPDATE OF orderplaced ON bb_basket 3 FOR EACH ROW 4 WHEN (OLD.orderplaced <> 1 AND NEW.orderplaced = 1) 5 DECLARE 6 CURSOR basketitem_cur IS 7 SELECT idproduct, quantity, option1 8 FROM bb_basketitem 9 WHERE idbasket = :NEW.idbasket; 10 lv_chg_num NUMBER(3,1); 11 BEGIN 12 FOR basketitem_rec IN basketitem_cur LOOP 13 IF basketitem_rec.option1 = 1 THEN 14 lv_chg_num := (.5 * basketitem_rec.quantity); 15 ELSE 16 lv_chg_num := basketitem_rec.quantity; 17 END IF; 18 UPDATE bb_product 19 SET stock = stock – lv_chg_num 20 WHERE idproduct = basketitem_rec.idproduct; 21 END LOOP; 22 END;
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
P L / S Q L Oracle10g Developer: PL/SQL Programming 8 Trigger Timing AFTER or BEFORE event What about multiple triggers? ROW level or STATEMENT level Row level indicates firing the trigger code for each row affected in the DML statements Statement level indicates firing the trigger only once for the event regardless of the number of rows affected by the DML statements
Background image of page 8
L / S Q L Trigger Timing Note that the row level option is applicable only for UPDATE and DELETE events. Why? The default timing of a trigger is statement level and no code is included to achieve this timing WHEN clause provides conditional processing OLD and NEW are correlation identifiers, or qualifiers, which are special PL/SQL bind variables that refer to vales associated with the DML action that fires a trigger. Both the OLD and NEW identifiers are not
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 10
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 38

ch09 - P L S Q L Oracle10g Developer PL/SQL Programming...

This preview shows document pages 1 - 10. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online