Creating a Trigger that runs on two tables

Question

I have two tables, COPY and BORROW. Their columns are:

COPY (
Copy_id,
Bk_id,
Loc_id,
Opinion
)

and

BORROW (
Cus_evo,
B_Date,
R_Date,
Fee,
Copy_id,
Cus_id
)

I want to configure a trigger that ensures Copies that stored in a Exact location (London) (from Loc-id column of COPY table) cannot be Borrowed in December (from B_Date column of BORROW table).

I have created the following trigger:

CREATE OR REPLACE TRIGGER BORROW_TRIGGER 
BEFORE INSERT ON BORROW FOR EACH ROW BEGIN
IF(TO_CHAR(TO_DATE(:NEW.B_Date, 'DD-MMM-YYYY'),'MMM'= 'DEC')
AND :NEW.Loc_id='LC0001')
THEN RAISE_APPLICATION_ERROR(-20669,'CANNOT BORROW BOOKS FROM LONDON STORE DURING MONTH DECEMBER');
END IF;
END;
/

The trigger is not created and have errors please cloud give me correct trigger for this??

Error i have experiencing

Errors: TRIGGER BORROW_TRIGGER
Line/Col: 3/5 PLS-00049: bad bind variable 'NEW.LOC_ID'

Answer 1

Since you only need to perform the check when b_date is in December, it's more efficient to add this as a when condition at the top of the trigger. This also simplifies the trigger logic.

create or replace trigger borrow_check_trg
    before insert on borrow 
    for each row
    when (to_char(new.b_date,'MM') = '12')
declare
    l_loc_id copy.loc_id%type;
begin
    select c.loc_id into l_loc_id
    from   copy c
    where  c.copy_id = :new.copy_id;

    if l_loc_id = 'LC0001' then
       raise_application_error(-20669, 'Books cannot be borrowed from the London store during December'); 
    end if;
end;