SQL & PL/SQL

Hello,

I have the following problem:
I want to place a constraint on a column that enforces uniqueness dependant on the attribute in another column.

For example:

---------------------------------------------
ID | USERNAME      | STATUS          | comment      
---------------------------------------------
1  | BEND          | CURRENT           allowed
2  | BEND          | NOT CURRENT       allowed
3  | BEND          | CURRENT           not allowed
----------------------------------------------

I have formed some code like this to test my idea, and would like to include it in a function or trigger but I am unsure on how to implement it, or can I just reference it on the column like a constraint.
Or is there an easier way to accomplish this.
Thank You for any assistance.

DECLARE
  data VARCHAR2(5);
  p_username VARCHAR2(4000);
  
BEGIN
p_username := 'BENDA';

   SELECT username
     INTO data
     FROM schemaName.tableName
    WHERE username = p_username
      AND status = 'CURRENT';
      
  IF ( sql%found ) THEN
    dbms_output.put_line('EXISTS' );
  ELSIF ( sql%notfound ) THEN
    dbms_output.put_line('NOT EXISTS' );
  END IF;
  
EXCEPTION

WHEN no_data_found THEN

  IF ( sql%found ) THEN
    dbms_output.put_line( 'EXISTS' );
  ELSIF ( sql%notfound ) THEN
    dbms_output.put_line( 'NOT EXISTS' );
  END IF;
  
END;
/

Edited by: Benton on Mar 18, 2010 9:35 AM