Triggers

1
Triggers
2
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) /
3
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

4
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.whoTYPE, num OUT mylog.logon_numTYPE)
IS BEGIN select logon_num into num
from mylog where who person END /
5
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 agegt30 -gt insert this row into
  oldSailors
• else-gt insert this row into youngSailors

6
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.

7
Types of triggers

• Row level triggers The code in the trigger is
  executed once for every row updated.
• 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.

8
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.
• After trigger is more efficient

9
Example (pseudocode)

• Create Before-Trigger
• For every string inserted into sailorName, turn
  it into upper case before insertion

10
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
Trigger timing
Triggering event
Further restricts when trigger is fired
11
Backing Up Data
create table sailors( sid number, sname
VARCHAR2(30), rating number check(rating lt
10), age number )
create table sailors_backup( who
varchar2(30), when_changed date,
sid number, old_rating number,
new_rating number )
12
Backing Up Data
CREATE or REPLACE TRIGGER backup_trig AFTER
UPDATE of Rating on Sailors FOR EACH ROW WHEN
(old.rating lt new.rating) BEGIN INSERT INTO
sailors_backup VALUES (USER, SYSDATE,
old.sid, old.rating, new.rating) END /

• 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..)

13
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?

14
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 /
15
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?

16
Another example

• create or replace trigger trig2
• after update of rating on sailors
• for each row
• DECLARE
• diff numberabs((old.rating)-(new.rating))
• BEGIN
• If ((old.rating)gt(new.rating)) then
  dbms_output.put_line('The rating of
  'old.sname' has dropped by 'diff)
• elsif ((old.rating)lt(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
• /

17
Trigger Compilation Errors

• As with procedures and functions, when creating a
  Trigger with errors, you will get the message
• Warning Trigger created with compilation
  errors.
• 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

18
Additional Types of Triggers

• Can also define triggers for
• logging in and off
• create/drop table events
• system errors
• etc.

19
Optimization Recap and examples
20
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

21
Disk-Memory-CPU
Delete from Sailors where sid90
sailors
Reserves
DISK
Main Memory
CPU
22
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.

23
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)

24
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.

25

• Suppose the table Students includes 105 tuples in
  103 blocks
• Select from students where snamemoshe
• With no index- read all blocks and search for
  moshe

Students
DISK
Main Memory
26
Sailor table
RID
27
Tree index on sname of sailors
A-gtM B1 N-gtZ B2
Root block
B1
B2
N-gtT L3 U-gtZ L4
A-gtG L1 H-gtM L2
Branch blocks
L2
L1
L4
L3
Boe(3,3) Bill(3,4)
Joe(3,1)
Vicky (4,1)
Phill (3,2)
Leaf blocks
28
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

29
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

30
Natural Join

• We want to compute
• We have 4 optional algorithms
• Block Nested Loops Join
• Index Nested Loops Join
• Sort Merge Join
• 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 relations2
31
Block Nested Loop Join

• Suppose there are B available blocks in the
  memory, BR blocks of relation R, and BS blocks of
  relations S, and RltS.
• 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))

32
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, X2.2
• If the index is tree-based and clustered, X3-4
• If it is not clustered, we evaluate X.

33
Sort-Merge Join

• Sort both relations on the join column
• Join them according to the join algorithm

34
Run time of Sort-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.

35
Question 1

• Suppose
• tuple size 100 bytes
• number of tuples (employees)3,000
• Page size1000 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.hobbystamps
36
Question 2

• How could you calculate this?

SELECT E.ename FROM Employees E, Departments
D WHERE D.dnameToy AND E.eidD.eid
37
Question 3
SELECT E.ename FROM Employees E, Departments
D WHERE E.eidD.eid

• 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?