Table of Contents



pears.MasterRosterChangeLog

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

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

Foreign Keys

Constraint Columns References Delete/update action
MasterRosterShift MasterRosterShiftID pears.MasterRosterShift (MasterRosterShiftID) ON DELETE CASCADE
MasterRoster MasterRosterID pears.MasterRoster (MasterRosterID) ON DELETE CASCADE
staff StaffID pears.staff (staffid) ON DELETE SET NULL

Referenced By

Table Constraint Columns Referenced columns
pears.MasterRosterShift MasterRosterChangeLog MasterRosterChangeLogID MasterRosterChangeLogID

Indexes

Triggers

Name Timing Event
MasterRosterChangeLog_Insert before insert order 1

Original 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