pears.CascadeRule

Settings determining when Shifts and Vacancies are cascaded to Secondary Agencies.

• CascadeRuleID

• No incoming foreign keys found.

• No indexes found.

-- 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