oracle - SQL library trigger -

im trying make library database. ensure 1 book can borrowed 1 person @ time. have no expirience triggers thought might ask you.

      create table "book"  (        "book_id"          integer                         not null,        "condition"               varchar2(50),        "isbn"               varchar2(50)                    not null,        constraint pk_book primary key ("book_id")     );      create table "borrowed"  (    "book_id"          integer                         not null,    "borrowed_id"        integer                         not null,    "user_id"            integer                         not null,    "date_borrowing"     date                            not null,    "date_returning"    date                            not null,    "returned"          smallint                        not null,    constraint pk_borrowed primary key ("book_id", "borrowed_id") ); 

atribute "returned" has yes or no value (1 or 0)

you don't need trigger this. borrowed table should structured this. note use of virtual column, requires oracle 11g+:

create table borrowed  (    borrowed_id integer not null primary key, -- should set unique id each row, using preferred method    book_id integer not null,    user_id integer not null,    date_borrowing date not null,    date_returning date  not null,    date_actualreturn date,    returned (case date_actualreturn null 0 else 1 end) ); 

then, want date_actualreturn have @ 1 null value per book. can unique index or constraint:

create table borrowed  (    borrowed_id integer not null primary key, -- should set unique id each row, using preferred method    book_id integer not null,    user_id integer not null,    date_borrowing date not null,    date_returning date  not null,    date_actualreturn date,    returned (case date_actualreturn null 0 else 1 end),    constraint unq_only_one_book_borrowed unique       (coalesce(date_actualreturn, date '1900-01-01'), book_id) ); 

this creates unique constraint on column. if book has not been returned, date looks same -- same book cannot borrowed twice. and, voila! no trigger.