-- Sample database to illustrate the concepts of constraints and triggers

CREATE TABLE Sneakers (
	name	CHAR(20) PRIMARY KEY,
	manf	CHAR(20)
);

CREATE TABLE Buyers (
	name	CHAR(20) PRIMARY KEY,
	addr    CHAR(20),
	phone   CHAR(15)
);

CREATE TABLE Stores (
	name	CHAR(20) PRIMARY KEY,
	addr    CHAR(20),
	owner   CHAR(20)
);

CREATE TABLE Sells (
	store	CHAR(20),
	sneaker	CHAR(20) check ( sneaker in ('Nike', 'Adidas', 'AirJordan', 'LiNing')),
	price	REAL check (store='Joe''s Store' or price <= 50.00),
	FOREIGN KEY(sneaker) REFERENCES Sneakers(name)
	on delete set null
	on update cascade,
	FOREIGN KEY(store) REFERENCES Stores(name)
	on delete set null
	on update cascade,
	primary key (store, sneaker)
);


CREATE trigger NoRipoffStores before insert on sells
begin
   select
   case
   --when not exists (
   when exists (
      SELECT store FROM Sells
      GROUP BY store
      HAVING 50.00 < AVG(price)
   ) then
   raise ( abort, 'Invalid price...')
  end;
end;

CREATE TRIGGER SneakerTrig
	AFTER INSERT ON Sells
	FOR EACH ROW
	WHEN (New.sneaker NOT IN
		(SELECT name FROM Sneakers))
        begin
	INSERT INTO Sneakers(name)
		VALUES(New.sneaker);
	end;

CREATE TRIGGER PriceTrig
	AFTER UPDATE OF price ON Sells
	FOR EACH ROW
	WHEN(New.price > Old.price + 10.00)
        begin
	INSERT INTO RipoffStores
		VALUES(New.store);
        end;

