If I have 2 tables like this:
Authors(
book char(30) PRIMARY KEY,
earnings INT
);
Books(
book char(30),
sales INT
FOREIGN KEY(book) REFERENCES Authors(book)
);
How would I write a check to enforce earnings for a book being less than sales for the book in Oracle SQL and where would I put it since it uses values from both tables?
If my understanding of your condition "check to enforce earnings for a book to be less than sales for the book" is correct, the primary and foreign key creation in tables need to be repositioned as following:
Rem -- Books Table
create table books(
book char(30) primary key,
sales int
);
Rem -- Authors Table
create table authors (
book char(30),
earnings int,
foreign key(book) references books(book)
);
Rem -- Create a trigger to enforce the condition
Rem -- Earnings for a book should be less than sales
CREATE OR REPLACE TRIGGER check_earnings BEFORE
INSERT ON authors FOR EACH ROW
DECLARE
earnings_higher_than_sales EXCEPTION;
sales_val int;
BEGIN
select sales into sales_val from books where book=:new.book;
dbms_output.put_line('Corresponding sales value:'||sales_val);
IF :new.earnings > sales_val THEN
RAISE earnings_higher_than_sales;
END IF;
EXCEPTION
WHEN earnings_higher_than_sales THEN
RAISE_APPLICATION_ERROR(-20000, 'Earnings values can''t be greater than sales');
END;
/
Rem -- Insert some rows into books
insert into books values('davinci code',100);
insert into books values('alchemist',200);
insert into books values('monk who sold his ferrari',300);
insert into books values('digital fortress',400);
insert into books values('unbowed',500);
commit;
Rem -- Insert some rows into authors
Rem -- Following two will succeed
insert into authors values('davinci code',90);
insert into authors values('alchemist',180);
Rem -- Following will fail, as the earnings(320) is greater than sales(300)
insert into authors values('monk who sold his ferrari',320);
Rem -- Following will succeed
insert into authors values('monk who sold his ferrari',290);
Rem -- Following two will succeed
insert into authors values('digital fortress',340);
insert into authors values('unbowed',400);
commit;
So, the answer is to create trigger on before insert or update on table Authors.
