CODEWITHSUNDEEP
sqloracle-databaseoracle11gtriggersoracle-sqldeveloper
M.A.S
M.A.S

Reputation: 343

How to access existing data in before insert trigger?

I have a table

CREATE TABLE borrow_document1 (
  id_borrow INT,
  id_client INT,
  id_document INT,
  isReturned number(1),
  CONSTRAINT PK_document PRIMARY KEY ( id_borrow));

I want to prevent borrowing the same document if it is not returned (if isReturned is equal to 0). I am trying to create a trigger to do this job, but I don't know how to access to existing values in the table. here is what I have done:

create or replace trigger check_borrow
BEFORE INSERT on borrow_document1
for each row when(new.id_document = id_document)
BEGIN
IF ISRETURNED = 0 THEN
    raise_application_error(-20111,'document is borrowed');
end if;
END;

I know that this is wrong but I am just trying to show you what I need to do. The problem is that compiling this trigger returns error as id_document and isreturned are undeclared. I might also have many documents that have the same id in my table but at most one of them is borrowed, because I need to keep the trace of the borrowed documents. any help would be highly appreciated.

Upvotes: 1

Views: 414

Answers (1)

M.A.S
M.A.S

Reputation: 343

Well I tried this and it seems to work well :

create or replace trigger check_borrow
BEFORE INSERT on borrow_document1
for each row
declare
cpt int;
BEGIN
select COUNT(*) into cpt FROM borrow_document1 WHERE id_document = :new.id_document AND isReturned = '0';
IF cpt>0 THEN
        raise_application_error(-20111,'document is borrowed');
end if;
END;
/

Upvotes: 1

Related Questions