DB 11

--1
--drop table stat_employee;
--create table stat_employee(
--    id_employee integer check(id_employee>0),
--    old_salary number(38),
--    new_salary number(38)
--);

--alter table stat_employee add
--foreign key (ID_EMPLOYEE) references stuff(code);
/
--create or replace procedure upgrade_salary(id_dept in varchar)
--is
--begin
--    update stuff set oklad=(oklad+oklad*0.25) where part_code=id_dept and oklad<3000;
--    commit;
--end upgrade_salary;
/
--create or replace TRIGGER audit_salary
--AFTER UPDATE ON stuff
--for each row
--BEGIN
--  insert into stat_employee values(:old.code, :OLD.oklad, :NEW.oklad);
--END;

BEGIN
upgrade_salary(52);
END;

--2

--ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY,HH24:MI';
/
--CREATE SEQUENCE  "ID_SEQ_EMP"  MINVALUE 300100 MAXVALUE 500000 INCREMENT BY 1 START WITH 300150 CACHE 20 NOORDER  NOCYCLE ;
/

--create or replace TRIGGER BI_CHECK_AGE
--before INSERT ON stuff
--FOR EACH ROW
--declare
--    oldid number;
--BEGIN
--
--    if ROUND((SYSDATE-(:new.bday))/365,0)>=18 then
--        select id_seq_emp.nextval into :new.code from dual;
--    else
--        select null into :new.code from dual;
--    end if;
--END;

/
--старше 18
INSERT INTO stuff
   VALUES(0,'Евлеев', 'Семен',   'Петрович', TO_DATE('28.10.1950'),  TO_DATE('23.05.1996'),  52, 13, 'Н',   5500,   20000,  0,  0,  '300');
/
--младше 18
INSERT INTO stuff
VALUES
('101000','Егоров',   'Константин', 'Егорович',TO_DATE('28.10.2000','DD.MM.RR'),TO_DATE('23.05.1996','DD.MM.RR'),'52',  '13','Н','20000',NULL,'0','0','300');
--alter trigger BI_CHECK_AGE enable;

ROLLBACK;

--3
--create table reg_inf (
--  tip_o char(4) check(tip_o in ('I','U','D')),
--  data_kor date,
--  user_name varchar2(256),
--  shifr_sotr integer);
--
--update stuff set fired='Д' where code=20000;
--delete from stuff where code=200198;
--insert into stuff
--   values(0,'Евлеев',   'Семен',   'Петрович', to_date('28.10.1950'),  to_date('23.05.1996'),  52, 13, 'Н',   5500,   20000,  0,  0,  '300');
--create or replace TRIGGER AIUD_LOG_REG_INF
--after INSERT or update or delete ON stuff
--FOR EACH ROW
--declare
--   v_change char(1);
--   ip_info varchar2(256);
--   id_emp integer;
--BEGIN
--    if inserting then
--        v_change := 'I';
--        id_emp := :new.code;
--    elsif updating then
--        v_change := 'U';
--        id_emp := :new.code;
--    else
--        v_change := 'D';
--        id_emp := :old.code;
--    end if;
--
--    insert into reg_inf values(v_change, sysdate, (select sys_context('userenv','ip_address') from dual), id_emp);
--END;

--4

--create table total_emp(
--    id_dept integer,
--    sum_salary integer
--);
/
--create or replace procedure push_total_empl
--is
--    sum_salary integer;
--
--    CURSOR get_employees
--    IS
--    SELECT * FROM stuff;
--
--    cursor get_dept
--    is
--    select part_code id_dept from stuff group by part_code;
--begin
--    delete from total_emp;
--    for n in get_dept loop
--        select sum(oklad) into sum_salary from stuff where part_code=n.id_dept and fired_cause<>'Д';
--        insert into total_emp values(n.id_dept, sum_salary);
--    end loop;
--end;
/
BEGIN
    push_total_empl();
END;
/

--create or replace TRIGGER AIUD_push_total_empl
--after INSERT or update of oklad, fired_cause or delete ON stuff
--FOR EACH ROW
--declare
--BEGIN
--
--    if inserting then
--        update total_emp set sum_salary=sum_salary+:new.oklad where id_dept=:new.part_code;
--    elsif updating then
--        if :new.fired_cause<> :old.fired_cause then
--            if :new.fired_cause='Д' then
--                update total_emp set sum_salary=sum_salary-:new.oklad where id_dept=:new.part_code;
--            elsif :new.fired_cause='Н' then
--                update total_emp set sum_salary=sum_salary+:new.oklad where id_dept=:new.part_code;
--            end if;
--        end if;
--        if :new.oklad<>:old.oklad then
--            update total_emp set sum_salary=sum_salary+(:new.oklad-:old.oklad) where id_dept=:new.part_code;
--
--        end if;
--
--    elsif deleting then
--        update total_emp set sum_salary=sum_salary-:old.oklad where id_dept=:old.part_code;
--    end if;
--END;

UPDATE stuff SET oklad=2000 WHERE code=200420;
UPDATE stuff SET fired='Д' WHERE code=200119;
DELETE FROM stuff WHERE code=200135;
ROLLBACK;

INSERT INTO stuff
   VALUES(0,'Заминайко',   'Николай',   'Юрьевич',   TO_DATE('28.10.1950'),  TO_DATE('23.05.1996'),  25, 21, 'N',    5500,   20000,  0,  0,  '300');