plsql - 创建一个数据库触发器来检查一个日期是否添加了多个记录？

Question

CREATE OR REPLACE TRIGGER POSITION_NUMBER
BEFORE UPDATE OR INSERT OR DELETE ON APPLIES
  DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  NUMBER_OF_POSITIONS NUMBER;

  BEGIN

  SELECT count(pnumber) INTO NUMBER_OF_POSITIONS  
  FROM APPLIES WHERE anumber = :NEW.anumber;
  IF( NUMBER_OF_POSITIONS > 2 AND count(APPDATE) > 2 )
 THEN
  RAISE_APPLICATION_ERROR(-20000,'an Employee cannot apply for 
  more than two positions');
   END IF;
   END;
  /

我试图创建一个触发器，如果​​申请人在同一天申请两个以上的职位，则该触发器会触发，但我不确定我将如何实施它的日期方面。下面是一组关系方案

标签： plsqltriggers

Answer 1

您可以使用 TRUNC 函数删除时间部分，然后查看应用程序日期是否与今天的日期匹配，无论时间如何。

此外，不需要自治事务杂注。您没有执行任何 DML。

CREATE OR REPLACE TRIGGER position_number
   BEFORE UPDATE OR INSERT OR DELETE
   ON applies
DECLARE
   number_of_positions   NUMBER;
BEGIN
   SELECT COUNT (pnumber)
     INTO number_of_positions
     FROM applies
    WHERE anumber = :new.anumber AND TRUNC (appdate) = TRUNC (SYSDATE);

   IF number_of_positions > 2
   THEN
      raise_application_error (
         -20000,
         'An Employee cannot apply for more than two positions on the same day');
   END IF;
END;
/