Triggers Triggers
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)
/
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;
PL/SQL reminder PL/SQL reminder- - cont. 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;
/
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;
/
Triggers
Triggers- - introduction 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;
Trigger introduction cont.
Trigger introduction cont.
The code within the trigger, called the trigger body, is made up of PL/SQL blocks
The f ir ing of a t r igger is t r anspar ent t o t he user.
There are many optional triggering events, but we will focus on update, delete, andinsert.
Triggers can be used to check for data integrity, but should be used so only if it is not possible through other means.
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.
Types of triggers Types of triggers- - cont. 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.
W e can r ef er ence t he old and new values.
If we want to change rows which will be inserted, we have t o use a BEFORE t r igger and change t he new values. Using an AFTER trigger will not allow the change.
After trigger is more efficient
Example (
Example (pseudocode pseudocode) )
Create Before-Trigger:
For every string inserted into sailorName, turn it into upper case before insertion
Trigger syntax Trigger syntax
CREATE [or REPLACE] TRIGGER trig_name {BEFORE | AFTER | INSTEAD OF}
{DELETE | INSERT | UPDATE}
[of column1, column2, ] [or {DELETE | INSERT | UPDATE]
[of columnA, columnB, ] on table_name
[FOR EACH ROW]
[WHEN (condition)]
PL/SQL block
Further restricts when trigger is
fired Triggering event Trigger timing
Backing Up Data Backing Up Data
create table sailors_backup(
who varchar2(30), when_changed date, sid number, old_rating number, new_rating number );
create table sailors(
sid number, sname VARCHAR2(30),
rating number check(rating <= 10), age number
);
Backing Up Data Backing Up Data
Q: Why AFTER Trigger?
A: Because in that case, the firing of the trigger occurs only when the inserted data complies with the table integrity (check..) CREATE or REPLACE TRIGGER backup_trig AFTER UPDATE of Rating on Sailors
FOR EACH ROW WHEN (old.rating < new.rating) BEGIN
INSERT INTO sailors_backup VALUES (USER, SYSDATE, :old.sid,
:old.rating, :new.rating);
END;
/
Ensuring Upper Case Ensuring Upper Case
CREATE or REPLACE TRIGGER sname_trig BEFORE INSERT or UPDATE of sname on Sailors
FOR EACH ROW BEGIN
:new.sname := UPPER(:new.sname);
END;
/
Why BEFORE Trigger?
Instead Of Trigger Instead Of Trigger
create view sailors_reserves as select sailors.sname, reserves.bid from sailors, reserves
where sailors.sid = reserves.sid;
CREATE or REPLACE TRIGGER view_trig INSTEAD OF INSERT
on sailors_reserves FOR EACH ROW BEGIN
INSERT INTO sailors values(:new.sname;
INSERT INTO reserves values(:new.bid);
END;
/
Statement Trigger Statement Trigger
CREATE or REPLACE TRIGGER no_work_on_shabbat_trig
BEFORE INSERT or DELETE or UPDATE on reserves
DECLARE
shabbat_exception EXCEPTION;
BEGIN
if (TO_CHAR (sysdate,'DY')='SAT') then raise shabbat_exception;
end if;
END;
/
What happens if exception is thrown?
Why BEFORE Trigger?
Another example Another example
create or replace trigger trig2 after update of rating on sailors for each row
DECLARE
diff number:=abs((:old.rating)-(:new.rating));
BEGIN
If ((:old.rating)>(:new.rating)) then dbms_output.put_line('The rating of '||:old.sname||' has dropped by '||diff);
elsif ((:old.rating)<(:new.rating)) then dbms_output.put_line('The rating of '||:old.sname||' has been raised by '||diff);
else dbms_output.put_line('The rating of '||:old.sname||' has remained the same');
end if;
END;
/
Trigger Compilation Errors Trigger Compilation Errors
As with procedures and functions, when creating a Trigger with errors, you will get the message:
W ar ning: Tr igger cr eat ed wit h compilat ion er r or s.
To view the errors, type:
SHOW ERRORS TRIGGER myTrigger To drop a trigger write
drop trigger myTrig
To disable/enable a trigger write alter trigger myTrig disable/enable
Additional Types of Triggers Additional Types of Triggers
Can also define triggers for logging in and off
create/drop table events system errors
etc.
Optimization Recap and examples Optimization Recap and examples
Optimization introduction Optimization introduction
For every SQL expression, there are many possible ways of implementation.
The different alternatives could result in huge run-time differences.
Our aim is to introduce the basic
hardware used, and optimization principles
Disk Disk- - Memory- Memory - CPU CPU
Delete from Sailors where
sid=90
DISK sailors Reserves
Main Memory
CPU
Hardware Recap Hardware Recap
The DB is kept on the Disk.
The Disk is divided into BLOCKS (1-4 Kbytes) Any processing of the information occurs in the Main Memory.
Therefore, a block which we want to access has to be brought from the Disk to the memory, and perhaps written back.
Blocks are read/written from/to the Disk as single units.
The time of reading/writing a block to/from the disk is an I/O operation, and takes a lot of time.
Hardware Recap Hardware Recap
We assume a constant time for each Disk access, and that only disk access affects define the run time.
Every table in the DB is stored as a File (on the Disk), which is a bunch of Blocks .
Every block contains many tuples, each of them has a Record ID (RID), which states its location:
(number of block, number of tuple within the block)
Indexes on files Indexes on files
Files can hold the tuples in a few ways, we will deal with a heap (no ordering), or ordered file.
An Index of Data Entries is an additional file which helps access the data fast.
The index can have the structure of a B+
Tree, or a hash function.
Suppose the table Students includes 105 tuples in 103 blocks
Select * from students where sname= moshe ; With no index- read all blocks and search for
moshe
DISK Students
Main Memory
23 6
Bill 1226
age rating
SNAME
SID
54 9
Vicky 1445
33 7
Boe 1332
41 9 Phill 3321
32 8
Joe
1923 (3,1)
(4,1) (3,4) (3,3) (3,2) RID Sailor table
Tree index on
Tree index on sname sname of sailors of sailors
A - > M B1 N - > Z B2
N - > T L3 U - > Z L4 A - > G L1
H - > M L2
Root block
Leaf blocks Branch blocks
Boe (3,3) Bill (3,4)
Vicky (4,1) Phill (3,2)
Joe (3,1) B1
L4 L2 L3
L1
B2
Tree index Tree index
The tree is kept balanced
The tree entries are always ordered
The leaves point to the exact location of tuples Getting to the leaf is typically 2-3 I/O Each leaf points to the next/previous leaf A Clustered index means that the index and the table are ordered by the same attribute
Hash index Hash index
Works in a similar way, but using a hash function instead of a tree
Works only for equality conditions Average of 1.2 I/O to get to the tuple location
Natural Join Natural Join
We want to computeWe have 4 optional algorithms:
1. Block Nested Loops Join 2. Index Nested Loops Join 3. Sort Merge Join 4. Hash Join
SELECT *
FROM Reserves R, Sailors S WHERE R.sid = S.sid
This is assuming there is not enough space in the memory for the smaller
of the 2 relations+2
Block Nested Loop Join Block Nested Loop Join
Suppose there are B available blocks in the memory, BRblocks of relation R, and BSblocks of relations S, and |R|<|S|.
Until all blocks of R have been read:
Read B-2 blocks of R
Read all blocks of S (one by one), and write the result Run time: BR+ BS* ceil(BR/(B-2))
Index Nested Loop Index Nested Loop
Suppose there is an index on sid of Sailor Until all blocks of R have been read:
Read a block of R
For each tuple in the block, use the index of S to locate the matching tuples in S.
We mark the time it takes to read the tuples in S that match a tuple in R as X.
Run time: BR+ tRX
If the index is hash-based and clustered, X=2.2 If the index is tree-based and clustered, X=3-4 If it is not clustered, we evaluate X.
Sort- Sort - Merge Join Merge Join
Sort both relations on the join column Join them according to the join algorithm:
Joe 10/2/96 101
31
Sam 12/7/96 102
31
Sam 13/7/96 101
31
Frank 2/6/96 103
58
Frank 11/3/96 103
28
Joe 12/4/96 103
28
agent day
bid sid
55 8
lubber 31
36 6
lubber 36
35 5
guppy 44
35 10 rusty 58
35 9
yuppy 28
45 7
dustin 22
age rating sname sid
Run time of Sort
Run time of Sort- - Merge Merge
M,N: number of blocks of the relations Sorting: O(MlogM)+O(NlogN)
Merging: O(N)+O(M) if no partition is scanned twice.
Total: O(MlogM)+O(NlogN)
Typically good if one or both of the relations is already sorted.
Question 1 Question 1
Suppose:
tuple size= 100 bytes
number of tuples (employees)=3,000 Page size=1000 bytes
You have an unclustered index on Hobby.
You know that 50 employees collect stamps.
Would you use the index?
And for 1,000 stamp-lovers?
SELECT E.dno FROM Employees E WHERE E.hobby= stamps
Question 2 Question 2
How could you calculate this?SELECT E.ename
FROM Employees E, Departments D WHERE D.dname= Toy AND
E.eid=D.eid
Question 3 Question 3
Length of tuples, Number of tuples Emp: 20 bytes, 20,000 tuples Dept: 40 bytes, 5000 tuples
Pages contain 4000 bytes; 12 buffer pages
Which algorithm would you use if there is no index? And if there is an unclustered index on E.eid? And if Emp is sorted by eid?
SELECT E.ename
FROM Employees E, Departments D WHERE E.eid=D.eid
This document was created with Win2PDF available at http://www.daneprairie.com.
The unregistered version of Win2PDF is for evaluation or non-commercial use only.
