====== pears.search ======
<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 =====
Details of Candidates registration for Temp or Perm work with departments.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **searchid** | char(20) | NOT NULL |  |  |
| temp | smallint | NULL |  |  |
| permanent | smallint | NULL |  |  |
| personid | char(20) | NOT NULL |  |  |
| departmentid | char(2) | NOT NULL |  |  |

===== Primary Key =====
  * searchid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| person | personid | [[database:tables:pears_person|pears.Person (personid)]] | NOT NULL; ON DELETE CASCADE |
| department | departmentid | [[database:tables:pears_department|pears.Department (departmentid)]] | NOT NULL; ON DELETE CASCADE |

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

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

===== Triggers =====
^ Name ^ Timing ^ Event ^
| searchauditinsdel | before | insert,delete,update order 1 |
| PersonKeyWordRefresh | after | insert,delete order 20 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."search"
-- Table comment: Details of Candidates registration for Temp or Perm work with departments.
-- Statement count: 8

CREATE TABLE "pears"."search" (
    "searchid"                       char(20) NOT NULL
   ,"temp"                           smallint NULL
   ,"permanent"                      smallint NULL
   ,"personid"                       char(20) NOT NULL
   ,"departmentid"                   char(2) NOT NULL
   ,PRIMARY KEY ("searchid" ASC) 
   ,CONSTRAINT "NoBlankRegistrations" check("isnull"("temp",0) <> 0 or "isnull"("permanent",0) <> 0)
)
go


COMMENT ON TABLE "pears"."search" IS 
	'Details of Candidates registration for Temp or Perm work with departments.'
go


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


ALTER TABLE "pears"."search"
    ADD NOT NULL FOREIGN KEY "department" ("departmentid" ASC)
    REFERENCES "pears"."Department" ("departmentid")
    ON DELETE CASCADE
go


create trigger "searchauditinsdel" before insert,delete,update order 1 on
"pears"."search"
referencing old as "old_pa" new as "new_pa"
for each row
when(exists(select * from "AuditItems" where "AreaName" = 'Person' and "AuditFlag" = 1 and "audititemid" = 'XXMANSB00000000051'))
begin
  declare "Dept" char(30);
  if inserting then
    select "name" into "dept" from "department" where "departmentid" = "new_pa"."departmentid";
    call "AuditLog"('PERSON',"new_pa"."personid","string"('Registration added. - ',"dept"),'','Temp ' || "new_pa"."temp" || ' Perm ' || "new_pa"."permanent")
  end if;
  if deleting then
    select "name" into "dept" from "department" where "departmentid" = "old_pa"."departmentid";
    call "AuditLog"('PERSON',"old_pa"."personid","string"('Registration deleted. - ',"dept"),'Temp ' || "old_pa"."temp" || ' Perm ' || "old_pa"."permanent",'')
  end if;
  if updating then
    select "name" into "dept" from "department" where "departmentid" = "old_pa"."departmentid";
    call "AuditLog"('PERSON',"old_pa"."personid","string"('Registration changed. - ',"dept"),'Temp ' || "old_pa"."temp" || ' Perm ' || "old_pa"."permanent",'Temp ' || "new_pa"."temp" || ' Perm ' || "new_pa"."permanent")
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."search"."searchauditinsdel" IS 
{create trigger searchauditinsdel 
 before insert,delete, update order 1 on
pears.search
referencing old as old_pa new as new_pa
for each row
when(exists(select * from AuditItems where AreaName = 'Person' and AuditFlag = 1 and audititemid = 'XXMANSB00000000051'))
begin
  declare Dept char(30);
  if inserting then
    select name into dept from department where departmentid = new_pa.departmentid;
    call AuditLog('PERSON',new_pa.personid,string('Registration added. - ',dept),'','Temp '||new_pa.temp || ' Perm ' || new_pa.permanent)
  end if;
  if deleting then
    select name into dept from department where departmentid = old_pa.departmentid;
    call AuditLog('PERSON',old_pa.personid,string('Registration deleted. - ',dept),'Temp '||old_pa.temp || ' Perm ' || old_pa.permanent,'')
  end if;
  if updating then
    select name into dept from department where departmentid = old_pa.departmentid;
    call AuditLog('PERSON',old_pa.personid,string('Registration changed. - ',dept),'Temp '||old_pa.temp || ' Perm ' || old_pa.permanent,'Temp '||new_pa.temp || ' Perm ' || new_pa.permanent)
  end if
end
}
go


create trigger "PersonKeyWordRefresh" after insert,delete order 20 on
"pears"."Search"
referencing old as "old_row" new as "new_row"
for each row
begin
  update "PersonKeyWords" set "RefreshRequired" = 1 where "PersonKeyWords"."PersonID" = "isnull"("new_row"."PersonID","old_row"."PersonID")
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."search"."PersonKeyWordRefresh" IS 
{create trigger PersonKeyWordRefresh 
 AFTER INSERT, DELETE ORDER 20 ON
 pears.Search
 REFERENCING OLD AS old_row NEW AS new_row 
FOR EACH ROW 
BEGIN 
    update PersonKeyWords set RefreshRequired = 1 where PersonKeyWords.PersonID = isnull(new_row.PersonID,old_row.PersonID);  
END
}
go
</code>