lecture9

lecture9 - Triggers 1 PL/SQL reminder Wepre nte PL/S a...

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

View Full Document Right Arrow Icon
1 Triggers Triggers
Background image of page 1

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

View Full DocumentRight Arrow Icon
2 PL/SQL reminder PL/SQL reminder We presented PL/SQL- a Procedural extension to the SQL language. We reviewed the structure of an anonymous PL/SQL block: DECLARE           (optional)        /* Variable declaration */ BEGIN               (mandatory)        /* Block action*/ EXCEPTION      (optional)        /* Exception handling */ END;                  (mandatory) /
Background image of page 2
3 Example from last week Example from last week DECLARE e_number1 EXCEPTION; cnt NUMBER; BEGIN select count(*) into cnt from number_table; IF cnt = 1 THEN RAISE e_number1; ELSE dbms_output.put_line(cnt); END IF; EXCEPTION WHEN e_number1 THEN dbms_output.put_line('Count = 1'); end;
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 PL/SQL reminder-cont. PL/SQL reminder-cont. We also showed the structures of procedures and functions, as named PL/SQL blocks which can be called: create or replace procedure num_logged (person IN mylog.who%TYPE, num OUT mylog.logon_num%TYPE) IS BEGIN select logon_num into num from mylog where who = person; END; /
Background image of page 4
5 Triggers- introduction Triggers- introduction A trigger is an action which the Database should perform when some DB event has occurred. For example (in pseudocode): TriggerA: For any row that is inserted into table Sailors: if age>30 -> insert this row into oldSailors; else-> insert this row into youngSailors;
Background image of page 5

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

View Full DocumentRight Arrow Icon
6 Trigger introduction cont. Trigger introduction cont. The code within the trigger, called the trigger body, is made up of PL/SQL blocks The “firing” of a trigger is transparent to the user. There are many optional triggering events, but we will focus on update, delete, and insert. Triggers can be used to check for data integrity, but should be used so only if it is not possible through other means.
Background image of page 6
7 Types of triggers Types of triggers 1. Row level triggers: The code in the trigger is executed once for every row updated. 2. Statement level triggers (Default): The code in the trigger is performed once per statement. For example: if the triggering event was an update which updates 100 rows, a row-level trigger will execute 100 times, and a statement level trigger will execute once.
Background image of page 7

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

View Full DocumentRight Arrow Icon
Types of triggers-cont. Types of triggers-cont. 1.BEFORE triggers: The trigger fires immediately BEFORE the triggering event executes. 2.AFTER triggers: The trigger fires immediately AFTER the triggering event executes. 3.INSTEAD OF triggers: The trigger fires INSTEAD of the triggering event. We can reference the “old” and “new” values. If we want to change rows which will be inserted, we have to use a BEFORE trigger and change the ‘new’ values. Using an AFTER trigger will not allow the change.
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.
  • Summer '10
  • MR.RAZA
  • Database trigger, Trigger, Block Nested Loop, Trigger introduction cont, Trigger Compilation Errors, drop trigger myTrig

Page1 / 37

lecture9 - Triggers 1 PL/SQL reminder Wepre nte PL/S a...

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

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