CODEWITHSUNDEEP
oracle-databaseplsqlroworacle-sqldeveloperdatabase-trigger
awyeanah2
awyeanah2

Reputation: 148

Using a row trigger to compare old dates to new dates

hey guys i have this problem that i just cannot seem to solve.

I have to create a row trigger so that when i insert a new application, an applicant cannot have applied for the same position within 30 days of their last application date for that position.

here is my code so far: i think i am on the right track but i just cant seem to string it all together. The inserted date should make the trigger go off.

CREATE OR REPLACE TRIGGER applicant_date
AFTER INSERT ON APPLIES
FOR EACH ROW
DECLARE applydate applies.appdate%TYPE;

BEGIN
SELECT appdate INTO applydate
FROM applies
where anumber=:New.anumber
and pnumber=:New.pnumber;

IF :New.appdate - applydate < 30 THEN
  RAISE_APPLICATION_ERROR(-20001,
                        'Applicantion within 30 days of last 
application.');
                        END IF;
END;
/

INSERT INTO APPLIES(anumber, pnumber, appdate)
VALUES(000004, 00000007, '13-JAN-2000');

its also asking me for to enter binds and i am just not experienced enough to get it all right. No matter what i put in i get: PLS-00487: Invalid reference to variable 'SQLDEVBIND1Z_2'

Here are the tables in that helps:

CREATE TABLE APPLIES(
anumber     NUMBER(6)   NOT NULL, /* Applicant number       */
pnumber     NUMBER(8)   NOT NULL, /* Position number        */
appdate     DATE        NOT NULL, /* Application date       */
CONSTRAINT APPLIES_pkey PRIMARY KEY ( anumber, pnumber, appdate ), 
CONSTRAINT APPLIES_fkey1 FOREIGN KEY ( anumber )
            REFERENCES APPLICANT ( anumber )
            ON DELETE CASCADE,
CONSTRAINT APPLIES_fkey2 FOREIGN KEY ( pnumber )
            REFERENCES POSITION ( pnumber ) 
            ON DELETE CASCADE);

 INSERT INTO APPLIES VALUES( 000001, 00000001, TO_DATE('13-DEC-1999','DD- 
 MON-YYYY') );
 INSERT INTO APPLIES VALUES( 000002, 00000001, TO_DATE('13-DEC-1999','DD- 
 MON-YYYY') );
 INSERT INTO APPLIES VALUES( 000003, 00000002, TO_DATE('14-NOV-1999','DD- 
 MON-YYYY') );
 INSERT INTO APPLIES VALUES( 000004, 00000002, TO_DATE('20-JAN-2000','DD- 
 MON-YYYY') );
 INSERT INTO APPLIES VALUES( 000005, 00000002, TO_DATE('22-JAN-2000','DD- 
 MON-YYYY') );
 INSERT INTO APPLIES VALUES( 000005, 00000003, TO_DATE('09-MAY-1999','DD- 
 MON-YYYY') );
 INSERT INTO APPLIES VALUES( 000006, 00000003, TO_DATE('17-JUN-1999','DD- 
 MON-YYYY') ); 
 INSERT INTO APPLIES VALUES( 000007, 00000003, TO_DATE('18-JUN-1999','DD- 
 MON-YYYY') );
 INSERT INTO APPLIES VALUES( 000007, 00000004, TO_DATE('13-APR-2000','DD- 
 MON-YYYY') );
 INSERT INTO APPLIES VALUES( 000008, 00000004, TO_DATE('13-APR-2000','DD- 
 MON-YYYY') );
 INSERT INTO APPLIES VALUES( 000009, 00000004, TO_DATE('14-APR-2000','DD- 
MON-YYYY') );
 INSERT INTO APPLIES VALUES( 000010, 00000005, TO_DATE('23-SEP-1999','DD- 
MON-YYYY') );
INSERT INTO APPLIES VALUES( 000001, 00000006, TO_DATE('26-OCT-1999','DD- 
MON-YYYY') );
INSERT INTO APPLIES VALUES( 000002, 00000006, TO_DATE('27-OCT-1999','DD- 
MON-YYYY') );
INSERT INTO APPLIES VALUES( 000003, 00000006, TO_DATE('28-OCT-1999','DD- 
MON-YYYY') );
INSERT INTO APPLIES VALUES( 000004, 00000007, TO_DATE('01-JAN-2000','DD- 
MON-YYYY') ); /*row i am trying to trigger*/
INSERT INTO APPLIES VALUES( 000005, 00000007, TO_DATE('03-JAN-2000','DD- 
MON-YYYY') );
INSERT INTO APPLIES VALUES( 000006, 00000007, TO_DATE('04-JAN-2000','DD- 
MON-YYYY') );
INSERT INTO APPLIES VALUES( 000007, 00000007, TO_DATE('07-JAN-2000','DD- 
MON-YYYY') );

Thank you so much in advance, much appreciated.

Upvotes: 1

Views: 101

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31686

As mentioned in the comments, I would recommend you to avoid Trigger for this requirement and would advise you to convert the INSERT as a part of the procedure or a PL/SQL block which raises an exception based on the insertion. 

DECLARE
  anumber  applies.anumber%type := 4;
  pnumber  applies.pnumber%type := 7;
  appdate  applies.appdate%type :=  to_date('13-JAN-2000','dd-mon-yyyy');
BEGIN
INSERT INTO APPLIES(anumber, pnumber, appdate)

with input(anumber, pnumber, appdate) as
  (
       select anumber, pnumber, appdate from dual  
  ) select * from input i 
        where not exists ( select 1 from applies ap where 
                                i.anumber = ap.anumber
                            and i.pnumber = ap.pnumber
                            and i.appdate - ap.appdate  < 30  ) ;
   IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
    'Application within 30 days of last application.');
   ELSE
     COMMIT;
   END IF;

END;
/

DEMO

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143023

I have no idea what error you got has to do anything with the rest of your message.

Anyway: AFTER trigger raises a mutating table error. If you switch to a BEFORE one, then it looks like this:

SQL> CREATE OR REPLACE TRIGGER applicant_date
  2     before INSERT
  3     ON APPLIES
  4     FOR EACH ROW
  5  DECLARE
  6     applydate  applies.appdate%TYPE;
  7  BEGIN
  8     SELECT appdate
  9       INTO applydate
 10       FROM applies
 11      WHERE     anumber = :New.anumber
 12            AND pnumber = :New.pnumber;
 13
 14     IF :New.appdate - applydate < 30
 15     THEN
 16        RAISE_APPLICATION_ERROR (-20001,
 17                                 'Applicantion within 30 days of last application.');
 18     END IF;
 19  END;
 20  /

Trigger created.

Testing:

SQL> INSERT INTO APPLIES(anumber, pnumber, appdate) VALUES(000004, 00000007, date '2000-01-13');
INSERT INTO APPLIES(anumber, pnumber, appdate) VALUES(000004, 00000007, date '2000-01-13')
            *
ERROR at line 1:
ORA-20001: Applicantion within 30 days of last application.
ORA-06512: at "SCOTT.APPLICANT_DATE", line 12
ORA-04088: error during execution of trigger 'SCOTT.APPLICANT_DATE'


SQL> INSERT INTO APPLIES(anumber, pnumber, appdate) VALUES(000004, 00000007, date '2000-04-13');

1 row created.

SQL>

Upvotes: 1

Related Questions