====== pears.CascadeRule ======
<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 =====
Settings determining when Shifts and Vacancies are cascaded to Secondary Agencies.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **CascadeRuleID** | char(20) | NOT NULL |  |  |
| CompanyID | char(20) | NULL |  |  |
| VacancyID | char(20) | NULL |  |  |
| AgencyStoredSelectionID | char(20) | NULL |  |  |
| CascadeHours | double | NULL |  |  |
| HoursFrom | char(1) | NULL |  | E from entry, S from start, X specify time at entry, C from previous cascade (treat as E if Level 0), O Other (use CustomFunction) |
| CascadeLevel | smallint | NULL | 0 | 0 rule is applied by program to new shifts. >0 by jobs to already cascaded shifts |
| CustomFunction | char(100) | NULL |  |  |
| VacancyRule | smallint | NULL | 0 | 0 for shift, 1 for vacancy |
| WhenEntered | timestamp | NULL |  |  |
| WhoEntered | char(20) | NULL |  |  |

===== Primary Key =====
  * CascadeRuleID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| Company | CompanyID | [[database:tables:pears_company|pears.Company (companyid)]] | ON DELETE CASCADE |
| Vacancy | VacancyID | [[database:tables:pears_vacancy|pears.vacancy (vacancyid)]] | ON DELETE CASCADE |
| StoredSelection | AgencyStoredSelectionID | [[database:tables:pears_storedselection|pears.storedselection (storedselectionid)]] |  |

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

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

===== Triggers =====
^ Name ^ Timing ^ Event ^
| CascadeRuleinsert | before | insert order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."CascadeRule"
-- Table comment: Settings determining when Shifts and Vacancies are cascaded to Secondary Agencies.
-- Statement count: 10

CREATE TABLE "pears"."CascadeRule" (
    "CascadeRuleID"                  char(20) NOT NULL
   ,"CompanyID"                      char(20) NULL
   ,"VacancyID"                      char(20) NULL
   ,"AgencyStoredSelectionID"        char(20) NULL
   ,"CascadeHours"                   double NULL
   ,"HoursFrom"                      char(1) NULL
   ,"CascadeLevel"                   smallint NULL DEFAULT 0
   ,"CustomFunction"                 char(100) NULL
   ,"VacancyRule"                    smallint NULL DEFAULT 0
   ,"WhenEntered"                    timestamp NULL
   ,"WhoEntered"                     char(20) NULL
   ,PRIMARY KEY ("CascadeRuleID" ASC) 
)
go


COMMENT ON COLUMN "pears"."CascadeRule"."HoursFrom" IS 
	'E from entry, S from start, X specify time at entry, C from previous cascade (treat as E if Level 0), O Other (use CustomFunction)'
go


COMMENT ON COLUMN "pears"."CascadeRule"."CascadeLevel" IS 
	'0 rule is applied by program to new shifts. >0 by jobs to already cascaded shifts'
go


COMMENT ON COLUMN "pears"."CascadeRule"."VacancyRule" IS 
	'0 for shift, 1 for vacancy'
go


COMMENT ON TABLE "pears"."CascadeRule" IS 
	'Settings determining when Shifts and Vacancies are cascaded to Secondary Agencies.'
go


ALTER TABLE "pears"."CascadeRule"
    ADD FOREIGN KEY "Company" ("CompanyID" ASC)
    REFERENCES "pears"."Company" ("companyid")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."CascadeRule"
    ADD FOREIGN KEY "Vacancy" ("VacancyID" ASC)
    REFERENCES "pears"."vacancy" ("vacancyid")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."CascadeRule"
    ADD FOREIGN KEY "StoredSelection" ("AgencyStoredSelectionID" ASC)
    REFERENCES "pears"."storedselection" ("storedselectionid")
go


create trigger "CascadeRuleinsert" before insert order 1 on
"pears"."CascadeRule"
referencing new as "new_tsq"
for each row
begin
  if "new_tsq"."WhoEntered" is null then set "new_tsq"."WhoEntered" = "userstaffid" end if;
  set "new_tsq"."WhenEntered" = current timestamp
exception
  when others then
    set "new_tsq"."WhoEntered" = null
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."CascadeRule"."CascadeRuleinsert" IS 
{create trigger CascadeRuleinsert 
 before insert order 1 on
pears.CascadeRule
referencing new as new_tsq
for each row
begin
  if new_tsq.WhoEntered is null then set new_tsq.WhoEntered=userstaffid end if;
  set new_tsq.WhenEntered=current timestamp
exception
  when others then
    set new_tsq.WhoEntered=null
end
}
go
</code>