[Solved] PK on table is increased when TRIGGER is fired

[dreamquartz]

Hello All,

Have a simple BEFORE INSERT TRIGGER for PostalCodes.
I want prevent duplication.

Code: Select all

CREATE TRIGGER PUBLIC.POSTALCODE BEFORE INSERT ON PUBLIC."tPostalCode"
	 REFERENCING NEW ROW AS NEWROW FOR EACH ROW
	 BEGIN ATOMIC
		 IF EXISTS
			 (
				 SELECT
					 *
				 FROM
					 PUBLIC."tPostalCode"
				 WHERE
					 "tPostalCode"."PostalCode" = NEWROW."PostalCode"
			 )
			 THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='This PostalCode already exists. Please verify the Existing entry!';
		 END IF;
	 END

When the TRIGGER is fired, I normally do: "Undo: Data Entry".
This still leads to an increment of the PK: PostalCodeID.

No data is entered, so should the PK not increase?

Dream

[eremmel]

Don't worry about this kind of internals, its just a auto-incremental number. You can not use these numbers when you need strict consecutive numbering, as requested by some governments for financial statements. This behavior is common for all databases I know.

[dreamquartz]

You know, I just recently realized it. I do not recall ever reading or hearing about it.

Thanks for the clarification.

Dream