Table of Contents



pears.CascadeRule

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

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

Foreign Keys

Constraint Columns References Delete/update action
Company CompanyID pears.Company (companyid) ON DELETE CASCADE
Vacancy VacancyID pears.vacancy (vacancyid) ON DELETE CASCADE
StoredSelection AgencyStoredSelectionID pears.storedselection (storedselectionid)

Referenced By

Indexes

Triggers

Name Timing Event
CascadeRuleinsert before insert order 1

Original 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