pears.MasterRosterChangeLog

Record of changes to a Master Roster.

• MasterRosterChangeLogID

• No indexes found.

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