plsql - how to prevent inserting empty data into the table row using trigger in pl-sql -

i'm trying create before insert trigger in pl/sql prevent inserting empty data table row. (a trigger enforce not null condition , prevents inserting numbers or special characters.

this table created.

create table customer  (   customer_id integer  , cust_forename varchar2(50) not null  , cust_surname varchar2(50) not null  , gender char(1)  , email_address varchar2(30) not null  , constraint customer_pk primary key (customer_id)   enable  ); 

and trigger created.

create or replace trigger cus_mand before insert or update on customer begin   case     when :new.cust_forename = null       dbms_output.put_line ('please enter customer forename.');     when :new.cust_surname = null       dbms_output.put_line ('please enter customer surname.');     when :new.email_address = null       dbms_output.put_line ('please enter customer email address.');   end case; end; 

but trigger not working successfully. can solve problem me please?

as @apc pointed out, make more sense use not null constraints trigger purpose.

however, if want use trigger, should raise error instead of printing dbms_output because

• printing message not prevent insertion of wrong data
• usually, inserts / updates run kind of client program, , dbms_output not shown in client program

apart that, you've got couple of errors in trigger:

• you need declare trigger each row, otherwise, won't able use :new , :old (because trigger fired once per statement, not once per row)
• your case statement lacks default branch, if checks successfull, you'll error during execution; i'd use if instead

one possible solution:

create or replace trigger cus_mand before insert or update on customer each row         begin   if :new.cust_forename null     raise_application_error(-20001,                             'please enter customer forename.');   end if;   if :new.cust_surname null     raise_application_error(-20002,                             'please enter customer surname.');   end if;   if :new.email_address null     raise_application_error(-20003,                             'please enter customer email address.');   end if; end; 

here, use raise_application_error raise user-defined exception; can either use same error number (-20001) errors or use number distinguish between them.