ALTER TABLE "SALARY_STRUCTURE" DROP CONSTRAINT "SALARY_STRUCTURE_PK";

ALTER TABLE "SALARY_STRUCTURE" ADD CONSTRAINT "SALARY_STRUCTURE_PK" PRIMARY KEY ("RECORD_KEY", "SALARY_GRADE");

ALTER TABLE "SALARY_STRUCTURE" DROP CONSTRAINT "SALARY_STRUCTURE_FK";
 
ALTER TABLE "SALARY_STRUCTURE" ADD CONSTRAINT "SALARY_STRUCTURE_FK" FOREIGN KEY ("RECORD_KEY") REFERENCES LK_EMPLOYEE_POSITIONS ("RECORD_ID") ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TRIGGER "EMPLOYEES_BI"
ACTIVE BEFORE INSERT POSITION 1
AS
  DECLARE VARIABLE IS_UNIQUE INTEGER;
  DECLARE VARIABLE ATEMP_ID VARCHAR(16);
  DECLARE VARIABLE AHAS_NA INTEGER;
BEGIN
  SELECT COUNT(RECORD_ID)
  FROM DEPARTMENT_UNITS
  WHERE RECORD_ID='N/A'
  INTO :AHAS_NA;
  IF (AHAS_NA = 0) THEN BEGIN
    INSERT INTO "DEPARTMENT_UNITS" (RECORD_ID, DEPARTMENT_ID, UNIT_NAME)
    VALUES ('N/A', 'N/A', 'Not Available');
  END
  SELECT COUNT(RECORD_ID)
  FROM SALARY_STRUCTURE
  WHERE RECORD_ID='N/A'
  INTO :AHAS_NA;
  IF (AHAS_NA = 0) THEN BEGIN
    INSERT INTO "SALARY_STRUCTURE" (HOURLY_PAY, UPDATE_DATE, UPDATED_BY, PAY, SALARY_GRADE, RECORD_KEY, RECORD_ID)
    VALUES (0.0, CURRENT_DATE, USER, 0.0, 'N/A', 'N/A', 'N/A');
  END
  NEW.UPDATED_BY=USER;
  NEW.UPDATE_DATE=CURRENT_DATE;
  NEW.CURRENTLY_EMPLOYED='1';
  IF (NEW.SALARY_KEY IS NULL) THEN
    NEW.SALARY_KEY='N/A';
  IF (NEW.DEPARTMENT_ID IS NULL) THEN
    NEW.DEPARTMENT_ID='N/A';
  IF (NEW.EMPLOYEE_ID IS NULL) THEN BEGIN
    IS_UNIQUE=0;
    WHILE (IS_UNIQUE=0) DO BEGIN
      EXECUTE PROCEDURE ELF_HASH (NEW.PA_ID||NEW.FAMILY_NAME||NEW.FIRST_NAME||NEW.PA_ID||CURRENT_TIME) RETURNING_VALUES NEW.EMPLOYEE_ID;
      SELECT EMPLOYEE_ID
      FROM EMPLOYEES
      WHERE EMPLOYEE_ID=NEW.EMPLOYEE_ID
      INTO :ATEMP_ID;
      IF (ATEMP_ID IS NULL) THEN
        IS_UNIQUE=1;
      ELSE BEGIN
        EXECUTE PROCEDURE GET_UNIQUE_ID(NEW.PA_ID) RETURNING_VALUES NEW.EMPLOYEE_ID;
        IS_UNIQUE=0;
      END
    END
  END
END;