pears.tagchoice [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.tagchoice ======
<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 =====
Choices for picklist responses to User Defined fields

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **taglocation** | char(3) | NOT NULL |  |  |
| **tagid** | char(3) | NOT NULL |  |  |
| **tagchoiceid** | char(4) | NOT NULL |  |  |
| description | char(60) | NOT NULL |  |  |
| sortorder | smallint | NULL |  |  |
| subchoice | smallint | NULL | 0 |  |
| value | double | NULL |  |  |
| aliases | char(250) | NULL |  |  |
| fixed | smallint | NULL | 0 |  |
| DoNotPublishToWeb | smallint | NULL | 0 |  |
| analysis | char(20) | NULL |  |  |
| tagchoiceparentid | char(4) | NULL |  |  |
| LongDescription | long varchar | NULL |  |  |
| readonly | smallint | NULL |  |  |

===== Primary Key =====
  * taglocation, tagid, tagchoiceid

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

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_criterion|pears.criterion]] | tagchoice | sourcelocation, tagid, tagchoiceid | taglocation, tagid, tagchoiceid |
| [[database:tables:pears_tagchoice|pears.tagchoice]] | tagchoice_parent | taglocation, tagid, tagchoiceparentid | taglocation, tagid, tagchoiceid |
| [[database:tables:pears_tagvalue|pears.tagvalue]] | tagchoice | taglocation, tagid, tagchoiceid | taglocation, tagid, tagchoiceid |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| tagchoice_sortorder | Index | taglocation, tagid, sortorder |  |
| tagchoice_description | Index | taglocation, description |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| WPKTagChoiceChange | after | insert,delete,update |
| TagChoiceInsertAudit | after | insert order 1 |
| TagChoiceDeleteAudit | after | delete order 1 |
| TagChoiceUpdateAudit | after | update order 1 |
| TagChoiceKeyword | after | insert,delete,update order 20 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."tagchoice"
-- Table comment: Choices for picklist responses to User Defined fields
-- Statement count: 15

CREATE TABLE "pears"."tagchoice" (
    "taglocation"                    char(3) NOT NULL
   ,"tagid"                          char(3) NOT NULL
   ,"tagchoiceid"                    char(4) NOT NULL
   ,"description"                    char(60) NOT NULL
   ,"sortorder"                      smallint NULL
   ,"subchoice"                      smallint NULL DEFAULT 0
   ,"value"                          double NULL
   ,"aliases"                        char(250) NULL
   ,"fixed"                          smallint NULL DEFAULT 0
   ,"DoNotPublishToWeb"              smallint NULL DEFAULT 0
   ,"analysis"                       char(20) NULL
   ,"tagchoiceparentid"              char(4) NULL
   ,"LongDescription"                long varchar NULL
   ,"readonly"                       smallint NULL
   ,PRIMARY KEY ("taglocation" ASC,"tagid" ASC,"tagchoiceid" ASC) 
)
go


COMMENT ON TABLE "pears"."tagchoice" IS 
	'Choices for picklist responses to User Defined fields'
go


ALTER TABLE "pears"."tagchoice"
    ADD NOT NULL FOREIGN KEY "tag" ("taglocation" ASC,"tagid" ASC)
    REFERENCES "pears"."tag" ("taglocation","tagid")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."tagchoice"
    ADD FOREIGN KEY "tagchoice_parent" ("taglocation" ASC,"tagid" ASC,"tagchoiceparentid" ASC)
    REFERENCES "pears"."tagchoice" ("taglocation","tagid","tagchoiceid")
    ON DELETE CASCADE
go


CREATE INDEX "tagchoice_sortorder" ON "pears"."tagchoice"
    ( "taglocation","tagid","sortorder" )
go


CREATE INDEX "tagchoice_description" ON "pears"."tagchoice"
    ( "taglocation","description" )
go


create trigger "WPKTagChoiceChange" after insert,delete,update on
"pears"."TagChoice"
referencing new as "tc"
for each row
begin
  call "WPKTrackChange"('Q',"tc"."taglocation")
end
go


