pears.staff [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.staff ======
<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 =====
User records.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **staffid** | char(20) | NOT NULL |  |  |
| name | char(60) | NULL |  |  |
| defaultdepartid | char(2) | NOT NULL |  |  |
| tempdeskid | char(20) | NULL |  |  |
| shortid | char(2) | NOT NULL |  |  |
| userid | char(25) | NULL |  |  |
| divisionid | char(20) | NULL |  |  |
| title | char(40) | NULL |  |  |
| agencyid | char(20) | NULL |  |  |
| canmailmerge | smallint | NULL | 0 |  |
| cancreatereports | smallint | NULL | 0 |  |
| reportview | smallint | NULL | 0 |  |
| reportprint | smallint | NULL | 0 |  |
| reportexport | smallint | NULL | 0 |  |
| access1 | smallint | NULL | 0 |  |
| access2 | smallint | NULL | 0 |  |
| access3 | smallint | NULL | 0 |  |
| access4 | smallint | NULL | 0 |  |
| access5 | smallint | NULL | 0 |  |
| password | char(250) | NULL |  |  |
| canedittemplates | smallint | NULL | 0 |  |
| candragmerge | smallint | NULL | 0 |  |
| departmentmaint | smallint | NULL | 0 |  |
| empquestmaint | smallint | NULL | 0 |  |
| wpletters | smallint | NULL | 0 |  |
| wpcvs | smallint | NULL | 0 |  |
| defunct | smallint | NULL | 0 |  |
| EMail | char(100) | NULL |  |  |
| manager | smallint | NULL | 0 |  |
| tempsaccess | smallint | NULL | 0 |  |
| Keyname | char(30) | NULL |  |  |
| AnalysisCode | char(20) | NULL |  |  |
| rateschememaint | smallint | NULL | 0 |  |
| template | smallint | NULL | 0 |  |
| divisionaccess | smallint | NULL | 0 | 0=all 1=own 2=selected |
| ChangeTracker | integer | NULL |  |  |
| WPKMainFormID | char(20) | NULL |  | Used instead of MAIN as the template for the mdi menu and button bar |
| WPKStartupForm | char(100) | NULL |  | A child form with switches to load on startup, comma separated e.g. TEMPDESK,x |
| ComboVisibility | char(25) | NULL |  |  |
| inboxlimit | smallint | NULL | 0 |  |
| inboxrefreshrate | smallint | NULL | 0 |  |
| monitorandform | long varchar | NULL |  |  |
| SMTPSend | smallint | NULL | 0 | 1=yes 0=no i.e. use the old default email settings |
| SMTPUID | char(50) | NULL |  |  |
| SMTPPWD | char(50) | NULL |  |  |
| IMAPFetch | smallint | NULL | 0 | +ve value=fetch frequency in minutes, any -ve value=manual fetch, 0=no i.e. use the old default email settings |
| IMAPUID | char(50) | NULL |  |  |
| IMAPPWD | char(50) | NULL |  |  |
| passwordvaliduntil | date | NULL |  |  |
| logins | integer | NULL |  |  |
| ExtensionNumber | char(50) | NULL |  |  |
| DoNotDisturb | tinyint | NULL |  |  |
| LeaveDate | date | NULL |  |  |
| TSQueryCode | char(5) | NULL |  |  |
| OpenInDefaultDivision | smallint | NULL | 0 |  |
| menupreference | smallint | NULL | 0 |  |
| formlimit | smallint | NULL | 0 |  |
| WhenCreated | timestamp | NULL | current timestamp |  |
| OauthAccessToken | long varchar | NULL |  |  |
| OauthRefreshToken | long varchar | NULL |  |  |
| IQXNetEmailDetailsID | char(20) | NULL |  |  |
| ThemeFile | char(40) | NULL |  |  |
| RelaxedRowHeight | tinyint | NULL |  |  |
| ThemeFormColours | tinyint | NULL | 0 |  |
| ThemeSystemBorder | tinyint | NULL | 0 |  |
| mobile | char(20) | NULL |  |  |
| directdial | char(20) | NULL |  |  |
| IsSystem | tinyint | NULL | 0 |  |
| ShiftButtonDefaults | integer | NULL |  |  |
| dashboardRefreshInterval | smallint | NULL | 0 | Seconds |
| homepageRefreshInterval | smallint | NULL | 0 | Seconds |
| tempdeskaccess | smallint | NULL | 0 | 0=all 1=own 2=selected |
| SqlWindowFontSize | integer | NULL |  |  |

===== Primary Key =====
  * staffid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| department | defaultdepartid | [[database:tables:pears_department|pears.Department (departmentid)]] | NOT NULL;  |
| division | divisionid | [[database:tables:pears_division|pears.Division (divisionid)]] | ON DELETE SET NULL |
| agencydetails | agencyid | [[database:tables:pears_agencydetails|pears.agencydetails (agencyid)]] | ON DELETE SET NULL |
| tempdesk | tempdeskid | [[database:tables:pears_tempdesk|pears.tempdesk (tempdeskid)]] | ON DELETE SET NULL |
| IQXNetEmailDetails | IQXNetEmailDetailsID | [[database:tables:pears_iqxnetemaildetails|pears.IQXNetEmailDetails (IQXNetEmailDetailsID)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_broadbeanjobboardallocation|pears.BroadbeanJobBoardAllocation]] | staff | StaffID | staffid |
| [[database:tables:pears_broadbeanuser|pears.BroadbeanUser]] | staff | StaffID | staffid |
| [[database:tables:pears_ciscard|pears.CISCard]] | CreatedByStaff | CreatedBy | staffid |
| [[database:tables:pears_ciscard|pears.CISCard]] | DeletedByStaff | DeletedBy | staffid |
| [[database:tables:pears_collection|pears.Collection]] | staff | staffid | staffid |
| [[database:tables:pears_collectionchat|pears.CollectionChat]] | Staff | Staffid | staffid |
| [[database:tables:pears_collectionchatread|pears.CollectionChatRead]] | Staff | Staffid | staffid |
| [[database:tables:pears_collectionusers|pears.CollectionUsers]] | Staff | Staffid | staffid |
| [[database:tables:pears_company|pears.Company]] | staff | staffid | staffid |
| [[database:tables:pears_contactevent|pears.contactevent]] | staff | staffid | staffid |
| [[database:tables:pears_dashboarddisplayedobject|pears.DashboardDisplayedObject]] | Staff | StaffID | staffid |
| [[database:tables:pears_dashboardobject|pears.DashboardObject]] | Staff | StaffID | staffid |
| [[database:tables:pears_deptmaintenance|pears.deptmaintenance]] | staff | staffid | staffid |
| [[database:tables:pears_diary|pears.diary]] | staff | staffid | staffid |
| [[database:tables:pears_diarytasklist|pears.DiaryTaskList]] | staff | StaffID | staffid |
| [[database:tables:pears_divisionaccess|pears.DivisionAccess]] | staff | staffid | staffid |
| [[database:tables:pears_docpackvalidation|pears.DocPackValidation]] | Staff | StaffID | staffid |
| [[database:tables:pears_ebtimesheet|pears.EBTimeSheet]] | Staff | StaffID | staffid |
| [[database:tables:pears_emailtosend|pears.EmailToSend]] | staff | WhoEntered | staffid |
| [[database:tables:pears_favourites|pears.Favourites]] | Staff | Staffid | staffid |
| [[database:tables:pears_iqxnetbulletin|pears.IQXNetBulletin]] | Staff | WhoCreated | staffid |
| [[database:tables:pears_iqxnetmessagecompanyrecipient|pears.IQXNetMessageCompanyRecipient]] | Staff | StaffID | staffid |
| [[database:tables:pears_iqxnetmessagerecipient|pears.IQXNetMessageRecipient]] | Staff | StaffID | staffid |
| [[database:tables:pears_iqxnetsettings|pears.IQXNetSettings]] | Staff | DefaultStaffID | staffid |
| [[database:tables:pears_iqxnetuser|pears.IQXNetUser]] | Staff | StaffID | staffid |
| [[database:tables:pears_iqxnetuserclass|pears.IQXNetUserClass]] | Staff | DefaultStaffID | staffid |
| [[database:tables:pears_mailerselection|pears.mailerselection]] | staff | StaffID | staffid |
| [[database:tables:pears_mailmerge|pears.mailmerge]] | staff | staffid | staffid |
| [[database:tables:pears_masterrosterchangelog|pears.MasterRosterChangeLog]] | staff | StaffID | staffid |
| [[database:tables:pears_mimemessage|pears.MIMEMessage]] | staff | staffid | staffid |
| [[database:tables:pears_notificationdeeplink|pears.NotificationDeepLink]] | Staff | WhoEntered | staffid |
| [[database:tables:pears_offlimits|pears.OffLimits]] | Staff | StaffID | staffid |
| [[database:tables:pears_person|pears.Person]] | staff | staffid | staffid |
| [[database:tables:pears_personincompatibility|pears.PersonIncompatibility]] | staff | StaffID | staffid |
| [[database:tables:pears_personinterest|pears.PersonInterest]] | PersonInterest2 | StaffID | staffid |
| [[database:tables:pears_personstafflink|pears.PersonStaffLink]] | Staff | StaffID | staffid |
| [[database:tables:pears_placement|pears.Placement]] | staff | staffid | staffid |
| [[database:tables:pears_placementattribution|pears.placementattribution]] | staff | staffid | staffid |
| [[database:tables:pears_popupescalation|pears.PopupEscalation]] | OrigStaffid | OrigStaffid | staffid |
| [[database:tables:pears_popupescalation|pears.PopupEscalation]] | TargetStaffid | TargetStaffid | staffid |
| [[database:tables:pears_progress|pears.progress]] | staff | staffid | staffid |
| [[database:tables:pears_progresshistory|pears.ProgressHistory]] | Staff | StaffID | staffid |
| [[database:tables:pears_referencerequest|pears.ReferenceRequest]] | staff | StaffID | staffid |
| [[database:tables:pears_staffexttempdeskaccess|pears.StaffExtTempdeskAccess]] | staff | StaffID | staffid |
| [[database:tables:pears_staffnoteread|pears.StaffNoteRead]] | Staff | Staffid | staffid |
| [[database:tables:pears_staffpassword|pears.staffpassword]] | staff | staffid | staffid |
| [[database:tables:pears_staffsynety|pears.StaffSynety]] | Staff | StaffID | staffid |
| [[database:tables:pears_staffteam|pears.StaffTeam]] | Staff | OwnerID | staffid |
| [[database:tables:pears_staffteammember|pears.StaffTeamMember]] | Staff | StaffID | staffid |
| [[database:tables:pears_statushistory|pears.StatusHistory]] | Staff | StaffID | staffid |
| [[database:tables:pears_storedsearch|pears.storedsearch]] | staff | staffid | staffid |
| [[database:tables:pears_storedselection|pears.storedselection]] | staff | staffid | staffid |
| [[database:tables:pears_storedselectionstaff|pears.storedselectionstaff]] | staff | staffid | staffid |
| [[database:tables:pears_tempdeskaccess|pears.TempdeskAccess]] | staff | staffid | staffid |
| [[database:tables:pears_templatestorestaff|pears.templatestorestaff]] | staff | staffid | staffid |
| [[database:tables:pears_tempprovtimesheethistory|pears.TempProvTimeSheetHistory]] | staff | StaffID | staffid |
| [[database:tables:pears_tempratemarginaudit|pears.TempRateMarginAudit]] | staff | StaffID | staffid |
| [[database:tables:pears_tempshift|pears.TempShift]] | Staff | StaffID | staffid |
| [[database:tables:pears_tempshift|pears.TempShift]] | WhoCancelled | WhoCancelled | staffid |
| [[database:tables:pears_tempshiftplan|pears.TempShiftPlan]] | Staff | StaffID | staffid |
| [[database:tables:pears_tempshiftprogress|pears.TempShiftProgress]] | Staff | StaffID | staffid |
| [[database:tables:pears_tempshiftprogresshistory|pears.TempShiftProgressHistory]] | Staff | StaffID | staffid |
| [[database:tables:pears_tempsubsidy|pears.TempSubsidy]] | Staff | StaffID | staffid |
| [[database:tables:pears_temptimesheet|pears.TempTimeSheet]] | Staff | StaffID | staffid |
| [[database:tables:pears_trengo|pears.Trengo]] | Staff | StaffID | staffid |
| [[database:tables:pears_trengosent|pears.TrengoSent]] | Staff | WhoSent | staffid |
| [[database:tables:pears_vacancy|pears.vacancy]] | staff | staffid | staffid |
| [[database:tables:pears_vacancy_team|pears.vacancy_team]] | staff | staffid | staffid |
| [[database:tables:pears_vacancyoverrideratescheme|pears.VacancyOverrideRateScheme]] | Staff | StaffID | staffid |
| [[database:tables:pears_withholds|pears.WithHolds]] | staff | StaffID | staffid |
| [[database:tables:pears_wpkcustomgridcolumnprivateselection|pears.WPKCustomGridColumnPrivateSelection]] | Staff | StaffID | staffid |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| StaffName | Index | name |  |
| staff_userid | Unique index | userid |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| WPK_staff_STAFF | after | insert,delete,update order 1 |
| StaffAudit | before | update of "divisionaccess", "defunct","divisionid","tempdeskid","agencyid", "Defaultdepartid" order 1 |
| StaffFuzeEmail | before | update of "email" order 2 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."staff"
-- Table comment: User records.
-- Statement count: 23

CREATE TABLE "pears"."staff" (
    "staffid"                        char(20) NOT NULL
   ,"name"                           char(60) NULL
   ,"defaultdepartid"                char(2) NOT NULL
   ,"tempdeskid"                     char(20) NULL
   ,"shortid"                        char(2) NOT NULL
   ,"userid"                         char(25) NULL
   ,"divisionid"                     char(20) NULL
   ,"title"                          char(40) NULL
   ,"agencyid"                       char(20) NULL
   ,"canmailmerge"                   smallint NULL DEFAULT 0
   ,"cancreatereports"               smallint NULL DEFAULT 0
   ,"reportview"                     smallint NULL DEFAULT 0
   ,"reportprint"                    smallint NULL DEFAULT 0
   ,"reportexport"                   smallint NULL DEFAULT 0
   ,"access1"                        smallint NULL DEFAULT 0
   ,"access2"                        smallint NULL DEFAULT 0
   ,"access3"                        smallint NULL DEFAULT 0
   ,"access4"                        smallint NULL DEFAULT 0
   ,"access5"                        smallint NULL DEFAULT 0
   ,"password"                       char(250) NULL
   ,"canedittemplates"               smallint NULL DEFAULT 0
   ,"candragmerge"                   smallint NULL DEFAULT 0
   ,"departmentmaint"                smallint NULL DEFAULT 0
   ,"empquestmaint"                  smallint NULL DEFAULT 0
   ,"wpletters"                      smallint NULL DEFAULT 0
   ,"wpcvs"                          smallint NULL DEFAULT 0
   ,"defunct"                        smallint NULL DEFAULT 0
   ,"EMail"                          char(100) NULL
   ,"manager"                        smallint NULL DEFAULT 0
   ,"tempsaccess"                    smallint NULL DEFAULT 0
   ,"Keyname"                        char(30) NULL
   ,"AnalysisCode"                   char(20) NULL
   ,"rateschememaint"                smallint NULL DEFAULT 0
   ,"template"                       smallint NULL DEFAULT 0
   ,"divisionaccess"                 smallint NULL DEFAULT 0
   ,"ChangeTracker"                  integer NULL
   ,"WPKMainFormID"                  char(20) NULL
   ,"WPKStartupForm"                 char(100) NULL
   ,"ComboVisibility"                char(25) NULL
   ,"inboxlimit"                     smallint NULL DEFAULT 0
   ,"inboxrefreshrate"               smallint NULL DEFAULT 0
   ,"monitorandform"                 long varchar NULL
   ,"SMTPSend"                       smallint NULL DEFAULT 0
   ,"SMTPUID"                        char(50) NULL
   ,"SMTPPWD"                        char(50) NULL
   ,"IMAPFetch"                      smallint NULL DEFAULT 0
   ,"IMAPUID"                        char(50) NULL
   ,"IMAPPWD"                        char(50) NULL
   ,"passwordvaliduntil"             date NULL
   ,"logins"                         integer NULL
   ,"ExtensionNumber"                char(50) NULL
   ,"DoNotDisturb"                   tinyint NULL
   ,"LeaveDate"                      date NULL
   ,"TSQueryCode"                    char(5) NULL
   ,"OpenInDefaultDivision"          smallint NULL DEFAULT 0
   ,"menupreference"                 smallint NULL DEFAULT 0
   ,"formlimit"                      smallint NULL DEFAULT 0
   ,"WhenCreated"                    timestamp NULL DEFAULT current timestamp
   ,"OauthAccessToken"               long varchar NULL
   ,"OauthRefreshToken"              long varchar NULL
   ,"IQXNetEmailDetailsID"           char(20) NULL
   ,"ThemeFile"                      char(40) NULL
   ,"RelaxedRowHeight"               tinyint NULL
   ,"ThemeFormColours"               tinyint NULL DEFAULT 0
   ,"ThemeSystemBorder"              tinyint NULL DEFAULT 0
   ,"mobile"                         char(20) NULL
   ,"directdial"                     char(20) NULL
   ,"IsSystem"                       tinyint NULL DEFAULT 0
   ,"ShiftButtonDefaults"            integer NULL
   ,"dashboardRefreshInterval"       smallint NULL DEFAULT 0
   ,"homepageRefreshInterval"        smallint NULL DEFAULT 0
   ,"tempdeskaccess"                 smallint NULL DEFAULT 0
   ,"SqlWindowFontSize"              integer NULL
   ,PRIMARY KEY ("staffid" ASC) 
)
go


COMMENT ON COLUMN "pears"."staff"."divisionaccess" IS 
	'0=all 1=own 2=selected'
go


COMMENT ON COLUMN "pears"."staff"."WPKMainFormID" IS 
	'Used instead of MAIN as the template for the mdi menu and button bar'
go


COMMENT ON COLUMN "pears"."staff"."WPKStartupForm" IS 
	'A child form with switches to load on startup, comma separated e.g. TEMPDESK,x'
go


COMMENT ON COLUMN "pears"."staff"."SMTPSend" IS 
	'1=yes 0=no i.e. use the old default email settings'
go


COMMENT ON COLUMN "pears"."staff"."IMAPFetch" IS 
	'+ve value=fetch frequency in minutes, any -ve value=manual fetch, 0=no i.e. use the old default email settings'
go


COMMENT ON COLUMN "pears"."staff"."dashboardRefreshInterval" IS 
	'Seconds'
go


COMMENT ON COLUMN "pears"."staff"."homepageRefreshInterval" IS 
	'Seconds'
go


COMMENT ON COLUMN "pears"."staff"."tempdeskaccess" IS 
	'0=all 1=own 2=selected'
go


COMMENT ON TABLE "pears"."staff" IS 
	'User records.'
go


ALTER TABLE "pears"."staff"
    ADD NOT NULL FOREIGN KEY "department" ("defaultdepartid" ASC)
    REFERENCES "pears"."Department" ("departmentid")
go


ALTER TABLE "pears"."staff"
    ADD FOREIGN KEY "division" ("divisionid" ASC)
    REFERENCES "pears"."Division" ("divisionid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."staff"
    ADD FOREIGN KEY "agencydetails" ("agencyid" ASC)
    REFERENCES "pears"."agencydetails" ("agencyid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."staff"
    ADD FOREIGN KEY "tempdesk" ("tempdeskid" ASC)
    REFERENCES "pears"."tempdesk" ("tempdeskid")
    ON DELETE SET NULL
go


ALTER TABLE "pears"."staff"
    ADD FOREIGN KEY "IQXNetEmailDetails" ("IQXNetEmailDetailsID" ASC)
    REFERENCES "pears"."IQXNetEmailDetails" ("IQXNetEmailDetailsID")
    ON DELETE SET NULL
go


CREATE INDEX "StaffName" ON "pears"."staff"
    ( "name" )
go


CREATE UNIQUE INDEX "staff_userid" ON "pears"."staff"
    ( "userid" )
go


create trigger "WPK_staff_STAFF" after insert,delete,update order 1 on
"pears"."staff"
for each statement
begin
  call "WPKTrackChange"('P','STAFF')
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."staff"."WPK_staff_STAFF" IS 
{create trigger WPK_staff_STAFF 
 after insert,delete,update order 1 on
staff
for each statement
begin
  call WPKTrackChange('P','STAFF')
end
}
go


create trigger "StaffAudit" before update of "divisionaccess",
"defunct","divisionid","tempdeskid","agencyid",
"Defaultdepartid" order 1 on "pears"."Staff"
referencing old as "old_staff" new as "new_staff"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'User' and "AuditFlag" = 1))
begin
  declare "AuditList" long varchar;
  declare "OldDescrip" char(10);
  declare "NewDescrip" char(10);
  declare "StaffName" char(25);
  select "string"(',',"list"("ItemName"),',') into "AuditList" from "AuditItems" where "AreaName" = 'User' and "AuditFlag" = 1;
  select "userid" into "StaffName" from "staff" where "staffid" = "new_staff"."staffid";
  -- Division Access
  if "locate"("AuditList",',Division Access,') > 0 and update("divisionaccess") then
    select case "old_staff"."divisionaccess" when 0 then 'All' when 1 then 'Own' when 2 then 'Selected' end into "OldDescrip";
    select case "new_staff"."divisionaccess" when 0 then 'All' when 1 then 'Own' when 2 then 'Selected' end into "NewDescrip";
    call "AuditLog"('USER',"old_staff"."staffid","string"('Division Access Updated - ',"StaffName"),"OldDescrip","NewDescrip")
  end if;
  if "locate"("AuditList",',Not in Use,') > 0 and update("defunct") then
    call "AuditLog"('USER',"old_staff"."staffid","string"('Not In Use updated - ',"StaffName"),"old_staff"."defunct","new_staff"."defunct")
  end if;
  if "locate"("AuditList",',Division,') > 0 and update("divisionid") then
    call "AuditLog"('USER',"old_staff"."staffid","string"('Division updated - ',"StaffName"),(select "name" from "division" as "d" where "d"."divisionid" = "old_staff"."divisionid"),(select "name" from "division" as "d" where "d"."divisionid" = "new_staff"."divisionid"))
  end if;
  if "locate"("AuditList",',Default Department,') > 0 and update("Defaultdepartid") then
    call "AuditLog"('USER',"old_staff"."staffid","string"('Default Department updated - ',"StaffName"),"old_staff"."Defaultdepartid","new_staff"."Defaultdepartid")
  end if;
  if "locate"("AuditList",',Branch,') > 0 and update("agencyid") then
    call "AuditLog"('USER',"old_staff"."staffid","string"('Branch updated - ',"StaffName"),(select "name" from "agencydetails" as "a" where "a"."agencyid" = "old_staff"."agencyid"),(select "name" from "agencydetails" as "a" where "a"."agencyid" = "new_staff"."agencyid"))
  end if;
  if "locate"("AuditList",',Default Tempdesk,') > 0 and update("tempdeskid") then
    call "AuditLog"('USER',"old_staff"."staffid","string"('Default Tempdesk updated - ',"StaffName"),(select "name" from "tempdesk" as "t" where "t"."tempdeskid" = "old_staff"."tempdeskid"),(select "name" from "tempdesk" as "t" where "t"."tempdeskid" = "new_staff"."tempdeskid"))
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."staff"."StaffAudit" IS 
{create trigger StaffAudit 
 before update of divisionaccess, defunct,divisionid,tempdeskid,agencyid,Defaultdepartid
 order 1 on pears.Staff
referencing old as old_staff new as new_staff
for each row
when(exists(select* from AuditItems where AreaName = 'User' and AuditFlag = 1))
begin
  declare AuditList long varchar;
  declare OldDescrip char(10);
  declare NewDescrip char(10);
  declare StaffName char(25);
  select string(',',list(ItemName),',') into AuditList from AuditItems where AreaName = 'User' and AuditFlag = 1;
  select userid into StaffName from staff where staffid = new_staff.staffid;
  -- Division Access
  if locate(AuditList,',Division Access,') > 0 and update(divisionaccess) then
    select case old_staff.divisionaccess when 0 then 'All' when 1 then 'Own' when 2 then 'Selected' end into OldDescrip ;
    select case new_staff.divisionaccess when 0 then 'All' when 1 then 'Own' when 2 then 'Selected' end into NewDescrip ;
    call AuditLog('USER',old_staff.staffid,string('Division Access Updated - ',StaffName),OldDescrip,NewDescrip)
  end if;
  if locate(AuditList,',Not in Use,') > 0 and update(defunct) then
    call AuditLog('USER',old_staff.staffid,string('Not In Use updated - ',StaffName),old_staff.defunct,new_staff.defunct)
  end if;
  if locate(AuditList,',Division,') > 0 and update(divisionid) then
    call AuditLog('USER',old_staff.staffid,string('Division updated - ',StaffName),(select name from division d where d.divisionid =old_staff.divisionid),(select name from division d where d.divisionid =new_staff.divisionid))
  end if;
  if locate(AuditList,',Default Department,') > 0 and update(Defaultdepartid) then
    call AuditLog('USER',old_staff.staffid,string('Default Department updated - ',StaffName),old_staff.Defaultdepartid,new_staff.Defaultdepartid)
  end if;
  if locate(AuditList,',Branch,') > 0 and update(agencyid) then
    call AuditLog('USER',old_staff.staffid,string('Branch updated - ',StaffName),(select name from agencydetails a where a.agencyid =old_staff.agencyid),(select name from agencydetails a where a.agencyid =new_staff.agencyid))
  end if;
  if locate(AuditList,',Default Tempdesk,') > 0 and update(tempdeskid) then
    call AuditLog('USER',old_staff.staffid,string('Default Tempdesk updated - ',StaffName),(select name from tempdesk t where t.tempdeskid =old_staff.tempdeskid),(select name from tempdesk t where t.tempdeskid = new_staff.tempdeskid))
  end if;
end
}
go


create trigger "StaffFuzeEmail" before update of "email"
order 2 on "pears"."Staff"
referencing old as "old_staff"
for each row
begin
  delete from "FuzeUsers" where "staffid" = "old_staff"."staffid"
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."staff"."StaffFuzeEmail" IS 
{create trigger StaffFuzeEmail 
 before update of email
 order 2 on pears.Staff
referencing old as old_staff
for each row
begin
  delete from FuzeUsers where staffid = old_staff.staffid;
end
}
go
</code>