====== pears.TempShiftProgress ======
<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 =====
Progress of candidates put forward for TempShiftPlan.

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

===== Primary Key =====
  * TempShiftProgressID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| TempShiftPlan | TempShiftPlanID | [[database:tables:pears_tempshiftplan|pears.TempShiftPlan (TempShiftPlanID)]] | NOT NULL; ON DELETE CASCADE |
| Staff | StaffID | [[database:tables:pears_staff|pears.staff (staffid)]] | NOT NULL;  |
| Person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] | NOT NULL; ON DELETE CASCADE |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_tempshiftprogresshistory|pears.TempShiftProgressHistory]] | TempShiftProgress | TempShiftProgressID | TempShiftProgressID |

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

===== Triggers =====
^ Name ^ Timing ^ Event ^
| TempShiftProgressInsert | after | insert order 1 |
| TempShiftProgressUpdate | after | update of "status" order 1 |

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