====== pears.TempPoolMember ======
<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 candidates and TempDesks.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **PersonID** | char(20) | NOT NULL |  |  |
| **TempDeskID** | char(20) | NOT NULL |  |  |

===== Primary Key =====
  * PersonID, TempDeskID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| Person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] | NOT NULL; ON DELETE CASCADE |
| TempDesk | TempDeskID | [[database:tables:pears_tempdesk|pears.tempdesk (tempdeskid)]] | NOT NULL; ON DELETE CASCADE |

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

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

===== Triggers =====
^ Name ^ Timing ^ Event ^
| TempPoolMemberDeleteAudit | before | delete order 2 |
| TempPoolMemberAddAudit | after | insert order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."TempPoolMember"
-- Table comment: Link between candidates and TempDesks.
-- Statement count: 8

CREATE TABLE "pears"."TempPoolMember" (
    "PersonID"                       char(20) NOT NULL
   ,"TempDeskID"                     char(20) NOT NULL
   ,PRIMARY KEY ("PersonID" ASC,"TempDeskID" ASC) 
)
go


COMMENT ON TABLE "pears"."TempPoolMember" IS 
	'Link between candidates and TempDesks.'
go


ALTER TABLE "pears"."TempPoolMember"
    ADD NOT NULL FOREIGN KEY "Person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."TempPoolMember"
    ADD NOT NULL FOREIGN KEY "TempDesk" ("TempDeskID" ASC)
    REFERENCES "pears"."tempdesk" ("tempdeskid")
    ON DELETE CASCADE
go


create trigger "TempPoolMemberDeleteAudit" before delete order 2 on
"pears"."TempPoolMember"
referencing old as "old_name"
for each row
begin
  declare "DeskName" char(50);
  select "name" into "DeskName" from "TempDesk" where "tempdeskid" = "old_name"."tempdeskid";
  call "AuditLog"('PERSON',"old_name"."personid","string"('Desk ',"DeskName",' Removed '),'Removed','')
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempPoolMember"."TempPoolMemberDeleteAudit" IS 
{create trigger TempPoolMemberDeleteAudit 
 before delete order 2 on
pears.TempPoolMember
referencing old as old_name
for each row
begin
  declare DeskName char(50);
  select name into DeskName from TempDesk where tempdeskid = old_name.tempdeskid;
  call AuditLog('PERSON',old_name.personid,string('Desk ',DeskName,' Removed '),'Removed','')
end
}
go


create trigger "TempPoolMemberAddAudit" after insert order 1 on
"pears"."TempPoolMember"
referencing new as "new_name"
for each row
begin
  declare "DeskName" char(50);
  select "name" into "DeskName" from "TempDesk" where "tempdeskid" = "new_name"."tempdeskid";
  call "AuditLog"('PERSON',"new_name"."personid","string"('Desk ',"DeskName",' Added '),'Added','')
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."TempPoolMember"."TempPoolMemberAddAudit" IS 
{create trigger TempPoolMemberAddAudit 
 after insert order 1 on
pears.TempPoolMember
referencing new as new_name
for each row
begin
  declare DeskName char(50);
  select name into DeskName from TempDesk where tempdeskid = new_name.tempdeskid;
  call AuditLog('PERSON',new_name.personid,string('Desk ',DeskName,' Added '),'Added','')
end
}
go
</code>