====== pears.tag ======
<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 =====
User defined field definitions (Questionnaires)

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **taglocation** | char(3) | NOT NULL |  |  |
| **tagid** | char(3) | NOT NULL |  |  |
| tagtype | char(1) | NOT NULL |  |  |
| description | char(60) | NOT NULL |  |  |
| sortorder | smallint | NULL |  |  |
| minstep | double | NULL |  |  |
| units | char(10) | NULL |  |  |
| required | smallint | NULL |  |  |
| displaygroup | smallint | NULL | 0 |  |
| fixed | smallint | NULL | 0 |  |
| AuditFlag | smallint | NULL | 0 |  |
| PublishToWeb | smallint | NULL | 0 |  |
| PublicOnWeb | smallint | NULL | 0 | Viewable, but not editable, by 3rd parties on web |
| NonSearchable | smallint | NULL | 0 |  |
| HeadingExpanded | smallint | NULL | 0 |  |
| Expiry | smallint | NULL |  |  |
| readonly | smallint | NULL |  |  |
| LongDescription | long varchar | NULL |  |  |
| HeadingAnswers | smallint | NULL | 0 |  |
| Archived | tinyint | NULL |  |  |
| ExpiryBehaviour | smallint | NULL |  |  |
| BackgroundColour | integer | NULL | 16777215 |  |
| FontColour | integer | NULL | 0 |  |
| wizarddisplaygroup | smallint | NULL | 0 |  |
| IncludeInKeyWordSearch | tinyint | NOT NULL | 0 |  |
| DependsOnTagid | char(3) | NULL |  | Ask if this tag is filled (in same taglocation), and DependsOnTagchoiceid is selected or null |
| DependsOnTagchoiceid | char(4) | NULL |  | Ask if this DependsOnTagid choice is selected or null |
| WebDescription | long varchar | NULL |  |  |
| MinValue | double | NULL | 0 |  |
| MaxValue | double | NULL | 0 |  |

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

===== Foreign Keys =====
  * No outgoing foreign keys found.

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_criterion|pears.criterion]] | tag | sourcelocation, tagid | taglocation, tagid |
| [[database:tables:pears_departmenttag|pears.DepartmentTag]] | TagKey | TagLocation, TagID | taglocation, tagid |
| [[database:tables:pears_documenttag|pears.DocumentTag]] | TagKey | TagLocation, TagID | taglocation, tagid |
| [[database:tables:pears_iqxnettaggroupmember|pears.IQXNetTagGroupMember]] | tag | TagLocation, TagID | taglocation, tagid |
| [[database:tables:pears_referencemasterquestion|pears.ReferenceMasterQuestion]] | tag | taglocation, tagid | taglocation, tagid |
| [[database:tables:pears_tagchoice|pears.tagchoice]] | tag | taglocation, tagid | taglocation, tagid |
| [[database:tables:pears_tagcompliance|pears.TagCompliance]] | tag | taglocation, tagid | taglocation, tagid |
| [[database:tables:pears_tagvalue|pears.tagvalue]] | tag | taglocation, tagid | taglocation, tagid |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| tag_sortorder | Index | taglocation, sortorder |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| WPKTagChange | after | insert,delete,update |
| TagInsertAudit | after | insert order 1 |
| TagDeleteAudit | after | delete order 1 |
| TagUpdateAudit | after | update order 1 |
| TagKeyword | after | update of "IncludeInKeyWordSearch" order 20 |
| Tag_UpdateDescriptionFormat | before | insert,update order 50 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."tag"
-- Table comment: User defined field definitions (Questionnaires)
-- Statement count: 17

CREATE TABLE "pears"."tag" (
    "taglocation"                    char(3) NOT NULL
   ,"tagid"                          char(3) NOT NULL
   ,"tagtype"                        char(1) NOT NULL
   ,"description"                    char(60) NOT NULL INLINE 35 PREFIX 8
   ,"sortorder"                      smallint NULL
   ,"minstep"                        double NULL
   ,"units"                          char(10) NULL
   ,"required"                       smallint NULL
   ,"displaygroup"                   smallint NULL DEFAULT 0
   ,"fixed"                          smallint NULL DEFAULT 0
   ,"AuditFlag"                      smallint NULL DEFAULT 0
   ,"PublishToWeb"                   smallint NULL DEFAULT 0
   ,"PublicOnWeb"                    smallint NULL DEFAULT 0
   ,"NonSearchable"                  smallint NULL DEFAULT 0
   ,"HeadingExpanded"                smallint NULL DEFAULT 0
   ,"Expiry"                         smallint NULL
   ,"readonly"                       smallint NULL
   ,"LongDescription"                long varchar NULL
   ,"HeadingAnswers"                 smallint NULL DEFAULT 0
   ,"Archived"                       tinyint NULL
   ,"ExpiryBehaviour"                smallint NULL
   ,"BackgroundColour"               integer NULL DEFAULT 16777215
   ,"FontColour"                     integer NULL DEFAULT 0
   ,"wizarddisplaygroup"             smallint NULL DEFAULT 0
   ,"IncludeInKeyWordSearch"         tinyint NOT NULL DEFAULT 0
   ,"DependsOnTagid"                 char(3) NULL
   ,"DependsOnTagchoiceid"           char(4) NULL
   ,"WebDescription"                 long varchar NULL
   ,"MinValue"                       double NULL DEFAULT 0
   ,"MaxValue"                       double NULL DEFAULT 0
   ,PRIMARY KEY ("taglocation" ASC,"tagid" ASC) 
)
go


