====== pears.tagvalue ======
<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 =====
Values for user defined fields

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| 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 | autoincrement |  |

===== Primary Key =====
  * TagValueID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| tag | taglocation, tagid | [[database:tables:pears_tag|pears.tag (taglocation, tagid)]] | ON DELETE CASCADE |
| tagchoice | taglocation, tagid, tagchoiceid | [[database:tables:pears_tagchoice|pears.tagchoice (taglocation, tagid, tagchoiceid)]] | ON DELETE CASCADE |

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

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| tagvalue_idetc | Index | id, taglocation, tagid |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| TagValueKeyWords | after | insert,delete,update order 20 |

===== Original SQL =====
<code sql>
-- 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
</code>