CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
lord_ozb
lord_ozb

Reputation: 105

Trigger to check foreign key not working in PLSQL

Here are my 2 tables that I am using

create table EMPLOYEE (
                    Eid number(5) primary key,
                    Ename char(30),
                    Designation char(30));

create table Attendance (
                        Eid number(5) references EMPLOYEE(Eid),
                        tdate date,
                        attendance char(2) check(attendance in('P','A')));

Here is the Procedure I am using to take attendance 

create or replace PROCEDURE take_attendance(
                                            id IN number,
                                            attendance IN char) IS
begin 
    insert into ATTENDANCE Values(id, sysdate, attendance);
end;

Here is the Trigger I am using

create or replace TRIGGER to_take_attendance
before insert or update of eid on ATTENDANCE
for each row
declare
    CURSOR c(n number) is select * from EMPLOYEE where eid = :new.eid;
    e c%rowtype;
    invalid_id EXCEPTION;
begin
open c(:new.eid);
    fetch c into e;
    if c%NOTFOUND then
        raise invalid_id;
    else
        NULL;
    end if;

EXCEPTION   
when invalid_id then
    close c;
    raise_application_error(-20001,'Invalid ID');

close c;
end;

Here are the Errors I am getting

begin
*
ERROR at line 1: 
ORA-20001: Invalid ID 
ORA-06512: at "101503028.TO_TAKE_ATTENDANCE", line 17 
ORA-04088: error during execution of trigger '101503028.TO_TAKE_ATTENDANCE' 
ORA-06512: at "101503028.TAKE_ATTENDANCE", line 5 
ORA-06512: at line 2

I am not sure why I am getting those errors. I am new to PL/SQL is my question my be very stupid.

Thanks!

EDIT - Removed the others in exception.

Upvotes: 1

Views: 595

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44971

You are getting errors because your trigger works as expected.
You are trying to insert to ATTENDANCE, ID that does not exist in EMPLOYEE.

Exception defined in your code:
raise_application_error(-20001,'Invalid ID');

Error received:
ORA-20001: Invalid ID

Upvotes: 2

Related Questions