COMMENT ON COLUMN "pears"."tag"."PublicOnWeb" IS 
	'Viewable, but not editable, by 3rd parties on web'
go


COMMENT ON COLUMN "pears"."tag"."DependsOnTagid" IS 
	'Ask if this tag is filled (in same taglocation), and DependsOnTagchoiceid is selected or null'
go


COMMENT ON COLUMN "pears"."tag"."DependsOnTagchoiceid" IS 
	'Ask if this DependsOnTagid choice is selected or null'
go


COMMENT ON TABLE "pears"."tag" IS 
	'User defined field definitions (Questionnaires)'
go


CREATE INDEX "tag_sortorder" ON "pears"."tag"
    ( "taglocation","sortorder" )
go


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


create trigger "TagInsertAudit" after insert order 1 on
"pears"."Tag"
referencing new as "new_tag"
for each row
begin
  call "AuditLog"('TAG','',"string"('Question ',"new_tag"."description",' Added id = ',"new_tag"."tagid",' location = ',"new_tag"."taglocation"),'','Added')
end
go


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


create trigger "TagDeleteAudit" after delete order 1 on
"pears"."tag"
referencing old as "old_tag"
for each row
begin
  call "AuditLog"('TAG','',"string"('Question ',"old_tag"."description",' Deleted id = ',"old_tag"."tagid",' location = ',"old_tag"."taglocation"),
  "string"('sort order = ',"old_tag"."sortorder",' type = ',"old_tag"."tagtype",' Min step = ',"old_tag"."minstep",' Units = ',"old_tag"."units",' Required = ',"old_tag"."required",
  ' Group = ',"old_tag"."displaygroup",' Audit = ',"old_tag"."auditflag",' Web Publish = ',"old_tag"."PublishToweb",' Web View = ',"old_tag"."publiconweb",
  ' Search Hide = ',"old_tag"."nonsearchable",' Collapse = ',"old_tag"."HeadingExpanded",'  Expiry Lead = ',"old_tag"."expiry",' Long Description = ',"old_tag"."longdescription"),'')
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."tag"."TagDeleteAudit" IS 
{create trigger TagDeleteAudit 
 after delete order 1 on
tag
referencing old as old_tag
for each row
begin
  call AuditLog('TAG','',string('Question ',old_tag.description,' Deleted id = ',old_tag.tagid,' location = ', old_tag.taglocation),
string('sort order = ',old_tag.sortorder,' type = ',old_tag.tagtype,' Min step = ',old_tag.minstep,' Units = ',old_tag.units,' Required = ',old_tag.required, 
' Group = ',old_tag.displaygroup,' Audit = ',old_tag.auditflag,' Web Publish = ',old_tag.PublishToweb,' Web View = ',old_tag.publiconweb,
' Search Hide = ',old_tag.nonsearchable,' Collapse = ',old_tag.HeadingExpanded,'  Expiry Lead = ',old_tag.expiry,' Long Description = ',old_tag.longdescription ),'')
end
}
go