create trigger "TagChoiceInsertAudit" after insert order 1 on
"pears"."TagChoice"
referencing new as "new_tag"
for each row
begin
  call "AuditLog"('TAGCHOICE','',"string"('Question Choice ',"new_tag"."description",' Added id = ',"new_tag"."tagid",' location = ',"new_tag"."taglocation",' choice id = ',"new_tag"."tagchoiceid"),'','Added')
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."tagchoice"."TagChoiceInsertAudit" IS 
{create trigger TagChoiceInsertAudit 
 after insert order 1 on
TagChoice
referencing new as new_tag
for each row
begin
  call AuditLog('TAGCHOICE','',string('Question Choice ',new_tag.description,' Added id = ',new_tag.tagid,' location = ', new_tag.taglocation,' choice id = ',new_tag.tagchoiceid),'','Added')
end
}
go


create trigger "TagChoiceDeleteAudit" after delete order 1 on
"pears"."tagchoice"
referencing old as "old_tag"
for each row
begin
  call "AuditLog"('TAGCHOICE','',"string"('Question Choice ',"old_tag"."description",' Deleted id = ',"old_tag"."tagid",' location = ',"old_tag"."taglocation",
  ' choice id = ',"old_tag"."tagchoiceid"),"string"('Autofill = ',"old_tag"."aliases",' value = ',"old_tag"."value",' Web Hide = ',"old_tag"."DoNotPublishToWeb",' analysis = ',"old_tag"."analysis",' subchoice = ',"old_tag"."subchoice"),'')
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."tagchoice"."TagChoiceDeleteAudit" IS 
{create trigger TagChoiceDeleteAudit 
 after delete order 1 on
tagchoice
referencing old as old_tag
for each row
begin
  call AuditLog('TAGCHOICE','',string('Question Choice ',old_tag.description,' Deleted id = ',old_tag.tagid,' location = ', old_tag.taglocation,
' choice id = ',old_tag.tagchoiceid),string('Autofill = ',old_tag.aliases,' value = ',old_tag.value,' Web Hide = ',old_tag.DoNotPublishToWeb,' analysis = ',old_tag.analysis,' subchoice = ',old_tag.subchoice),'')
end
}
go


create trigger "TagChoiceUpdateAudit" after update order 1 on
"pears"."tagchoice"
referencing old as "old_tag" new as "new_tag"
for each row
begin
  call "AuditLog"('TAGCHOICE','',"string"('Question Choice ',"new_tag"."description",' Changed id = ',"old_tag"."tagid",' location = ',"old_tag"."taglocation",' choice id = ',"old_tag"."tagchoiceid"),
  "string"('Description = ',"old_tag"."description",' Autofill = ',"old_tag"."aliases",' value = ',"old_tag"."value",' Web Hide = ',"old_tag"."DoNotPublishToWeb",' analysis = ',"old_tag"."analysis",' subchoice = ',"old_tag"."subchoice",' readonly = ',"old_tag"."readonly"),
  "string"('Description = ',"new_tag"."description",' Autofill = ',"new_tag"."aliases",' value = ',"new_tag"."value",' Web Hide = ',"new_tag"."DoNotPublishToWeb",' analysis = ',"new_tag"."analysis",' subchoice = ',"new_tag"."subchoice",' readonly = ',"new_tag"."readonly"))
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."tagchoice"."TagChoiceUpdateAudit" IS 
{create trigger TagChoiceUpdateAudit 
 after update order 1 on
tagchoice
referencing old as old_tag new as new_tag
for each row
begin
  call AuditLog('TAGCHOICE','',string('Question Choice ',new_tag.description,' Changed id = ',old_tag.tagid,' location = ', old_tag.taglocation,' choice id = ',old_tag.tagchoiceid)
,string('Description = ',old_tag.description,' Autofill = ',old_tag.aliases,' value = ',old_tag.value,' Web Hide = ',old_tag.DoNotPublishToWeb,' analysis = ',old_tag.analysis,' subchoice = ',old_tag.subchoice,' readonly = ',old_tag.readonly),
string('Description = ',new_tag.description,' Autofill = ',new_tag.aliases,' value = ',new_tag.value,' Web Hide = ',new_tag.DoNotPublishToWeb,' analysis = ',new_tag.analysis,' subchoice = ',new_tag.subchoice,' readonly = ',new_tag.readonly))
end
}
go


