Oracle: Need help on triggers

Question

Hey everyone it's me again, I've just started on a class in my degree on Oracle/Databases, I've posted this a few days ago which was answered promptly and really appreciated it.

Learning Oracle, Setting boolean based on date

Following up on this, consider I have these 2 tables

CREATE TABLE CUSTOMER (
    CustID NUMBER(5) NOT NULL,
    CONSTRAINT CustID_PK PRIMARY KEY (CustID),
    
    PlanTypeNo Number(1) NOT NULL,
    CONSTRAINT PlanTypeNo_CustomerFK FOREIGN KEY (PlanTypeNo) REFERENCES PLANTYPE(PlanTypeNo),
    
    CustName VARCHAR2(50) NOT NULL,
    CustICNo VARCHAR2(9) NOT NULL,
    CustTelNo VARCHAR2(8) NOT NULL,
    CONSTRAINT UniqueCustomer UNIQUE (CustICNo,CustTelNo),
    CONSTRAINT Exact_IC_Length CHECK (LENGTH(CustICNo) = 9),
    CONSTRAINT Exact_Tel_Length CHECK (LENGTH(CustTelNo) = 8),
    
    PaymentModePref VARCHAR2(15) NOT NULL,
    CONSTRAINT PaymentModePref_Accepted CHECK (PaymentModePref IN ('Paypal','Cash','EFT')),
    
    LastBillPaidDate DATE,
    hasUnpaidBill NUMBER(1) NOT NULL,
    CONSTRAINT UnpaidBill_Boolean CHECK (hasUnpaidBill IN ('0', '1')),
    
    CompanyName VARCHAR2(50),
    CompanyRegNo VARCHAR2(10),
    CONSTRAINT If_NonResident CHECK ((((PlanTypeNo) = 1) OR (CompanyName IS NOT NULL AND CompanyRegNo IS NOT NULL)))
    
    
);

and

CREATE TABLE LOCATION (
    LocationID NUMBER(6) NOT NULL,
    CONSTRAINT LocationID_PK PRIMARY KEY (LocationID),
    
    CustID Number(5) NOT NULL,
    CONSTRAINT CustID_LocationFK FOREIGN KEY (CustID) REFERENCES CUSTOMER(CustID),
        
    LocationName VARCHAR2(40) NOT NULL,
    LocationAddress VARCHAR2(50) NOT NULL,
    CONSTRAINT UniqueNameAddress_Location UNIQUE (LocationName, LocationAddress)
        
);

I'm relatively experienced with coding, but not database programs/scripting/querying. How would you go about creating a way (constraint maybe?) for the database to check if an owner that has an outstanding bill wants to add a new location?

I got as far as comparing the CustID Foreign Key to go back to the CUSTOMER table and check if they have any LastBillPaidDate that's > 60 days, either that or create a trigger that turns hasUnpaidBill to '1' if current date - LastBillPaidDate > 60, and then check if the value of hasUnpaidBill is 1 when attempting to INSERT a row

Hope it's not too confusing, I get the logic but have no idea how to implement it for databases :|

Thanks again!

Answer 1

Here's an example trigger. When writing a trigger, you can query other tables, you just generally can't query the table which is being inserted/updated. So it's fine to query CUSTOMER, but you should use the :NEW object to reference the fields on the LOCATION record currently being inserted.

set define off
CREATE OR REPLACE TRIGGER LOCATION.LOC_CUSTOMER_UNPAID
BEFORE INSERT 
FOR EACH ROW
DECLARE
    has_unpaid varchar2(1);
    unpaid_bill EXCEPTION;
    PRAGMA EXCEPTION_INIT( unpaid_bill, -20001 );
BEGIN

    select 'Y' into has_unpaid
    from CUSTOMER c
    where c.CustID = :new.CustID
      and lastBillPaidDate < sysdate-60;
    
    if has_unpaid = 'Y' then
        raise_application_error( -20001, 'Cannot create new location: Customer has unpaid bill.' );
    end if;
    
EXCEPTION when NO_DATA_FOUND then
    null; -- ignore
END;
/

(The set define off is not strictly necessary, but some IDEs will get confused and ask you to set a value for :NEW as a bind variable.)

I would say the database might not be the best place for this kind of logic. You can do it in a trigger, but it's sometimes a bit awkward - raising an error message backtrace from a trigger can look terrible on the client side if you can't catch the error and display a friendly message.

Edit: I'm defining a custom error message here. The process is a little convoluted. unpaid_bill EXCEPTION says we're creating a new error type, PRAGMA EXCEPTION_INIT(unpaid_bill, -20001) says to associate our error with code #-20001, and raise_application_error(-20001) says to raise/return that error code.

To give more background, if you want to prevent a user from doing something (like adding a location if they have an outstanding bill), you have a few options:

• a constraint is good for validating the current row being inserted. This is usually simple logic, like "PHONE_NUM does not have letters in it" or "AGE is greater than zero". If the constraint logic is violated, it raises an exception and blocks the insert.
• a BEFORE INSERT trigger can also be used - if it raises an exception, it will block the insert. They're slower and more complicated than constraints, and hard to debug, but also flexible - you can query other tables, call functions, etc. Good coders use them sparingly.
• using application logic should almost always be your first choice. It's the most flexible option, it's often faster, and you can show the user much more helpful warnings than the Oracle error stack traces.
• if your user-facing application is large and complex, or your users have database access, an API is also a good pattern. Write all your validation code in a stored procedure or package that does the inserts, and make your application/users call the API instead of inserting directly into the tables. You can enforce this pattern with grants/permissions, but I won't get into that here.