pears.WithHolds [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.WithHolds ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

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

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **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 | current timestamp |  |

===== Primary Key =====
  * WithHoldsID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] | ON DELETE CASCADE |
| company | CompanyID | [[database:tables:pears_company|pears.Company (companyid)]] | ON DELETE CASCADE |
| vacancy | VacancyID | [[database:tables:pears_vacancy|pears.vacancy (vacancyid)]] | ON DELETE CASCADE |
| staff | StaffID | [[database:tables:pears_staff|pears.staff (staffid)]] | ON DELETE CASCADE |
| WithHoldReason | ReasonID | [[database:tables:pears_withholdreason|pears.WithHoldReason (ReasonID)]] | ON DELETE SET NULL |

===== Referenced By =====
  * No incoming foreign keys found.

===== Indexes =====
  * No indexes found.

===== Triggers =====
^ Name ^ Timing ^ Event ^
| Withholds_UpdateAudit | before | update of "reasonid" order 1 |
| Withholds_DeleteAudit | before | delete order 1 |

===== Original SQL =====
<code sql>
-- 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
</code>