pears.WithHolds

Stores witholds. When a worker is blocked from a vacancy or a company

• WithHoldsID

• No incoming foreign keys found.

• No indexes found.

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."WithHolds"
-- Table comment: Stores witholds.  When a worker is blocked from a vacancy or a company
-- Statement count: 11
 
CREATE TABLE "pears"."WithHolds" (
    "WithHoldsID"                    CHAR(20) NOT NULL
   ,"PersonID"                       CHAR(20) NULL
   ,"CompanyID"                      CHAR(20) NULL
   ,"VacancyID"                      CHAR(20) NULL
   ,"Note"                           long VARCHAR NULL
   ,"StaffID"                        CHAR(20) NULL
   ,"ReasonID"                       CHAR(20) NULL
   ,"WhenCreated"                    TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP
   ,PRIMARY KEY ("WithHoldsID" ASC) 
)
GO
 
 
COMMENT ON TABLE "pears"."WithHolds" IS 
	'Stores witholds.  When a worker is blocked from a vacancy or a company'
GO
 
 
ALTER TABLE "pears"."WithHolds"
    ADD FOREIGN KEY "person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."WithHolds"
    ADD FOREIGN KEY "company" ("CompanyID" ASC)
    REFERENCES "pears"."Company" ("companyid")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."WithHolds"
    ADD FOREIGN KEY "vacancy" ("VacancyID" ASC)
    REFERENCES "pears"."vacancy" ("vacancyid")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."WithHolds"
    ADD FOREIGN KEY "staff" ("StaffID" ASC)
    REFERENCES "pears"."staff" ("staffid")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."WithHolds"
    ADD FOREIGN KEY "WithHoldReason" ("ReasonID" ASC)
    REFERENCES "pears"."WithHoldReason" ("ReasonID")
    ON DELETE SET NULL
