====== pears.WPKAssignedRole ======
<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 =====
Link between Staff and WPKRoles.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **WPKRoleID** | char(20) | NOT NULL |  |  |
| **OwnerID** | char(20) | NOT NULL |  |  |
| Expires | date | NULL |  |  |

===== Primary Key =====
  * WPKRoleID, OwnerID

===== Foreign Keys =====
  * No outgoing foreign keys found.

===== Referenced By =====
  * No incoming foreign keys found.

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| RoleOwner | Index | OwnerID |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| WPKTrackAssigRoleChange | after | insert,delete,update order 1 |
| WPKAssignedRoleInsertAudit | after | insert order 1 |
| WPKAssignedRoleDeleteAudit | after | delete order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."WPKAssignedRole"
-- Table comment: Link between Staff and WPKRoles.
-- Statement count: 9

CREATE TABLE "pears"."WPKAssignedRole" (
    "WPKRoleID"                      char(20) NOT NULL
   ,"OwnerID"                        char(20) NOT NULL
   ,"Expires"                        date NULL
   ,PRIMARY KEY ("WPKRoleID" ASC,"OwnerID" ASC) 
)
go


COMMENT ON TABLE "pears"."WPKAssignedRole" IS 
	'Link between Staff and WPKRoles.'
go


CREATE INDEX "RoleOwner" ON "pears"."WPKAssignedRole"
    ( "OwnerID" )
go


create trigger "WPKTrackAssigRoleChange" after insert,delete,update order 1 on
"pears"."WPKAssignedRole"
for each statement
begin
  call "WPKTrackChange"('R','*')
end
go


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


create trigger "WPKAssignedRoleInsertAudit" after insert order 1 on
"pears"."WPKAssignedRole"
referencing new as "new_role"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'User' and "AuditFlag" = 1))
begin
  declare "AuditList" long varchar;
  declare "RoleName" char(50);
  declare "StaffName" char(25);
  select "string"(',',"list"("ItemName"),',') into "AuditList" from "AuditItems" where "AreaName" = 'User' and "AuditFlag" = 1;
  if "locate"("AuditList",',Roles,') > 0 and exists(select * from "wpkrole" where "wpkroleid" = "new_role"."wpkroleid" and "rolegroup" <> 'USER') and "new_role"."wpkroleid" <> 'USERSCRNMOD' then
    select "name" into "RoleName" from "wpkrole" where "wpkroleid" = "new_role"."wpkroleid";
    select "userid" into "StaffName" from "staff" where "staffid" = "new_role"."ownerid";
    call "AuditLog"('USER','',"string"("StaffName",' Added ',"RoleName"),'Added','')
  end if;
  if "locate"("AuditList",',Layout Settings,') > 0 and(exists(select * from "wpkrole" where "wpkroleid" = "new_role"."wpkroleid" and "rolegroup" = 'USER') or "new_role"."wpkroleid" = 'USERSCRNMOD') then
    select "name" into "RoleName" from "wpkrole" where "wpkroleid" = "new_role"."wpkroleid";
    select "userid" into "StaffName" from "staff" where "staffid" = "new_role"."ownerid";
    call "AuditLog"('USER','',"string"("StaffName",' Added ',"RoleName"),'Added','')
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."WPKAssignedRole"."WPKAssignedRoleInsertAudit" IS 
{create trigger WPKAssignedRoleInsertAudit 
 after insert order 1 on
WPKAssignedRole
referencing new as new_role
for each row
when(exists(select * from AuditItems where AreaName = 'User' and AuditFlag = 1))
begin
  declare AuditList long varchar;
  declare RoleName char(50);
  declare StaffName char(25);
  select string(',',list(ItemName),',') into AuditList from AuditItems where AreaName = 'User' and AuditFlag = 1;
  if locate(AuditList,',Roles,') > 0 and exists(select * from wpkrole where wpkroleid = new_role.wpkroleid and rolegroup <> 'USER') and new_role.wpkroleid <> 'USERSCRNMOD' then
    select name into RoleName from wpkrole where wpkroleid = new_role.wpkroleid;
    select userid into StaffName from staff where staffid = new_role.ownerid;
    call AuditLog('USER','',string(StaffName,' Added ',RoleName),'Added','')
  end if;
  if locate(AuditList,',Layout Settings,') > 0 and (exists(select * from wpkrole where wpkroleid = new_role.wpkroleid and rolegroup = 'USER') or new_role.wpkroleid = 'USERSCRNMOD') then
    select name into RoleName from wpkrole where wpkroleid = new_role.wpkroleid;
    select userid into StaffName from staff where staffid = new_role.ownerid;
    call AuditLog('USER','',string(StaffName,' Added ',RoleName),'Added','')
  end if;
end
}
go


