====== pears.MasterRosterChangeLog ======
<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 =====
Record of changes to a Master Roster.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **MasterRosterChangeLogID** | char(20) | NOT NULL |  |  |
| MasterRosterShiftID | char(20) | NULL |  |  |
| MasterRosterID | char(20) | NULL |  |  |
| Action | char(30) | NOT NULL |  |  |
| Details | long varchar | NULL |  |  |
| Reason | char(30) | NULL |  |  |
| CanUndo | smallint | NOT NULL | 0 |  |
| StaffID | char(20) | NULL |  |  |
| WhenEntered | timestamp | NULL | current timestamp |  |

===== Primary Key =====
  * MasterRosterChangeLogID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| MasterRosterShift | MasterRosterShiftID | [[database:tables:pears_masterrostershift|pears.MasterRosterShift (MasterRosterShiftID)]] | ON DELETE CASCADE |
| MasterRoster | MasterRosterID | [[database:tables:pears_masterroster|pears.MasterRoster (MasterRosterID)]] | ON DELETE CASCADE |
| staff | StaffID | [[database:tables:pears_staff|pears.staff (staffid)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_masterrostershift|pears.MasterRosterShift]] | MasterRosterChangeLog | MasterRosterChangeLogID | MasterRosterChangeLogID |

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

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

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."MasterRosterChangeLog"
-- Table comment: Record of changes to a Master Roster.
-- Statement count: 7

CREATE TABLE "pears"."MasterRosterChangeLog" (
    "MasterRosterChangeLogID"        char(20) NOT NULL
   ,"MasterRosterShiftID"            char(20) NULL
   ,"MasterRosterID"                 char(20) NULL
   ,"Action"                         char(30) NOT NULL
   ,"Details"                        long varchar NULL
   ,"Reason"                         char(30) NULL
   ,"CanUndo"                        smallint NOT NULL DEFAULT 0
   ,"StaffID"                        char(20) NULL
   ,"WhenEntered"                    timestamp NULL DEFAULT current timestamp
   ,PRIMARY KEY ("MasterRosterChangeLogID" ASC) 
)
go


COMMENT ON TABLE "pears"."MasterRosterChangeLog" IS 
	'Record of changes to a Master Roster.'
go


ALTER TABLE "pears"."MasterRosterChangeLog"
    ADD FOREIGN KEY "MasterRosterShift" ("MasterRosterShiftID" ASC)
    REFERENCES "pears"."MasterRosterShift" ("MasterRosterShiftID")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."MasterRosterChangeLog"
    ADD FOREIGN KEY "MasterRoster" ("MasterRosterID" ASC)
    REFERENCES "pears"."MasterRoster" ("MasterRosterID")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."MasterRosterChangeLog"
    ADD FOREIGN KEY "staff" ("StaffID" ASC)
    REFERENCES "pears"."staff" ("staffid")
    ON DELETE SET NULL
go


create trigger "MasterRosterChangeLog_Insert" before insert order 1 on
"pears"."MasterRosterChangeLog"
referencing new as "new_log"
for each row
when("new_log"."staffid" is null)
begin
  set "new_log"."staffid" = "userstaffid"
exception
  when others then
    set "new_log"."staffid" = null
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."MasterRosterChangeLog"."MasterRosterChangeLog_Insert" IS 
{create trigger MasterRosterChangeLog_Insert 
 before insert order 1 on
pears.MasterRosterChangeLog
referencing new as new_log
for each row
when(new_log.staffid is null)
begin
  set new_log.staffid=userstaffid
exception
  when others then
    set new_log.staffid=null
end
}
go
</code>