pears.search

Details of Candidates registration for Temp or Perm work with departments.

• searchid

• No incoming foreign keys found.

• No indexes found.

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