DROP TABLE books
CREATE TABLE books (
  id NUMBER GENERATED AS IDENTITY,
  title VARCHAR2(100) NOT NULL
);

INSERT INTO books (title) values('title1');
INSERT INTO books (title) values('title2');

Select * From books

Select User from dual

DROP TABLE books_Log;
CREATE TABLE books_Log (
  id_Log NUMBER GENERATED AS IDENTITY,
  id NUMBER,
  title VARCHAR2(100) ,
  Change_By  varchar2(100), 
  TrnsTyp varchar2(100) ,
  ChangeDate date
);

create or replace trigger Tr_Book
after update or delete
on books
for each row
DECLARE
   TrnsTyp VARCHAR2(10);
BEGIN
   -- determine the transaction type
   TrnsTyp := CASE  
         WHEN UPDATING THEN 'UPDATE'
         WHEN DELETING THEN 'DELETE'
   END;

   INSERT INTO books_Log (id, title,  Change_By,  TrnsTyp ,  ChangeDate)
   values(:old.id, :old.title,  User,  TrnsTyp ,  sysdate);
END;

Select * FROM books_Log;
DELETE FROM books_Log;

Select * FROM books;
UPDATE books set TITLE = 'NewTitle'
WHERE ID = 1;

Select * FROM books_Log;

UPDATE books set TITLE = 'NewTitle2'
WHERE ID = 1;

Select * FROM books_Log;

DELETE FROM books WHERE ID = 1;

Select * FROM books_Log;
---------------------------------

DROP trigger BIUD_STUDENT;

create or replace trigger T1_STUDENT
before insert or update or delete 
on STUDENT
for each row
DECLARE
   Avg_CGPA number;
   TrnsTyp VARCHAR2(10);
BEGIN
   -- determine the transaction type
   TrnsTyp := CASE  
         WHEN UPDATING THEN 'UPDATE'
         WHEN DELETING THEN 'DELETE'
   END;

   -- insert a row into the audit table   
   --SELECT user INTO v_username
   --FROM dual;
   --INSERT INTO Log  (table_name, transaction_name, by_user, transaction_date)
   --VALUES('CUSTOMERS', l_transaction, USER, SYSDATE);
   --ALTER TABLE Department ADD AvgCGPA NUMBER DEFAULT 0
   --SELECT * FROM Department
   
   SELECT SUM(CGPA) into Avg_CGPA FROM Student;
   
   
    --insert into PERSON(Perid) values(15);
 
   --Update Department 
   --Set 
  --    AVGCGPA = Avg_CGPA
  -- WHERE Dept_Name  =:new.Dept_Name;
   
   --dbms_output.put('Old AVGCGPA: ' || :old.CGPA);
   --dbms_output.put('Old AVGCGPA: ' );
END;


create or replace trigger T1_STUDENT
after insert or update or delete
on STUDENT
for each row
DECLARE
   Avg_CGPA number;
   TrnsTyp VARCHAR2(10);
BEGIN
   -- determine the transaction type
   TrnsTyp := CASE  
         WHEN UPDATING THEN 'UPDATE'
         WHEN DELETING THEN 'DELETE'
   END;

   -- insert a row into the audit table   
   --SELECT user INTO v_username
   --FROM dual;
   --INSERT INTO Log  (table_name, transaction_name, by_user, transaction_date)
   --VALUES('CUSTOMERS', l_transaction, USER, SYSDATE);
   --ALTER TABLE Department ADD AvgCGPA NUMBER DEFAULT 0
   --SELECT * FROM Department
   
   SELECT SUM(CGPA) into Avg_CGPA FROM Student;
   
   
    --insert into PERSON(Perid) values(15);
 
   Update Department 
   Set 
      AVGCGPA = Avg_CGPA
   WHERE Dept_Name  =:new.Dept_Name;
   
   --dbms_output.put('Old AVGCGPA: ' || :old.CGPA);
   --dbms_output.put('Old AVGCGPA: ' );
END;


SELECT * FROM student;
SELECT * FROM Department;

insert into student(ID, Name, Dept_Name, Tot_credit) values (10005,'AgjhAAA','CMPE',101)
insert into student(ID, Name, Dept_Name, Tot_credit) values (5435305,'derter','CMPE',101)

--UPDATE

UPDATE student
SET CGPA = 3.1
WHERE ID = 10001;



ALTER TRIGGER T1_STUDENT DISABLE;

ALTER TABLE student ENABLE ALL TRIGGERS;