Before running INSERT or Update plsql

You can easily do what you want with CHECK CONSTRAINT in your column.

ALTER TABLE T
ADD CONSTRAINT constraint_name CHECK (location <> 'chicago') [DISABLE];

      

        
        
        
      

    

The DISABLE keyword is optional. If you create a validation constraint using the DISABLE keyword, the constraint will be created , but the condition will not be met .

States with restrictions

• ENABLE - make sure all incoming data meets the limit
• DISABLE - Allow incoming data regardless of whether it meets the restriction
• VALIDATE - make sure existing data meets the constraint
• NOVALIDATE - existing data must not meet the constraint

They can be used in combination

ENABLE {[default] VALIDATE | NOVALIDATE}

DISABLE {VALIDATE | [default] NOVALIDATE}

• ENABLE VALIDATE is the same as ENABLE.

• ENABLE NOVALIDATE means the constraint was checked, but it shouldn't be true for all rows. this will resume checking the constraints on the disabled constraints without first checking all the data in the table.

• DISABLE NOVALIDATE is the same as DISABLE.

• DISABLE VALIDATE disables the constraint, lowers the constraint index, and disallows any modification of the constrained columns. for a UNIQUE constraint, this allows data to be loaded from an unallocated table into a partitioned table using the ALTER TABLE .. EXCHANGE PARTITION clause.

Here is an example of an ADD trigger. However, it is better to create constraints on your schema, or do CUSTOM_INSERT PROCEDURE to filter it out. Here is a good article on Data Integrity - Constraints and Triggers .

Triggers should not be used to enforce business rules or referential integrity rules, which can be enforced with simple constraints.

Trigger example (consider it as a bad idea for filtering input):

CREATE TRIGGER myTrigger 
BEFORE INSERT
ON table
REFERENCING NEW AS New
FOR EACH ROW
   BEGIN
   IF (New.location = 'chicago') THEN
       RAISE cError;    
EXCEPTION
WHEN cError THEN
      RAISE_APPLICATION_EXCEPTION(-20001,'Chicago is not allowed');
END;