Click here to Skip to main content
15,068,463 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Create a trigger which gets triggered when there is any inserting or updating on phone number column of the customer table and this trigger has to capture the customer number and check that number having 10 digits or not.


What I have tried:

create or replace trigger number_trigger
before insert or update on customers
for each row
begin
if length(:old.customer_number)>10 then
raise_application_error(-20500,'length is exceeded');
elseif inserting then 
dbms_output.put_line('number inserting');
elseif updating then
dbms_output.put_line('updating number');
end if;
end;
/
Posted
Updated 18-Jun-21 2:51am
Comments
OriginalGriff 18-Jun-21 5:15am
   
And?
What does it do that you didn't expect, or not do that you did?
What have you tried to do to find out why?
Are there any error messages, and if so, where and when? What did you do to make them happen?

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

1 solution

The trigger is checking the length of the old number, not the new or added number.
Change if length(:old.customer_number)>10 to if length(:new.customer_number)>10

Or even better, don't use triggers. Add a check on the column instead: BETWEEN 1000000000 AND 9999999999 assuming the number is without area code, so adjust as appropriate.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900