Q:

Custom exception for ORA01400 not working

I'm doing a school assignment where I have to add exceptions in all PL/SQL. Since I've created some sequences and triggers to add auto-increment functionality (this is Oracle SQL), I thought I'd add an exception to all triggers concerning ORA-01400: cannot insert NULL into(...).
Following the example here, I've created a package to store my custom exception, so I can use it in all triggers that implement the auto-incrementation:
CREATE OR REPLACE PACKAGE my_exceptions
AS
    insert_null_into_notnull EXCEPTION;
    PRAGMA EXCEPTION_INIT(insert_null_into_notnull, -1400);
END my_exceptions;
/

This is one of the triggers:
CREATE OR REPLACE TRIGGER update_cust_id
BEFORE INSERT ON CUSTOMER
FOR EACH ROW
BEGIN
    SELECT pk_cust_seq.NEXTVAL INTO :NEW.CUST_ID FROM DUAL;
EXCEPTION
    WHEN my_exceptions.insert_null_into_notnull THEN
        DBMS_OUTPUT.PUT_LINE('Attempted to insert a null value into not nullable column.');
        RAISE;
END;
/

The problem is when I try to add a failed insert on purpose, I still get the typical Oracle error as: ORA-01400: cannot insert NULL into (...) rather than the DBMS_OUTPUT that I wanted.
I've looked at the example on the website a few times and can't see any difference, except that the exception is used in a procedure and not a trigger. Is that the problem? If so, how can I make this work in a trigger?
(OBS. This is just a school exercise to demonstrate error handling; DBMS_OUTPUT is probably not useful in this context, but that's not relevant for this exercise)

A:

The issue is as you mentioned in a comment. The ORA-01400 is raised by the insert, after the before trigger has fired and completed. If that wasn't the case, you wouldn't be able to set non-nullable columns automatically from a trigger - for example, setting a primary key value from a sequence (mimicking an autoincrement ID), as you are doing to set cust_id.  No exception is being raised within the PL/SQL block attached to the trigger.
You could check each not-nullable column manually and explicitly within the trigger, and raise your own exception if any are null, but you'd really just be duplicating the built-in functionality. And that assumes you keep track of the column definitions - if you change the nullability of a column, you'd have to remember to update the trigger to match, unless you figured put the columns to check dynamically at run-time - which would be tricky at best, if it's possible at all (with dbms_sql).
If your assignment doesn't actually require this custom handling, it seems like a lot of effort for no real gain. Oracle recommend you don't use triggers when you can use constraints; I know this is an exercise but even so, it's not something you're ever likely to want to do.

One way to force an run-time error in the trigger you showed is to manipulate the sequence so you try to exceed its MAXVALUE:
CREATE TABLE customer (cust_id NUMBER PRIMARY KEY, name VARCHAR2(20));

CREATE SEQUENCE pk_cust_seq MINVALUE 1 MAXVALUE 3 NOCYCLE;

CREATE OR REPLACE TRIGGER update_cust_id
BEFORE INSERT ON CUSTOMER
FOR EACH ROW
BEGIN
    SELECT pk_cust_seq.NEXTVAL INTO :NEW.CUST_ID FROM DUAL;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unable to create cust_id.');
        RAISE;
END;
/

Then the first three inserts would work, and the fourth would fail:
set serveroutput on
insert into customer(name) values ('Customer 1')

1 rows inserted.

insert into customer(name) values ('Customer 2')

1 rows inserted.

insert into customer(name) values ('Customer 3')

1 rows inserted.

insert into customer(name) values ('Customer 4')

Error starting at line : 24 in command -
insert into customer(name) values ('Customer 4')
Error report -
SQL Error: ORA-08004: sequence PK_CUST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
ORA-06512: at "SCHEMA.UPDATE_CUST_ID", line 6
ORA-04088: error during execution of trigger 'SCHEMA.UPDATE_CUST_ID'
08004. 00000 -  "sequence %s.NEXTVAL %s %sVALUE and cannot be instantiated"
*Cause:    instantiating NEXTVAL would violate one of MAX/MINVALUE
*Action:   alter the sequence so that a new value can be requested
Unable to create cust_id.

You already have the sequence, so in your case you could alter sequence to cap maxvalue just above whatever your current highest value it, and make sure nocycle is set; then cause the error, and reset it to a high value after you've done your test.
Also, just as an aside, if you're on 11g you have two ways to assign the sequence number to the column; before 11g you had to do it as you've shown:
SELECT pk_cust_seq.NEXTVAL INTO :NEW.CUST_ID FROM DUAL;

... but now you can do:
:NEW.CUST_ID := pk_cust_seq.NEXTVAL;

