时间戳

首页 > 解决方案 > 插入前的 SQL 触发器，将新的工作日期与上一个日期进行比较

sql - 插入前的 SQL 触发器，将新的工作日期与上一个日期进行比较

问题描述

我正在用这些表创建一个数据库

WORK(WORK_ID, WORK_CODE, DATE_BEGIN, DATE_END)  
ASSIGNMENTS( WORK_ID:WORK, WORK_CODE:WORK, EMPL_ID:EMPLOYEE)  
EMPLOYEE(EMPL_ID, NAME, SRN)

我想创建一个触发器以确保授予每个员工 1 天的休息时间，因此我需要在插入新分配之前触发触发器并检查 EMPL_ID 上次工作的日期以及新工作的日期工作距离新日期不到 1 天，触发器阻止插入并引发错误。我不知道如何与上次工作日期进行比较。我怎么能做这样的事情，在此先感谢您的帮助！

标签： sqloracledatetriggers

解决方案

像这样的东西？我删除了多余的列，因为它们无关紧要。

SQL> -- Tables
SQL> create table work
  2    (work_id     number,
  3     work_code   number,
  4     date_begin  date,
  5     date_end    date,
  6     constraint pk_work primary key (work_id, work_code)
  7    );

Table created.

SQL> create table employee
  2    (empl_id     number primary key);

Table created.

SQL> create table assignments
  2    (work_id     number,
  3     work_code   number,
  4     empl_id     number constraint fk_ae references employee (empl_id),
  5     constraint fk_aw foreign key (work_id, work_code)
  6       references work (work_id, work_code));

Table created.

SQL> insert into work (work_id, work_code, date_begin, date_end)
  2    select 1, 500, date '2018-05-20', date '2018-05-25' from dual union
  3    select 2, 500, date '2018-06-01', date '2018-06-05' from dual union
  4    select 3, 500, date '2018-06-06', date '2018-06-10' from dual;

3 rows created.

SQL> insert into employee(empl_id) values (100);

1 row created.

SQL> -- Trigger
SQL> create or replace trigger trg_bi_ass
  2    before insert on assignments
  3    for each row
  4  declare
  5    l_date_begin date;
  6    l_date_end   date;
  7  begin
  8    -- Previous work
  9    select max(w.date_end)
 10      into l_date_end
 11      from work w
 12      where (w.work_id, work_code) in (select a.work_id, a.work_code
 13                                       from assignments a
 14                                       where a.empl_id = :new.empl_id
 15                                      );
 16    -- This work
 17    select w.date_begin
 18      into l_date_begin
 19      from work w
 20      where w.work_id = :new.work_id
 21        and w.work_code = :new.work_code;
 22    -- Compare dates
 23    if l_date_begin - l_date_end <= 1 then
 24       raise_application_error(-20001, 'Employee has to rest for at least 1 day');
 25    end if;
 26  end;
 27  /

Trigger created.

SQL> -- Testing
SQL> -- This should be OK
SQL> insert into assignments (work_id, work_code, empl_id) values (1, 500, 100);

1 row created.

SQL> -- This should be OK, because there are several days between 2018-06-01 and 2018-05-25
SQL> insert into assignments (work_id, work_code, empl_id) values (2, 500, 100);

1 row created.

SQL> -- This should fails, because there's just 1 day between 2018-06-05 and 2016-06-06
SQL> insert into assignments (work_id, work_code, empl_id) values (3, 500, 100);
insert into assignments (work_id, work_code, empl_id) values (3, 500, 100)
            *
ERROR at line 1:
ORA-20001: Employee has to rest for at least 1 day
ORA-06512: at "SCOTT.TRG_BI_ASS", line 21
ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_ASS'


SQL>

推荐阅读