CODEWITHSUNDEEP
sqlplsqloracle11gsqlplusplsqldeveloper
Kabil
Kabil

Reputation: 96

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

I'm trying to create a before insert Trigger in PL/SQL which will prevent inserting empty data into the table row. (A trigger to enforce not null condition and prevents inserting numbers or special characters.

This is the table I 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 the Trigger that I created.

create or replace
TRIGGER CUS_MAND before insert OR update on CUSTOMER
begin
  CASE
    WHEN :NEW.CUST_FORENAME = NULL
      THEN DBMS_OUTPUT.PUT_LINE ('Please enter customer forename.');
    WHEN :NEW.CUST_SURNAME = NULL
      THEN DBMS_OUTPUT.PUT_LINE ('Please enter customer surname.');
    WHEN :NEW.EMAIL_ADDRESS = NULL
      THEN DBMS_OUTPUT.PUT_LINE ('Please enter customer email address.');
  END CASE;
END;

But this trigger is not working successfully. Can someone solve the problem for me please?

Upvotes: 1

Views: 5353

Answers (2)

jagra
jagra

Reputation: 553

You are checking for null values the wrong way:

:NEW.CUST_FORENAME = NULL

Null values checking must use "is null" or "is not null". Equal operator doesn't work. Nothing is ever equal or different to NULL.

So, if A := null:

a = Null => false
a != null => false
a is null => true
a is not null => false

Also, your trigger should be "FOR EACH ROW" as pointed by Frank Schmitt.

If your validations are simple, using not null constraints or check constraints might be a best option.

Upvotes: 0

Frank Schmitt
Frank Schmitt

Reputation: 30765

As @APC pointed out, it would make more sense to use NOT NULL constraints than a trigger for this purpose.

However, if you want to use a trigger, you should raise an error instead of just printing to DBMS_OUTPUT because

  • printing a message will not prevent insertion of the wrong data
  • usually, INSERTS / UPDATES will be run by some kind of client program, and DBMS_OUTPUT will not be shown in this client program

Apart from that, you've got a couple of errors in your trigger:

  • you need to declare your trigger as FOR EACH ROW, otherwise, you won't be able to use :NEW and :OLD (because your trigger will be fired once per statement, not once per row)
  • your CASE statement lacks the DEFAULT branch, so if all checks are successfull, you'll get an error during execution; I'd use an IF instead

One possible solution:

create or replace trigger CUS_MAND before insert OR update on CUSTOMER
for each row        
begin
  if :NEW.CUST_FORENAME is NULL THEN
    RAISE_APPLICATION_ERROR(-20001,
                            'Please enter customer forename.');
  end if;
  if :NEW.CUST_SURNAME is NULL THEN
    RAISE_APPLICATION_ERROR(-20002,
                            'Please enter customer surname.');
  end if;
  if :NEW.EMAIL_ADDRESS is NULL THEN
    RAISE_APPLICATION_ERROR(-20003,
                            'Please enter customer email address.');
  END if;
END;

Here, I use RAISE_APPLICATION_ERROR to raise a user-defined exception; you can either use the same error number (-20001) for all errors or use the number to distinguish between them.

Upvotes: 4

Related Questions