Oracle character count trigger

Question

Hello, I need to know how to create a trigger in the Oracle database for you to monitor a insert or update in an NCM field of a product so that it is not less than 8 characters, follows example below:

IF LENGTH(UPDATING ('NCM') OR INSERTING)
            AND (:NEW.NCM < 8)
            THEN
                RAISE_APPLICATION_ERROR (-20101,'Campo NCM deve possui 8 caracteres.');

Answer 1

Just like Motta said in the comment, instead of doing a Rigger you can do a Non-constraint check. Below are the two ways to implement verification(Trigger/check Constraint).

Trigger:

CREATE OR REPLACE TRIGGER NCM_CHECK
    BEFORE INSERT OR UPDATE ON TGFPRO
    FOR EACH ROW
    BEGIN
        IF :new.Ncm < 8 THEN
            RAISE_APPLICATION_ERROR (-20101,'Campo NCM deve possui 8 caracteres.');
        END IF;

END NCM_CHECK;

Check Constraint:

ALTER TABLE TGFPRO
ADD CONSTRAINT NCM_CHECK CHECK (LENGTHB(Ncm) < 8)

I hope I’ve helped, thank you!