Re: Triggers

[Phillip Ward]

So a Book can be loaned out to one and only one "borrower", who can be either a Professor or a Student. Sounds to me like to need to abstract a Person "type" and allow Persons to borrow books.

select * 
from Books ; 

+------+----------+--------------+---------------------------------------+
| id   | borrower | Author       | Title                                 |
+------+----------+--------------+---------------------------------------+
|   42 |       77 | Adams, D     | Hitchhiker's Guide to the Galaxy, The | 
| 1941 |       99 | Spielberg, S | 1941                                  | 
| 2001 | NULL     | Kubrick, S   | Space Odyssey, A                      | 
+------+----------+--------------+---------------------------------------+

select * 
from Persons ; 

+----+-----------+------------+-----------+----------+
| id | tipe      | surname    | forenames | address  |
+----+-----------+------------+-----------+----------+
| 77 | Student   | Flintstone | Fred      | ...      |
| 88 | Student   | Rubble     | Barney    | ...      |
| 99 | Professor | Slate      | Mr        | ...      |
+----+-----------+------------+-----------+----------+

Then ... 

select 
  b.title 
, p.tipe
, p.forenames 
, p.surname 
from Books b
inner join Persons p 
   on b.borrower = p.id 
order by 1 ; 

+---------------------------------------+-----------+-----------+------------+
| title                                 | tipe      | forenames | surname    | 
+---------------------------------------+-----------+-----------+------------+
| 1941                                  | Professor | Mr        | Slate      |
| Hitchhiker's Guide to the Galaxy, The | Student   | Fred      | Flintstone | 
+---------------------------------------+-----------+-----------+------------+

Regards, Phill W.