create trigger "TagChoiceKeyword" after insert,delete,update order 20 on
"pears"."TagChoice"
referencing old as "OldRow" new as "NewRow"
for each row
begin
  case when deleting then
    case when("OldRow"."TagLocation" = 'P' or "OldRow"."TagLocation" like 'A%') and(select "IncludeInKeyWordSearch" from "Tag" where "Tag"."TagLocation" = "OldRow"."TagLocation" and "Tag"."TagID" = "OldRow"."TagID") = 1 then
      case
      when "OldRow"."TagChoiceID" <> '_' then // not default TagChoice so only People with related TagValue records need updating  
        update "PersonKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "PersonKeyWords" as "pkw" join "TagValue" as "tv" on "tv"."TagLocation" = "OldRow"."TagLocation" and "tv"."TagID" = "OldRow"."TagID" and "tv"."TagChoiceID" = "OldRow"."TagChoiceID" and "tv"."ID" = "pkw"."PersonID"
      when "OldRow"."TagLocation" like 'A%' then // only need to set values for those registered with that department 
        update "PersonKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "PersonKeyWords" as "pkw" join "Search" as "s" on "s"."PersonID" = "pkw"."PersonID" and "s"."DepartmentID" = "substring"("OldRow"."TagLocation",2)
      when "NewRow"."TagLocation" = 'P' then // need to set values for all 
        update "PersonKeyWords" as "pkw" set "TagRefreshRequired" = 1
      end case
    when("OldRow"."TagLocation" like 'C%') and(select "IncludeInKeyWordSearch" from "Tag" where "Tag"."TagLocation" = "OldRow"."TagLocation" and "Tag"."TagID" = "OldRow"."TagID") = 1 then
      case
      when "OldRow"."TagChoiceID" <> '_' then // not default TagChoice so only People with related TagValue records need updating  
        update "CompanyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "CompanyKeyWords" as "pkw" join "TagValue" as "tv" on "tv"."TagLocation" = "OldRow"."TagLocation" and "tv"."TagID" = "OldRow"."TagID" and "tv"."TagChoiceID" = "OldRow"."TagChoiceID" and "tv"."ID" = "pkw"."CompanyID"
      when "length"("OldRow"."TagLocation") > 1 then // only need to set values for those registered with that department 
        update "CompanyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "CompanyKeyWords" as "pkw" join "Company" as "c" on("c"."CompanyID" = "pkw"."CompanyID"),"Company" as "c" key join "staff" as "s" where "s"."Defaultdepartid" = "substring"("OldRow"."TagLocation",2)
      when "NewRow"."TagLocation" = 'C' then // need to set values for all   
        update "CompanyKeyWords" as "pkw" set "TagRefreshRequired" = 1
      end case
    when("OldRow"."TagLocation" like 'V%') and(select "IncludeInKeyWordSearch" from "Tag" where "Tag"."TagLocation" = "OldRow"."TagLocation" and "Tag"."TagID" = "OldRow"."TagID") = 1 then
      case
      when "OldRow"."TagChoiceID" <> '_' then // not default TagChoice so only People with related TagValue records need updating  
        update "VacancyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "VacancyKeyWords" as "pkw" join "TagValue" as "tv" on "tv"."TagLocation" = "OldRow"."TagLocation" and "tv"."TagID" = "OldRow"."TagID" and "tv"."TagChoiceID" = "OldRow"."TagChoiceID" and "tv"."ID" = "pkw"."VacancyID"
      when "length"("OldRow"."TagLocation") > 1 then // only need to set values for those registered with that department 
        update "VacancyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "VacancyKeyWords" as "pkw" join "Vacancy" as "s" on "s"."VacancyID" = "pkw"."VacancyID" and "s"."DepartmentID" = "substring"("OldRow"."TagLocation",2)
      when "NewRow"."TagLocation" = 'P' then // need to set values for all   
        update "VacancyKeyWords" as "pkw" set "TagRefreshRequired" = 1
      end case
    end case // when ..... - for other tag locations  
  when inserting then
    case when("NewRow"."TagLocation" = 'P' or "NewRow"."TagLocation" like 'A%') and(select "IncludeInKeyWordSearch" from "Tag" where "Tag"."TagLocation" = "NewRow"."TagLocation" and "Tag"."TagID" = "NewRow"."TagID") = 1 then
      if "NewRow"."TagChoiceID" = '_' then // we are only concerned with new default choices - anything else will be dealt with by the TagValue trigger 
        if "NewRow"."TagLocation" like 'A%' then // only need to set values for those registered with that department 
          update "PersonKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "PersonKeyWords" as "pkw" join "Search" as "s" on "s"."PersonID" = "pkw"."PersonID" and "s"."DepartmentID" = "substring"("NewRow"."TagLocation",2)
        else // everyone will need the TagKeyWords re-evaulated 
          update "PersonKeyWords" as "pkw" set "TagRefreshRequired" = 1
        end if
      end if
    when("NewRow"."TagLocation" like 'C%') and(select "IncludeInKeyWordSearch" from "Tag" where "Tag"."TagLocation" = "NewRow"."TagLocation" and "Tag"."TagID" = "NewRow"."TagID") = 1 then
      if "NewRow"."TagChoiceID" = '_' then // we are only concerned with new default choices - anything else will be dealt with by the TagValue trigger 
        if "length"("NewRow"."TagLocation") > 1 then // only need to set values for those registered with that department 
          update "CompanyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "CompanyKeyWords" as "pkw" join "Company" as "c" on("c"."CompanyID" = "pkw"."CompanyID"),"company" as "c" key join "staff" as "s" where "s"."Defaultdepartid" = "substring"("NewRow"."TagLocation",2)
        else // everyone will need the TagKeyWords re-evaulated 
          update "CompanyKeyWords" as "pkw" set "TagRefreshRequired" = 1
        end if
      end if
    when("NewRow"."TagLocation" like 'V%') and(select "IncludeInKeyWordSearch" from "Tag" where "Tag"."TagLocation" = "NewRow"."TagLocation" and "Tag"."TagID" = "NewRow"."TagID") = 1 then
      if "NewRow"."TagChoiceID" = '_' then // we are only concerned with new default choices - anything else will be dealt with by the TagValue trigger 
        if "length"("NewRow"."TagLocation") > 1 then // only need to set values for those registered with that department 
          update "VacancyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "VacancyKeyWords" as "pkw" join "Vacancy" as "s" on "s"."VacancyID" = "pkw"."VacancyID" and "s"."DepartmentID" = "substring"("NewRow"."TagLocation",2)
        else // everyone will need the TagKeyWords re-evaulated 
          update "VacancyKeyWords" as "pkw" set "TagRefreshRequired" = 1
        end if
      end if
    end case
  when updating then
    if "NewRow"."Description" <> "OldRow"."Description" // we are only concerned if the Description has changed  
      and("NewRow"."TagLocation" = 'P' or "NewRow"."TagLocation" like 'A%')
      and(select "IncludeInKeyWordSearch" from "Tag" where "Tag"."TagLocation" = "NewRow"."TagLocation" and "Tag"."TagID" = "NewRow"."TagID") = 1 then
      case
      when "OldRow"."TagChoiceID" <> '_' then // not default TagChoice so only People with related TagValue records need updating 
        update "PersonKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "PersonKeyWords" as "pkw" join "TagValue" as "tv" on "tv"."TagLocation" = "OldRow"."TagLocation" and "tv"."TagID" = "OldRow"."TagID" and "tv"."TagChoiceID" = "OldRow"."TagChoiceID" and "tv"."ID" = "pkw"."PersonID"
      when "NewRow"."TagLocation" like 'A%' then // we only need to deal with the department 
        update "PersonKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "PersonKeyWords" as "pkw" join "Search" as "s" on "s"."PersonID" = "pkw"."PersonID" and "s"."DepartmentID" = "substring"("NewRow"."TagLocation",2)
      when "NewRow"."TagLocation" = 'P' then // need to set values for all  
        update "PersonKeyWords" as "pkw" set "TagRefreshRequired" = 1
      end case
    else if "NewRow"."Description" <> "OldRow"."Description" // we are only concerned if the Description has changed  
        and("NewRow"."TagLocation" like 'C%')
        and(select "IncludeInKeyWordSearch" from "Tag" where "Tag"."TagLocation" = "NewRow"."TagLocation" and "Tag"."TagID" = "NewRow"."TagID") = 1 then
        case
        when "OldRow"."TagChoiceID" <> '_' then // not default TagChoice so only People with related TagValue records need updating  
          update "CompanyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "CompanyKeyWords" as "pkw" join "TagValue" as "tv" on "tv"."TagLocation" = "OldRow"."TagLocation" and "tv"."TagID" = "OldRow"."TagID" and "tv"."TagChoiceID" = "OldRow"."TagChoiceID" and "tv"."ID" = "pkw"."CompanyID"
        when "NewRow"."TagLocation" like 'A%' then // we only need to deal with the department 
          update "CompanyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "CompanyKeyWords" as "pkw" join "Company" as "s" on("s"."CompanyID" = "pkw"."CompanyID"),"Company" as "s" key join "Staff" as "s" where "s"."Defaultdepartid" = "substring"("NewRow"."TagLocation",2)
        when "NewRow"."TagLocation" = 'C' then // need to set values for all  
          update "CompanyKeyWords" as "pkw" set "TagRefreshRequired" = 1
        end case
      else if "NewRow"."Description" <> "OldRow"."Description" // we are only concerned if the Description has changed  
          and("NewRow"."TagLocation" like 'V%')
          and(select "IncludeInKeyWordSearch" from "Tag" where "Tag"."TagLocation" = "NewRow"."TagLocation" and "Tag"."TagID" = "NewRow"."TagID") = 1 then
          case
          when "OldRow"."TagChoiceID" <> '_' then // not default TagChoice so only People with related TagValue records need updating  
            update "VacancyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "VacancyKeyWords" as "pkw" join "TagValue" as "tv" on "tv"."TagLocation" = "OldRow"."TagLocation" and "tv"."TagID" = "OldRow"."TagID" and "tv"."TagChoiceID" = "OldRow"."TagChoiceID" and "tv"."ID" = "pkw"."Vacancyid"
          when "length"("NewRow"."TagLocation") > 1 then // we only need to deal with the department 
            update "VacancyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "VacancyKeyWords" as "pkw" join "vacancy" as "s" on "s"."VacancyID" = "pkw"."VacancyID" and "s"."DepartmentID" = "substring"("NewRow"."TagLocation",2)
          when "NewRow"."TagLocation" = 'V' then // need to set values for all  
            update "VacancyKeyWords" as "pkw" set "TagRefreshRequired" = 1
          end case
        end if end if end if
  end case
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."tagchoice"."TagChoiceKeyword" IS 
{create trigger TagChoiceKeyword 
AFTER INSERT, UPDATE, DELETE ORDER 20 ON
pears.TagChoice 
referencing old as OldRow new as NewRow
FOR EACH ROW 
BEGIN 
  case when DELETING then 
      case when (OldRow.TagLocation ='P' or OldRow.TagLocation like 'A%' ) and (select IncludeInKeyWordSearch from Tag where Tag.TagLocation = OldRow.TagLocation and Tag.TagID = OldRow.TagID) = 1 then  
          case 
           when OldRow.TagChoiceID != '_' then // not default TagChoice so only People with related TagValue records need updating  
                  update PersonKeyWords pkw set TagRefreshRequired = 1 from PersonKeyWords pkw join TagValue tv on tv.TagLocation = OldRow.TagLocation and tv.TagID = OldRow.TagID and tv.TagChoiceID = OldRow.TagChoiceID and tv.ID = pkw.PersonID 
           when OldRow.TagLocation like 'A%' then // only need to set values for those registered with that department 
                  update PersonKeyWords pkw set TagRefreshRequired = 1 from PersonKeyWords pkw join Search s on s.PersonID = pkw.PersonID and s.DepartmentID = substring(OldRow.TagLocation,2) 
           when NewRow.TagLocation = 'P' then // need to set values for all 
                  update PersonKeyWords pkw set TagRefreshRequired = 1 
          end case;
 
  when (OldRow.TagLocation like 'C%' ) and (select IncludeInKeyWordSearch from Tag where Tag.TagLocation = OldRow.TagLocation and Tag.TagID = OldRow.TagID) = 1 then  
          case  
           when OldRow.TagChoiceID != '_' then // not default TagChoice so only People with related TagValue records need updating  
                  update CompanyKeyWords pkw set TagRefreshRequired = 1 from CompanyKeyWords pkw join TagValue tv on tv.TagLocation = OldRow.TagLocation and tv.TagID = OldRow.TagID and tv.TagChoiceID = OldRow.TagChoiceID and tv.ID = pkw.CompanyID 
           when length(OldRow.TagLocation)>1 then // only need to set values for those registered with that department 
                  update CompanyKeyWords pkw set TagRefreshRequired = 1 from CompanyKeyWords pkw join Company c on (c.CompanyID = pkw.CompanyID), Company c key join staff s where s.Defaultdepartid = substring(OldRow.TagLocation,2) 
           when NewRow.TagLocation = 'C' then // need to set values for all   
                  update CompanyKeyWords pkw set TagRefreshRequired = 1 
          end case;
 
  when (OldRow.TagLocation like 'V%' ) and (select IncludeInKeyWordSearch from Tag where Tag.TagLocation = OldRow.TagLocation and Tag.TagID = OldRow.TagID) = 1 then  
          case  
           when OldRow.TagChoiceID != '_' then // not default TagChoice so only People with related TagValue records need updating  
                  update VacancyKeyWords pkw set TagRefreshRequired = 1 from VacancyKeyWords pkw join TagValue tv on tv.TagLocation = OldRow.TagLocation and tv.TagID = OldRow.TagID and tv.TagChoiceID = OldRow.TagChoiceID and tv.ID = pkw.VacancyID 
           when length(OldRow.TagLocation)>1 then // only need to set values for those registered with that department 
                  update VacancyKeyWords pkw set TagRefreshRequired = 1 from VacancyKeyWords pkw join Vacancy s on s.VacancyID = pkw.VacancyID and s.DepartmentID = substring(OldRow.TagLocation,2) 
          when NewRow.TagLocation = 'P' then // need to set values for all   
                  update VacancyKeyWords pkw set TagRefreshRequired = 1 
          end case;
      // when ..... - for other tag locations  
      end case;
  
      when INSERTING then 
           case when (NewRow.TagLocation ='P' or NewRow.TagLocation like 'A%' ) and (select IncludeInKeyWordSearch from Tag where Tag.TagLocation = NewRow.TagLocation and Tag.TagID = NewRow.TagID) = 1 then 
            if NewRow.TagChoiceID = '_' // we are only concerned with new default choices - anything else will be dealt with by the TagValue trigger 
                   then 
                    if NewRow.TagLocation like 'A%' then // only need to set values for those registered with that department 
                          update PersonKeyWords pkw set TagRefreshRequired = 1 from PersonKeyWords pkw join Search s on s.PersonID = pkw.PersonID and s.DepartmentID = substring(NewRow.TagLocation,2) 
                   else // everyone will need the TagKeyWords re-evaulated 
                          update PersonKeyWords pkw set TagRefreshRequired = 1 
                    end if;
             end if;
  	when ( NewRow.TagLocation like 'C%' ) and (select IncludeInKeyWordSearch from Tag where Tag.TagLocation = NewRow.TagLocation and Tag.TagID = NewRow.TagID) = 1 then  
            if NewRow.TagChoiceID = '_' // we are only concerned with new default choices - anything else will be dealt with by the TagValue trigger 
                   then 
                    if length(NewRow.TagLocation)>1 then // only need to set values for those registered with that department 
                          update CompanyKeyWords pkw set TagRefreshRequired = 1 from CompanyKeyWords pkw join Company c on (c.CompanyID = pkw.CompanyID), company c key join staff s where s.Defaultdepartid = substring(NewRow.TagLocation,2) 
                   else // everyone will need the TagKeyWords re-evaulated 
                          update CompanyKeyWords pkw set TagRefreshRequired = 1 
                    end if;
             end if;
  	when ( NewRow.TagLocation like 'V%' ) and (select IncludeInKeyWordSearch from Tag where Tag.TagLocation = NewRow.TagLocation and Tag.TagID = NewRow.TagID) = 1 then  
            if NewRow.TagChoiceID = '_' // we are only concerned with new default choices - anything else will be dealt with by the TagValue trigger 
                   then 
                    if length(NewRow.TagLocation)>1 then // only need to set values for those registered with that department 
                          update VacancyKeyWords pkw set TagRefreshRequired = 1 from VacancyKeyWords pkw join Vacancy s on s.VacancyID = pkw.VacancyID and s.DepartmentID = substring(NewRow.TagLocation,2) 
                   else // everyone will need the TagKeyWords re-evaulated 
                          update VacancyKeyWords pkw set TagRefreshRequired = 1 
                    end if;
             end if;
           end case;
       when UPDATING then
           if NewRow.Description !=  OldRow.Description and // we are only concerned if the Description has changed  
              (NewRow.TagLocation ='P' or NewRow.TagLocation like 'A%' ) and  
              (select IncludeInKeyWordSearch from Tag where Tag.TagLocation = NewRow.TagLocation and Tag.TagID = NewRow.TagID) = 1 then 
                 case 
                   when OldRow.TagChoiceID != '_' then // not default TagChoice so only People with related TagValue records need updating 
                      update PersonKeyWords pkw set TagRefreshRequired = 1 from PersonKeyWords pkw join TagValue tv on tv.TagLocation = OldRow.TagLocation and tv.TagID = OldRow.TagID and tv.TagChoiceID = OldRow.TagChoiceID and tv.ID = pkw.PersonID 
                   when NewRow.TagLocation like 'A%' then // we only need to deal with the department 
                      update PersonKeyWords pkw set TagRefreshRequired = 1 from PersonKeyWords pkw join Search s on s.PersonID = pkw.PersonID and s.DepartmentID = substring(NewRow.TagLocation,2) 
                   when NewRow.TagLocation = 'P' then // need to set values for all  
                      update PersonKeyWords pkw set TagRefreshRequired = 1 
                  end case;
           else if NewRow.Description !=  OldRow.Description and // we are only concerned if the Description has changed  
              (NewRow.TagLocation like 'C%' ) and  
              (select IncludeInKeyWordSearch from Tag where Tag.TagLocation = NewRow.TagLocation and Tag.TagID = NewRow.TagID) = 1 then 
                 case 
                   when OldRow.TagChoiceID != '_' then // not default TagChoice so only People with related TagValue records need updating  
                      update CompanyKeyWords pkw set TagRefreshRequired = 1 from CompanyKeyWords pkw join TagValue tv on tv.TagLocation = OldRow.TagLocation and tv.TagID = OldRow.TagID and tv.TagChoiceID = OldRow.TagChoiceID and tv.ID = pkw.CompanyID 
                   when NewRow.TagLocation like 'A%' then // we only need to deal with the department 
                      update CompanyKeyWords pkw set TagRefreshRequired = 1 from CompanyKeyWords pkw join Company s on (s.CompanyID = pkw.CompanyID), Company s key join Staff s  where s.Defaultdepartid = substring(NewRow.TagLocation,2) 
                   when NewRow.TagLocation = 'C' then // need to set values for all  
                      update CompanyKeyWords pkw set TagRefreshRequired = 1 
                  end case;
           else if NewRow.Description !=  OldRow.Description and // we are only concerned if the Description has changed  
              ( NewRow.TagLocation like 'V%' ) and  
              (select IncludeInKeyWordSearch from Tag where Tag.TagLocation = NewRow.TagLocation and Tag.TagID = NewRow.TagID) = 1 then 
                 case 
                   when OldRow.TagChoiceID != '_' then // not default TagChoice so only People with related TagValue records need updating  
                      update VacancyKeyWords pkw set TagRefreshRequired = 1 from VacancyKeyWords pkw join TagValue tv on tv.TagLocation = OldRow.TagLocation and tv.TagID = OldRow.TagID and tv.TagChoiceID = OldRow.TagChoiceID and tv.ID = pkw.Vacancyid 
                   when length(NewRow.TagLocation)>1 then // we only need to deal with the department 
                      update VacancyKeyWords pkw set TagRefreshRequired = 1 from VacancyKeyWords pkw join vacancy s on s.VacancyID = pkw.VacancyID and s.DepartmentID = substring(NewRow.TagLocation,2) 
                   when NewRow.TagLocation = 'V' then // need to set values for all  
                      update VacancyKeyWords pkw set TagRefreshRequired = 1 
                  end case;
            end if end if end if ;
     end case
 END
}
go
</code>