create trigger "TagUpdateAudit" after update order 1 on
"pears"."tag"
referencing old as "old_tag" new as "new_tag"
for each row
begin
  call "AuditLog"('TAG','',"string"('Question ',"new_tag"."description",' Changed id = ',"old_tag"."tagid",' location = ',"old_tag"."taglocation"),
  "string"('description = ',"old_tag"."description",' sort order = ',"old_tag"."sortorder",' type = ',"old_tag"."tagtype",' Min step = ',"old_tag"."minstep",' Units = ',"old_tag"."units",' Required = ',"old_tag"."required",
  ' Group = ',"old_tag"."displaygroup",' Audit = ',"old_tag"."auditflag",' Web Publish = ',"old_tag"."PublishToweb",' Web View = ',"old_tag"."publiconweb",
  ' Search Hide = ',"old_tag"."nonsearchable",' Collapse = ',"old_tag"."HeadingExpanded",'  Expiry Lead = ',"old_tag"."expiry",' Long Description = ',"old_tag"."longdescription",' Web Description = ',"old_tag"."webdescription"),
  "string"('description = ',"new_tag"."description",' sort order = ',"new_tag"."sortorder",' type = ',"new_tag"."tagtype",' Min step = ',"new_tag"."minstep",' Units = ',"new_tag"."units",' Required = ',"new_tag"."required",
  ' Group = ',"new_tag"."displaygroup",' Audit = ',"new_tag"."auditflag",' Web Publish = ',"new_tag"."PublishToweb",' Web View = ',"new_tag"."publiconweb",
  ' Search Hide = ',"new_tag"."nonsearchable",' Collapse = ',"new_tag"."HeadingExpanded",'  Expiry Lead = ',"new_tag"."expiry",' Long Description = ',"new_tag"."longdescription",' Web Description = ',"new_tag"."webdescription"))
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."tag"."TagUpdateAudit" IS 
{create trigger TagUpdateAudit 
 after update order 1 on
tag
referencing old as old_tag new as new_tag
for each row
begin
  call AuditLog('TAG','',string('Question ',new_tag.description,' Changed id = ',old_tag.tagid,' location = ', old_tag.taglocation),
string('description = ',old_tag.description,' sort order = ',old_tag.sortorder,' type = ',old_tag.tagtype,' Min step = ',old_tag.minstep,' Units = ',old_tag.units,' Required = ',old_tag.required, 
' Group = ',old_tag.displaygroup,' Audit = ',old_tag.auditflag,' Web Publish = ',old_tag.PublishToweb,' Web View = ',old_tag.publiconweb,
' Search Hide = ',old_tag.nonsearchable,' Collapse = ',old_tag.HeadingExpanded,'  Expiry Lead = ',old_tag.expiry,' Long Description = ',old_tag.longdescription ,' Web Description = ',old_tag.webdescription),
string('description = ',new_tag.description,' sort order = ',new_tag.sortorder,' type = ',new_tag.tagtype,' Min step = ',new_tag.minstep,' Units = ',new_tag.units,' Required = ',new_tag.required, 
' Group = ',new_tag.displaygroup,' Audit = ',new_tag.auditflag,' Web Publish = ',new_tag.PublishToweb,' Web View = ',new_tag.publiconweb,
' Search Hide = ',new_tag.nonsearchable,' Collapse = ',new_tag.HeadingExpanded,'  Expiry Lead = ',new_tag.expiry,' Long Description = ',new_tag.longdescription,' Web Description = ',new_tag.webdescription ))
end
}
go


