before INSERT or Update trigger plsql

Question

can anyone help me write a trigger to disallow particular entry into a table (for e.g. location = 'chicago' not allowed).The table schema is as follows department(deptno,deptname,location).I am using oracle 10g.

Answer 1

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

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

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

Constraint States

• ENABLE - ensure that all incoming data conforms to the constraint
• DISABLE - allow incoming data, regardless of whether it conforms to the constraint
• VALIDATE - ensure that existing data conforms to the constraint
• NOVALIDATE - existing data does not have to conform to the constraint

These can be used in combination

ENABLE { [default] VALIDATE | NOVALIDATE }

DISABLE { VALIDATE |[default] NOVALIDATE }

• ENABLE VALIDATE is the same as ENABLE.

• ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. this will resume constraint checking on disabled constraints without first validating all data in the table.

• DISABLE NOVALIDATE is the same as DISABLE.

• DISABLE VALIDATE disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns. for a UNIQUE constraint, this enables you to load data from a nonpartitioned table into a partitioned table using the ALTER TABLE.. EXCHANGE PARTITION clause.

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

Triggers should not be used to enforce business rules or referential integrity rules that could be implemented with simple constraints.

Example trigger(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;