create trigger "WPKAssignedRoleDeleteAudit" after delete order 1 on
"pears"."WPKAssignedRole"
referencing old as "old_role"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'User' and "AuditFlag" = 1))
begin
  declare "AuditList" long varchar;
  declare "RoleName" char(50);
  declare "StaffName" char(25);
  select "string"(',',"list"("ItemName"),',') into "AuditList" from "AuditItems" where "AreaName" = 'User' and "AuditFlag" = 1;
  if "locate"("AuditList",',Roles,') > 0 and exists(select * from "wpkrole" where "wpkroleid" = "old_role"."wpkroleid" and "rolegroup" <> 'USER') and "old_role"."wpkroleid" <> 'USERSCRNMOD' then
    select "name" into "RoleName" from "wpkrole" where "wpkroleid" = "old_role"."wpkroleid";
    select "userid" into "StaffName" from "staff" where "staffid" = "old_role"."ownerid";
    call "AuditLog"('USER','',"string"("StaffName",' Removed ',"RoleName"),'','Removed')
  end if;
  if "locate"("AuditList",',Layout Settings,') > 0 and(exists(select * from "wpkrole" where "wpkroleid" = "old_role"."wpkroleid" and "rolegroup" = 'USER') or "old_role"."wpkroleid" = 'USERSCRNMOD') then
    select "name" into "RoleName" from "wpkrole" where "wpkroleid" = "old_role"."wpkroleid";
    select "userid" into "StaffName" from "staff" where "staffid" = "old_role"."ownerid";
    call "AuditLog"('USER','',"string"("StaffName",' Removed ',"RoleName"),'','Removed')
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."WPKAssignedRole"."WPKAssignedRoleDeleteAudit" IS 
{create trigger WPKAssignedRoleDeleteAudit 
 after delete order 1 on
WPKAssignedRole
referencing old as old_role
for each row
when(exists(select * from AuditItems where AreaName = 'User' and AuditFlag = 1))
begin
  declare AuditList long varchar;
  declare RoleName char(50);
  declare StaffName char(25);
  select string(',',list(ItemName),',') into AuditList from AuditItems where AreaName = 'User' and AuditFlag = 1;
  if locate(AuditList,',Roles,') > 0 and exists(select * from wpkrole where wpkroleid = old_role.wpkroleid and rolegroup <> 'USER') and old_role.wpkroleid <> 'USERSCRNMOD' then
    select name into RoleName from wpkrole where wpkroleid = old_role.wpkroleid;
    select userid into StaffName from staff where staffid = old_role.ownerid;
    call AuditLog('USER','',string(StaffName,' Removed ',RoleName),'','Removed')
  end if;
  if locate(AuditList,',Layout Settings,') > 0 and (exists(select * from wpkrole where wpkroleid = old_role.wpkroleid and rolegroup = 'USER') or old_role.wpkroleid = 'USERSCRNMOD') then
    select name into RoleName from wpkrole where wpkroleid = old_role.wpkroleid;
    select userid into StaffName from staff where staffid = old_role.ownerid;
    call AuditLog('USER','',string(StaffName,' Removed ',RoleName),'','Removed')
  end if;
end
}
go
</code>