Create trigger in Oracle DB to provide the fixed length for an individual column

Question

Create trigger on the table Employees, which should:

Trigger on the data insertion command Format the phone field, converting it to the pattern (YYY)XXX-XX-XX. The number you enter must be 10 digits. The trigger should check for this. If the number has less than or more than 10 digits, then prohibit data insertion and issue an error message.

Tables:

CREATE TABLE dept
(
  dept_id    number(3) not null,
  dept_name  varchar2(50) not null,
  emp_cnt    number(3) not null,
  constraint dept_pk primary key (dept_id)
);
/
CREATE TABLE employees
(
  emp_id   number(3) not null,
  dept_id  number(3) not null,
  emp_name varchar2(50) not null,
  address  varchar2(100),
  phone    varchar2(20) not null,
  salary   number(8,2) not null,
  constraint emp_pk primary key (emp_id),
  constraint dept_fk foreign key (dept_id) references dept(dept_id)
);
/

Please, help me

Answer 1

You don't need to create a trigger for this aim, just add a check constraint to the table such as

ALTER TABLE employees
  ADD CONSTRAINT cc_emp_phone_no_len
  CHECK(LENGTH(phone)=10);

it will hurl with

ORA-02290 check constraint (<schema_name>.cc_emp_phone_no_len) violated

error message, if somebody tries to enter a numeric value whose length is not ten

Answer 2

Using CHECK constraint will not solve your problem as you have to mask your Phone, which will ultimately have more than 10 digits. So you have to put a check condition in your TRIGGER. For masking the phone number in the desired format, You can use the below trigger -

CREATE OR REPLACE TRIGGER trig_mask_phone
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
     IF LENGTH(:new.phone) < 10 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Phone No is incorrect');
     END IF;
     :new.phone := '(' || SUBSTR(:new.phone, 1, 3) || ')' || SUBSTR(:new.phone, 4, 3) || '-' || SUBSTR(:new.phone, 7, 2) || '-' || SUBSTR(:new.phone, 9, 2);
END;
/

Demo.