Database midterm

Midterm2(BOOK SHOP)

Database systems

Shakman AminaBekbossinova Ayaulym Madina Shanshar Darkhan Beksultan

start_

Presentation content

PROCEDURE

EXCEPTION

TRIGGERS

ER DIAGRAMS

FUNCTIONS

ER DIAGRAM

Guide Lorem ipsum dolor sit amet

info

PROCEDURE

LOREM IPSUM 02

Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nibh euismod aliquam erat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit lobortis.

info

EXCEPTION

info

LOREM IPSUM 03

Lorem ipsum dolor sit amet, consectetuer adipiscing elit volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit.

FUNCTION

CODING

report

info

CREATE OR REPLACE FUNCTION counter_of_records (t_name IN VARCHAR2, t_cond IN VARCHAR2) RETURN NUMBER IS count_num_var NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' t_name ' WHERE ' || t_cond INTO count_num_var; DBMS_OUTPUT.PUT_LINE('Number of records: ' || count_num_var); RETURN count_num_var; END; -- declaring DECLARE count_num NUMBER; BEGIN count_num := counter_of_records('BOOK', 'GENRE = ''Comedy'''); END;

TRIGGERS

info

CODING

report

3)CREATE TRIGGER insert_trgg FOR INSERT ON book COMPOUND TRIGGER BEFORE EACH ROW IS BEGIN :new.b_price:=5000; END BEFORE EACH ROW; END insert_trgg;

report

report

1)CREATE OR REPLACE TRIGGER prevent_high_price_deletion BEFORE DELETE ON BOOK FOR EACH ROW BEGIN IF :OLD.B_PRICE > 4000 THEN RAISE_APPLICATION_ERROR(-20001, 'Cannot delete books with high price'); END IF; END; DELETE FROM book WHERE b_id = 843835877;

2)CREATE OR REPLACE TRIGGER BOOK_PRICE_TRIG BEFORE INSERT ON BOOK FOR EACH ROW DECLARE c NUMBER; BEGIN SELECT COUNT(*) INTO c FROM BOOK; DBMS_OUTPUT.PUT_LINE('Before insert book table has ' c ' values.'); END; / insert into book values (101, 'Madin', 'Book2',200, 'Thriller');

4)CREATE OR REPLACE TRIGGER Check_total BEFORE INSERT OR UPDATE ON orders FOR EACH ROW BEGIN IF :new.total_price > 10000 THEN raise_application_error(-20001, 'Price should not be greater than 10000'); END IF; END; / insert into orders values(7823938037,7205338831,1547134050,6588);

report

Thanks for your attention

end