Postgresql trigger to maintain coloumn uniqueness

yockee

I am writing a trigger in Postgresql database so that one particular coloumn can maintain its uniqueness.

The idea is to check the data before they are entered. The data are entered via Field Map.

So, I write a Function below :

CREATE OR REPLACE FUNCTION xyz_check_unique()
RETURNS TRIGGER
AS
$$
BEGIN
 IF (SELECT EXISTS(SELECT FROM scema1.chamber where id = new.id)) = 'false' THEN
   RETURN new;   //entry the new record
   else
   RAISE EXCEPTION SQLSTATE '90001' USING MESSAGE = 'DUP';
   return null;   //do not entry due to duplication
 end if;
END;

CREATE OR REPLACE TRIGGER xyz_check_unique
BEFORE update of id ON scema1.chamber
FOR EACH ROW EXECUTE PROCEDURE xyz_check_unique();

CREATE OR REPLACE TRIGGER xyz_check_unique
BEFORE insert ON scema1.chamber
FOR EACH ROW EXECUTE PROCEDURE xyz_check_unique();

 

That trigger works using Field Map if I enter an ID that's never been entered before.

If the ID entered has existed in the record, then an error comes up :

Then, if I want to entry another data, *i have to repeat the process from the very beginning, with all empty coloumns*. I f I just change the ID into another unique ID, the same error message comes up just like the picture above.

So, how can I employ such a trigger.

FYI, this trigger works in Desktop without error.

Thanks

 

yockee

I come to a conclusion that somehow the Field Map can not handle such error in a correct manner. 

Allow me to summarize the trouble after a trigger is implemented:

1. First, entry an *incorrect* record (data that has already existed)

2. The response from the Field Map : "Error.."

3. Second, entry a *correct* data.

4. The response from the Field Map : "Error.."

5. Repeat step 3 by directly hitting the Save button

6. The response from the Field Map : "Success !"

 

So, for my case above, the second entry (step number 3), the Field Map still regard it as error. The subsequent entry (step number 5), is success. The trigger is not a problem.

It seems that the Field Map still regard the second entry (step number 3) incorrect due to the first entry (step number 1). Perhaps there is a cache that has not been cleared. Or maybe something else.

I can not confirm the exact cause.

 

thanks