create table department ( 
    id_dept int primary key, 
    name varchar2(50), 
    total_sal number(10,2) default 0 
)

create table employee ( 
    id_empl int primary key, 
    name varchar2(50), 
    salary number(8,2) default 0, 
    id_dept int references department 
)

insert into employee (id_empl, name, salary) values (1, 'gabriel', 999999.99)

select * from employee

create sequence seq_empl

select seq_empl.nextval from dual

create or replace trigger tg_empl_before_insert 
before insert 
on employee 
for each row 
begin 
select seq_empl.nextval into :new.id_empl from dual; 
end;

insert into employee (name,salary) values ('yoda', 3000.00);

select * from employee

create sequence seq_dept

create or replace trigger tg_dept_before_insert 
before insert 
on department 
for each row 
begin 
select seq_dept.nextval into :new.id_dept from dual; 
end;

insert into department (name) values ('tecnology')

select * from department

create or replace trigger tg_empl_after_insert 
after insert 
on employee 
for each row 
when (new.id_dept is not null) 
begin 
update department dept set dept.total_sal = dept.total_sal + :new.salary where :new.id_dept = dept.id_dept; 
end;

insert into employee (name, salary, id_dept) values ('maria', 2000.0, 1)

select * from department

create or replace trigger tg_empl_after_update 
after update 
on employee 
for each row 
begin 
update department deptOld set deptOld.total_sal = deptOld.total_sal - :old.salary where deptOld.id_dept = :old.id_dept; 
update department deptNew set deptNew.total_sal = deptNew.total_sal + :new.salary where deptNew.id_dept = :new.id_dept; 
end;

update employee set salary = 6000.0 where id_empl = 3

select * from department

insert into department (name) values ('rh')

insert into employee (name, salary, id_dept) values ('jose', 1000.0, 2);

select * from department

update employee set id_dept = 2 where id_empl = 3

select * from department

update employee set salary = 9000.0, id_dept = 1 where id_empl = 1

update employee set id_dept = 2 where id_empl = 2;

select * from department;

create or replace trigger tg_empl_after_delete 
after delete 
on employee 
for each row 
begin 
update department dept set dept.total_sal = dept.total_sal - :old.salary where dept.id_dept = :old.id_dept; 
end;

delete from employee where id_empl = 4

select * from employee

select * from department