create trigger "TagKeyword" after update of "IncludeInKeyWordSearch"
order 20 on
"pears"."Tag"
referencing new as "NewRow"
for each row
begin
  case when("NewRow"."TagLocation" in( 'P' ) or "NewRow"."TagLocation" like 'A%') then
    case
    when "NewRow"."TagType" <> 'M'
    or(select "count"() from "TagChoice" as "tc" where "tc"."TagLocation" = "NewRow"."TagLocation" and "tc"."TagID" = "NewRow"."TagID" and "tc"."TagChoiceID" = '_') = 0 then // no default TagChoice so only People with TagValue records need updating  
      update "PersonKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "PersonKeyWords" as "pkw" join "TagValue" as "tv" on "tv"."TagLocation" = "NewRow"."TagLocation" and "tv"."TagID" = "NewRow"."TagID" and "tv"."ID" = "pkw"."PersonID"
    when "NewRow"."TagLocation" like 'A%' then // only need to set values for 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)
    when "NewRow"."TagLocation" = 'P' then // need to set values for all  
      update "PersonKeyWords" as "pkw" set "TagRefreshRequired" = 1
    end case
  when("NewRow"."TagLocation" like 'C%') then
    case
    when "NewRow"."TagType" <> 'M'
    or(select "count"() from "TagChoice" as "tc" where "tc"."TagLocation" = "NewRow"."TagLocation" and "tc"."TagID" = "NewRow"."TagID" and "tc"."TagChoiceID" = '_') = 0 then // no default TagChoice so only Company with TagValue records need updating  
      update "CompanyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "CompanyKeyWords" as "pkw" join "TagValue" as "tv" on "tv"."TagLocation" = "NewRow"."TagLocation" and "tv"."TagID" = "NewRow"."TagID" and "tv"."ID" = "pkw"."CompanyID"
    when "length"("NewRow"."TagLocation") > 1 then // only need to set values for that department 
      update "CompanyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "CompanyKeyWords" as "pkw" join "company" as "c" on("c"."company" = "pkw"."CompanyID"),"company" as "c" key join "staff" as "s" where "s"."Defaultdepartid" = "substring"("NewRow"."TagLocation",2,2)
    when "NewRow"."TagLocation" = 'C' then // need to set values for all  
      update "CompanyKeyWords" as "pkw" set "TagRefreshRequired" = 1
    end case
  when("NewRow"."TagLocation" like 'V%') then
    case
    when "NewRow"."TagType" <> 'M'
    or(select "count"() from "TagChoice" as "tc" where "tc"."TagLocation" = "NewRow"."TagLocation" and "tc"."TagID" = "NewRow"."TagID" and "tc"."TagChoiceID" = '_') = 0 then // no default TagChoice so only vacancy with TagValue records need updating  
      update "VacancyKeyWords" as "pkw" set "TagRefreshRequired" = 1 from "VacancyKeyWords" as "pkw" join "TagValue" as "tv" on "tv"."TagLocation" = "NewRow"."TagLocation" and "tv"."TagID" = "NewRow"."TagID" and "tv"."ID" = "pkw"."VacancyID"
    when "length"("NewRow"."TagLocation") > 1 then // only need to set values for 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,2)
    when "NewRow"."TagLocation" = 'V' then // need to set values for all  
      update "VacancyKeyWords" as "pkw" set "TagRefreshRequired" = 1
    end case
  end case
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."tag"."TagKeyword" IS 
{create trigger TagKeyword 
  AFTER UPDATE OF IncludeInKeyWordSearch ORDER 20 ON 
  pears.Tag  
 referencing new as NewRow   
 FOR EACH ROW  
 BEGIN  
   case when (NewRow.TagLocation  in ('P') or NewRow.TagLocation like 'A%' ) then  
     case 
         when NewRow.TagType != 'M' or 
               (select count(*) from TagChoice tc where tc.TagLocation = NewRow.TagLocation and tc.TagID = NewRow.TagID and tc.TagChoiceID = '_' ) = 0 then // no default TagChoice so only People with TagValue records need updating  
             update PersonKeyWords pkw set TagRefreshRequired = 1 from PersonKeyWords pkw join TagValue tv on tv.TagLocation = NewRow.TagLocation and tv.TagID = NewRow.TagID and tv.ID = pkw.PersonID  
         when NewRow.TagLocation like 'A%' then // only need to set values for 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)  
         when NewRow.TagLocation = 'P' then // need to set values for all  
                 update PersonKeyWords pkw set TagRefreshRequired = 1 
      end case;
   when (NewRow.TagLocation  like 'C%' ) then 
     case 
         when NewRow.TagType != 'M' or 
               (select count(*) from TagChoice tc where tc.TagLocation = NewRow.TagLocation and tc.TagID = NewRow.TagID and tc.TagChoiceID = '_' ) = 0 then // no default TagChoice so only Company with TagValue records need updating  
             update CompanyKeyWords pkw set TagRefreshRequired = 1 from CompanyKeyWords pkw join TagValue tv on tv.TagLocation = NewRow.TagLocation and tv.TagID = NewRow.TagID and tv.ID = pkw.CompanyID  
         when length(NewRow.TagLocation)>1 then // only need to set values for that department 
                  update CompanyKeyWords pkw set TagRefreshRequired = 1 from CompanyKeyWords pkw join company c on (c.company = pkw.CompanyID) , company c key join staff s where s.Defaultdepartid = substring(NewRow.TagLocation,2,2)  
         when NewRow.TagLocation = 'C' then // need to set values for all  
                 update CompanyKeyWords pkw set TagRefreshRequired = 1 
      end case;
   when (NewRow.TagLocation  like 'V%' ) then 
     case 
         when NewRow.TagType != 'M' or 
               (select count(*) from TagChoice tc where tc.TagLocation = NewRow.TagLocation and tc.TagID = NewRow.TagID and tc.TagChoiceID = '_' ) = 0 then // no default TagChoice so only vacancy with TagValue records need updating  
             update VacancyKeyWords pkw set TagRefreshRequired = 1 from VacancyKeyWords pkw join TagValue tv on tv.TagLocation = NewRow.TagLocation and tv.TagID = NewRow.TagID and tv.ID = pkw.VacancyID  
         when length(NewRow.TagLocation)>1 then // only need to set values for 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,2)  
         when NewRow.TagLocation = 'V' then // need to set values for all  
                 update VacancyKeyWords pkw set TagRefreshRequired = 1 
      end case;
   end case;
 END
}
go


create trigger "Tag_UpdateDescriptionFormat" before insert,update order 50 on
"pears"."Tag"
referencing new as "new_tag"
for each row
begin
  set "new_tag"."description" = "trim"("new_tag"."description")
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."tag"."Tag_UpdateDescriptionFormat" IS 
{create trigger Tag_UpdateDescriptionFormat 
 before insert, update order 50 on
 pears.Tag
 referencing new as new_tag
 for each row
 begin
   set new_tag.description = trim(new_tag.description); 
 end
}
go
</code>