GO
 
 
CREATE TRIGGER "Withholds_UpdateAudit" BEFORE UPDATE OF "reasonid"
ORDER 1 ON
"pears"."Withholds"
REFERENCING OLD AS "oldw" NEW AS "neww"
FOR each ROW
BEGIN
  DECLARE "NewDesc" CHAR(200);
  DECLARE "OldDesc" CHAR(200);
  DECLARE "PName" CHAR(200);
  SELECT "string"(CASE "type" WHEN 'T' THEN 'Candidate: ' WHEN 'C' THEN 'Client: ' WHEN 'A' THEN 'Agency: ' END,"description")
    INTO "OldDesc" FROM "WithholdReason" WHERE "reasonid" = "oldw"."reasonid";
  SELECT "string"(CASE "type" WHEN 'T' THEN 'Candidate: ' WHEN 'C' THEN 'Client: ' WHEN 'A' THEN 'Agency: ' END,"description")
    INTO "NewDesc" FROM "WithholdReason" WHERE "reasonid" = "neww"."reasonid";
  SET "PName" = "string"((SELECT "name" FROM "person" WHERE "personid" = "neww"."personid"),' ',(SELECT "name" FROM "company" WHERE "companyid" = "neww"."companyid"),
    ' ',(SELECT "position"+' '+"refcode" FROM "vacancy" WHERE "vacancyid" = "neww"."vacancyid"));
  CALL "AuditLog"('PERSON',"neww"."PersonID",'Withhold Reason edited '+"PName","OldDesc","NewDesc");
  CALL "AuditLog"('COMPANY',"neww"."CompanyID",'Withhold Reason edited '+"PName","OldDesc","NewDesc");
  IF "neww"."VacancyID" IS NOT NULL THEN CALL "AuditLog"('VACANCY',"neww"."VacancyID",'Withhold Reason edited '+"PName","OldDesc","NewDesc")
  END IF
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."WithHolds"."Withholds_UpdateAudit" IS 
{CREATE TRIGGER Withholds_UpdateAudit 
 BEFORE UPDATE OF reasonid ORDER 1 ON
pears.Withholds
REFERENCING  NEW AS neww OLD AS oldw
FOR each ROW
BEGIN
  DECLARE NewDesc CHAR(200);
  DECLARE OldDesc CHAR(200);
  DECLARE PName CHAR(200);
  SELECT string( CASE TYPE WHEN 'T' THEN 'Candidate: ' WHEN 'C' THEN 'Client: ' WHEN 'A' THEN 'Agency: '  END, description) 
     INTO OldDesc FROM WithholdReason WHERE reasonid = oldw.reasonid;
  SELECT string( CASE TYPE WHEN 'T' THEN 'Candidate: ' WHEN 'C' THEN 'Client: ' WHEN 'A' THEN 'Agency: '  END, description) 
     INTO NewDesc FROM WithholdReason WHERE reasonid = neww.reasonid;
  SET PName = string((SELECT name FROM person WHERE personid = neww.personid),' ',(SELECT name FROM company WHERE companyid = neww.companyid),
  ' ',(SELECT POSITION+' '+refcode FROM vacancy WHERE vacancyid = neww.vacancyid));
  CALL AuditLog('PERSON',neww.PersonID,'Withhold Reason edited '+PName,OldDesc,NewDesc);
  CALL AuditLog('COMPANY',neww.CompanyID,'Withhold Reason edited '+PName,OldDesc,NewDesc);
  IF neww.VacancyID IS NOT NULL THEN CALL AuditLog('VACANCY',neww.VacancyID,'Withhold Reason edited '+PName,OldDesc,NewDesc) END IF
END
}
GO
 
 
CREATE TRIGGER "Withholds_DeleteAudit" BEFORE DELETE ORDER 1 ON
"pears"."Withholds"
REFERENCING OLD AS "oldw"
FOR each ROW
BEGIN
  DECLARE "OldDesc" CHAR(200);
  DECLARE "PName" CHAR(200);
  SELECT "string"(CASE "type" WHEN 'T' THEN 'Candidate: ' WHEN 'C' THEN 'Client: ' WHEN 'A' THEN 'Agency: ' END,"description")
    INTO "OldDesc" FROM "WithholdReason" WHERE "reasonid" = "oldw"."reasonid";
  SET "PName" = "string"((SELECT "name" FROM "person" WHERE "personid" = "oldw"."personid"),' ',(SELECT "name" FROM "company" WHERE "companyid" = "oldw"."companyid"),
    ' ',(SELECT "position"+' '+"refcode" FROM "vacancy" WHERE "vacancyid" = "oldw"."vacancyid"));
  CALL "AuditLog"('PERSON',"oldw"."PersonID",'Withhold Reason deleted '+"PName","OldDesc",' ');
  CALL "AuditLog"('COMPANY',"oldw"."CompanyID",'Withhold Reason deleted '+"PName","OldDesc",' ');
  IF "oldw"."VacancyID" IS NOT NULL THEN CALL "AuditLog"('VACANCY',"oldw"."VacancyID",'Withhold Reason deleted '+"PName","OldDesc",' ') END IF
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."WithHolds"."Withholds_DeleteAudit" IS 
{CREATE TRIGGER Withholds_DeleteAudit 
 BEFORE DELETE ORDER 1 ON
pears.Withholds
REFERENCING OLD AS oldw
FOR each ROW
BEGIN
  DECLARE OldDesc CHAR(200);
  DECLARE PName CHAR(200);
  SELECT string( CASE TYPE WHEN 'T' THEN 'Candidate: ' WHEN 'C' THEN 'Client: ' WHEN 'A' THEN 'Agency: '  END, description) 
      INTO OldDesc FROM WithholdReason WHERE reasonid = oldw.reasonid;
  SET PName = string((SELECT name FROM person WHERE personid = oldw.personid),' ',(SELECT name FROM company WHERE companyid = oldw.companyid),
  ' ',(SELECT POSITION+' '+refcode FROM vacancy WHERE vacancyid = oldw.vacancyid));
  CALL AuditLog('PERSON',oldw.PersonID,'Withhold Reason deleted '+PName,OldDesc,' '); 
  CALL AuditLog('COMPANY',oldw.CompanyID,'Withhold Reason deleted '+PName,OldDesc,' ');
  IF oldw.VacancyID IS NOT NULL THEN CALL AuditLog('VACANCY',oldw.VacancyID,'Withhold Reason deleted '+PName,OldDesc,' ') END IF;
END
}
GO