pears.tagvalue

Values for user defined fields

• TagValueID

• No incoming foreign keys found.

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."tagvalue"
-- Table comment: Values for user defined fields
-- Statement count: 7
 
CREATE TABLE "pears"."tagvalue" (
    "taglocation"                    CHAR(3) NULL
   ,"tagid"                          CHAR(3) NULL
   ,"tagchoiceid"                    CHAR(4) NULL
   ,"id"                             CHAR(20) NOT NULL
   ,"value"                          DOUBLE NULL
   ,"textvalue"                      long VARCHAR NULL
   ,"TagValueID"                     BIGINT NOT NULL DEFAULT autoincrement
   ,PRIMARY KEY ("TagValueID" ASC) 
)
GO
 
 
COMMENT ON TABLE "pears"."tagvalue" IS 
	'Values for user defined fields'
GO
 
 
ALTER TABLE "pears"."tagvalue"
    ADD FOREIGN KEY "tag" ("taglocation" ASC,"tagid" ASC)
    REFERENCES "pears"."tag" ("taglocation","tagid")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."tagvalue"
    ADD FOREIGN KEY "tagchoice" ("taglocation" ASC,"tagid" ASC,"tagchoiceid" ASC)
    REFERENCES "pears"."tagchoice" ("taglocation","tagid","tagchoiceid")
    ON DELETE CASCADE
GO
 
 
CREATE INDEX "tagvalue_idetc" ON "pears"."tagvalue"
    ( "id","taglocation","tagid" )
GO
 
 
CREATE TRIGGER "TagValueKeyWords" after INSERT,DELETE,UPDATE ORDER 20 ON
"pears"."TagValue"
REFERENCING OLD AS "OldRow" NEW AS "NewRow"
FOR each ROW
BEGIN
  IF deleting THEN
    IF(SELECT "t"."IncludeInKeyWordSearch" FROM "Tag" AS "t" WHERE "t"."TagLocation" = "NewRow"."TagLocation" AND "t"."TagID" = "OldRow"."TagID") = 1 THEN
      CASE "substr"("NewRow"."TagLocation",1,1)
      WHEN 'P' THEN UPDATE "PersonKeyWords" SET "TagRefreshRequired" = 1 WHERE "PersonID" = "OldRow"."ID"
      WHEN 'A' THEN UPDATE "PersonKeyWords" SET "TagRefreshRequired" = 1 WHERE "PersonID" = "OldRow"."ID"
      WHEN 'C' THEN UPDATE "CompanyKeyWords" SET "TagRefreshRequired" = 1 WHERE "CompanyID" = "OldRow"."ID"
      WHEN 'V' THEN UPDATE "VacancyKeyWords" SET "TagRefreshRequired" = 1 WHERE "VacancyID" = "OldRow"."ID"
      END CASE
    END IF
  ELSE IF(SELECT "t"."IncludeInKeyWordSearch" FROM "Tag" AS "t" WHERE "t"."TagLocation" = "NewRow"."TagLocation" AND "t"."TagID" = "NewRow"."TagID") = 1 THEN
      CASE "substr"("NewRow"."TagLocation",1,1)
      WHEN 'P' THEN UPDATE "PersonKeyWords" SET "TagRefreshRequired" = 1 WHERE "PersonID" = "NewRow"."ID"
      WHEN 'A' THEN UPDATE "PersonKeyWords" SET "TagRefreshRequired" = 1 WHERE "PersonID" = "NewRow"."ID"
      WHEN 'C' THEN UPDATE "CompanyKeyWords" SET "TagRefreshRequired" = 1 WHERE "CompanyID" = "NewRow"."ID"
      WHEN 'V' THEN UPDATE "VacancyKeyWords" SET "TagRefreshRequired" = 1 WHERE "VacancyID" = "NewRow"."ID"
      END CASE
    END IF END IF
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."tagvalue"."TagValueKeyWords" IS 
{CREATE TRIGGER TagValueKeyWords 
after  INSERT, UPDATE, DELETE  ORDER 20 ON 
   pears.TagValue
  REFERENCING OLD AS OldRow NEW AS NewRow
 FOR each ROW
BEGIN
   IF deleting THEN
      IF (SELECT t.IncludeInKeyWordSearch FROM Tag t WHERE t.TagLocation = NewRow.TagLocation AND t.TagID = OldRow.TagID) = 1 THEN
        CASE substr(NewRow.TagLocation,1,1) 
          WHEN 'P' THEN UPDATE PersonKeyWords SET  TagRefreshRequired=1 WHERE PersonID = OldRow.ID
          WHEN 'A' THEN UPDATE PersonKeyWords SET TagRefreshRequired=1 WHERE PersonID = OldRow.ID
          WHEN 'C' THEN UPDATE CompanyKeyWords SET  TagRefreshRequired=1 WHERE CompanyID = OldRow.ID
          WHEN 'V' THEN UPDATE VacancyKeyWords SET  TagRefreshRequired=1 WHERE VacancyID = OldRow.ID
        END
 END IF
    ELSE IF (SELECT t.IncludeInKeyWordSearch FROM Tag t WHERE t.TagLocation = NewRow.TagLocation AND t.TagID = NewRow.TagID) = 1 THEN
        CASE substr(NewRow.TagLocation,1,1) 
          WHEN 'P' THEN UPDATE PersonKeyWords SET  TagRefreshRequired=1 WHERE PersonID = NewRow.ID
          WHEN 'A' THEN UPDATE PersonKeyWords SET TagRefreshRequired=1 WHERE PersonID = NewRow.ID
          WHEN 'C' THEN UPDATE CompanyKeyWords SET  TagRefreshRequired=1 WHERE CompanyID = NewRow.ID
          WHEN 'V' THEN UPDATE VacancyKeyWords SET  TagRefreshRequired=1 WHERE VacancyID = NewRow.ID
      END
 END IF END IF;
 END
}
GO