pears.TempShiftProgress

Progress of candidates put forward for TempShiftPlan.

• TempShiftProgressID

• No indexes found.

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."TempShiftProgress"
-- Table comment: Progress of candidates put forward for TempShiftPlan.
-- Statement count: 9
 
CREATE TABLE "pears"."TempShiftProgress" (
    "TempShiftProgressID"            CHAR(20) NOT NULL
   ,"TempShiftPlanID"                CHAR(20) NOT NULL
   ,"Status"                         CHAR(1) NOT NULL
   ,"Note"                           long VARCHAR NULL
   ,"RejectionReason"                CHAR(100) NULL
   ,"StaffID"                        CHAR(20) NOT NULL
   ,"PersonID"                       CHAR(20) NOT NULL
   ,"WhenEntered"                    TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP
   ,PRIMARY KEY ("TempShiftProgressID" ASC) 
)
GO
 
 
COMMENT ON TABLE "pears"."TempShiftProgress" IS 
	'Progress of candidates put forward for TempShiftPlan.'
GO
 
 
ALTER TABLE "pears"."TempShiftProgress"
    ADD NOT NULL FOREIGN KEY "TempShiftPlan" ("TempShiftPlanID" ASC)
    REFERENCES "pears"."TempShiftPlan" ("TempShiftPlanID")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."TempShiftProgress"
    ADD NOT NULL FOREIGN KEY "Staff" ("StaffID" ASC)
    REFERENCES "pears"."staff" ("staffid")
GO
 
 
ALTER TABLE "pears"."TempShiftProgress"
    ADD NOT NULL FOREIGN KEY "Person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
    ON DELETE CASCADE
GO
 
 
CREATE TRIGGER "TempShiftProgressInsert" after INSERT ORDER 1 ON
"pears"."TempShiftProgress"
REFERENCING NEW AS "new_prog"
FOR each ROW
BEGIN
  INSERT INTO "TempShiftProgressHistory"( "TempShiftProgressHistoryID","TempShiftProgressID","status","historydate","historytime","staffid","whenentered" ) VALUES
    ( "uniquekey"("new_prog"."TempShiftProgressID"),"new_prog"."TempShiftProgressID","new_prog"."status",CURRENT DATE,CURRENT TIME,"new_prog"."staffid","new_prog"."whenentered" ) 
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempShiftProgress"."TempShiftProgressInsert" IS 
{CREATE TRIGGER TempShiftProgressInsert 
 after INSERT ORDER 1 ON
pears.TempShiftProgress
REFERENCING NEW AS new_prog
FOR each ROW
BEGIN
  INSERT INTO TempShiftProgressHistory( TempShiftProgressHistoryID,TempShiftProgressID,STATUS,historydate,historytime,staffid,whenentered) 
 VALUES( uniquekey(new_prog.TempShiftProgressID),new_prog.TempShiftProgressID,new_prog.status,CURRENT DATE,CURRENT TIME,new_prog.staffid,new_prog.whenentered) 
END
}
GO
 
 
CREATE TRIGGER "TempShiftProgressUpdate" after UPDATE OF "status"
ORDER 1 ON "pears"."TempShiftProgress"
REFERENCING NEW AS "new_prog"
FOR each ROW
BEGIN
  INSERT INTO "TempShiftProgressHistory"( "TempShiftProgressHistoryID","TempShiftProgressID","status","historydate","historytime","staffid","whenentered" ) VALUES
    ( "uniquekey"("new_prog"."TempShiftProgressID"),"new_prog"."TempShiftProgressID","new_prog"."status",CURRENT DATE,CURRENT TIME,"new_prog"."staffid","new_prog"."whenentered" ) 
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempShiftProgress"."TempShiftProgressUpdate" IS 
{CREATE TRIGGER TempShiftProgressUpdate 
 after UPDATE OF STATUS
ORDER 1 ON pears.TempShiftProgress
REFERENCING NEW AS new_prog
FOR each ROW
BEGIN
  INSERT INTO TempShiftProgressHistory( TempShiftProgressHistoryID,TempShiftProgressID,STATUS,historydate,historytime,staffid,whenentered) 
 VALUES( uniquekey(new_prog.TempShiftProgressID),new_prog.TempShiftProgressID,new_prog.status,CURRENT DATE,CURRENT TIME,new_prog.staffid,new_prog.whenentered) 
END
}
GO