Triggers and sequences in Oracle Database.

trigger.sql

-- Logs every time an employee's salary is changed between the first and fifth
-- business day's of any given month. Most comments are in Portuguese.

create table employee (
  id int not null,
  name varchar(20) not null,
  salary int not null
);

alter table employee add constraint employee_pk primary key (id);

create sequence employee_seq start with 1;

create or replace trigger employee_insert before insert on employee for each row
begin
  select employee_seq.nextval into :new.id from dual;
end;
/

create table employee_audit (
  id int not null,
  employee int not null,
  new_salary int not null,
  old_salary int not null,
  update_date date not null
);

alter table employee_audit add constraint employee_audit_pk primary key (id);
alter table employee_audit add constraint employee_audit_employee_fk foreign key (employee) references employee (id) on delete set null;

create sequence employee_audit_seq start with 1;

create or replace trigger employee_audit_insert before insert on employee_audit for each row
begin
  select employee_audit_seq.nextval into :new.id from dual;
end;
/

-- `for each row` specified the trigger should be called each row, otherwise it
-- would only be called once on a statement that affects multiple rows.
create or replace trigger employee_update before update on employee for each row
declare
  limit date;
  month varchar(6);
  today date;
  weekday varchar(9);
begin
  select to_char(sysdate, 'mon-yy') into month from dual;
  -- O limite é o quinto dia útil do mês. Maioria das vezes o quinto dia útil
  -- será do dia sete por causa do final de semana.
  limit := to_date('05-' || month) + 2;
  -- Achar o dia da semana do quinto dia deste mês.
  select to_char(to_date('05-' || month), 'day') into weekday from dual;
  -- Se o quinto dia deste mês foi numa sexta, ele também foi o quinto dia
  -- útil. Se foi numa quinta, o quinto dia útil será o dia seis.
  if weekday = 'friday' then
    limit := limit - 2;
  end if;
  if weekday = 'thursday' then
    limit := limit - 1;
  end if;
  select sysdate into today from dual;
  if :new.salary <> :old.salary and today between to_date('01-' || month) and limit then
    insert into employee_audit(employee, new_salary, old_salary, update_date) values (:new.id, :new.salary, :old.salary, today);
  end if;
end;
/

insert into employee(name, salary) values ('Pedro Minicz', 10000);
update employee set salary = 12000 where id = 1;
select * from employee_audit;