-- Online SQL Editor to Run SQL Online.
-- Use the editor to create new tables, insert data and all other SQL operations.
DROP TABLE student;
CREATE TABLE student
( id CHAR(20),
  age INTEGER 
);
INSERT INTO student(id,age)
VALUES('123',19);
INSERT INTO student(id,age)
VALUES('124',20);
DELETE 
FROM student
WHERE age = 19;
INSERT INTO student(id,age)
VALUES('123',19);
INSERT INTO student(id,age)
VALUES('125',18);
INSERT INTO student(id,age)
VALUES('125',null);
INSERT INTO student(id,age)
VALUES('126',18);

ALTER TABLE student
ADD COLUMN register CHAR(2);
ALTER TABLE student
DROP COLUMN register;

SELECT *
FROM Orders
WHERE customer_id = 1 or customer_id = 4;
DROP TABLE teacher;
CREATE TABLE teacher
( id char(20),
  age INTERGER
);
INSERT INTO teacher(id,age)
VALUES('46',30);
INSERT INTO teacher(id,age)
VALUES('47',32);
INSERT INTO teacher(id,age)
VALUES('48',45);
INSERT INTO teacher(id,age)
VALUES('126',18);
-- เเก้ เป็น cascade
CREATE Trigger key
before delete on student
--referencing old row as O
for each row
when(OLD.id IN (select id from teacher))
BEGIN 
DELETE 
FROM teacher where id = OLD.id;
END;

--DELETE 
--FROM student
--WHERE id ='126';

--ALTER TABLE student
--ADD COLUMN toy;

--UPDATE student 
--SET age = 20
--WHERE id = 125;

-- เเก้ foreign key
CREATE trigger R_age
after update of age on student
for each row
begin
UPDATE teacher
SET age = new.age
where id = new.id;
END;

UPDATE student 
SET age = 20
WHERE id = 126;

--key constrain ให้ไม่ซ้า
CREATE trigger Rx
before insert on student
for each row 
when exists(select * from student where id = new.id)
begin
select raise(ignore); --ข้ามไม่ใส่ข้อมูลถ้ามีข้อมูลนั้นอยู่เเล้ว
end;

INSERT INTO student(id,age) VALUES('124',28);

--ทำtrigger เเบบใช้select count(*) เพื่อนับจำนวน
CREATE trigger Ry
before insert on Orders
for each row 
when(select count(*) from Orders where item = 'Keyboard') > 3
begin
select raise(ignore);
end;
--ลองเทส trigger Ry
INSERT INTO Orders(order_id,item,amount,customer_id) VALUES(6,'Keyboard',300,5);
INSERT INTO Orders(order_id,item,amount,customer_id) VALUES(7,'Keyboard',350,1);