pears.OffLimits [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.OffLimits ======
<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 =====
Limits on approaching Companies or People during specfied period.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **OffLimitsID** | char(20) | NOT NULL |  |  |
| PersonID | char(20) | NULL |  |  |
| CompanyID | char(20) | NULL |  |  |
| Until | date | NOT NULL |  |  |
| Reason | char(255) | NULL |  |  |
| StaffID | char(20) | NOT NULL |  |  |
| WhenEntered | timestamp | NOT NULL | current timestamp |  |

===== Primary Key =====
  * OffLimitsID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| Person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] | ON DELETE CASCADE |
| Company | CompanyID | [[database:tables:pears_company|pears.Company (companyid)]] | ON DELETE CASCADE |
| Staff | StaffID | [[database:tables:pears_staff|pears.staff (staffid)]] | NOT NULL;  |

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

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| OffLimits_CompanyIDUntil | Index | CompanyID, Until |  |
| OffLimits_PersonIDUntil | Index | PersonID, Until |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| AuditOffLimits | before | insert,delete,update order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."OffLimits"
-- Table comment: Limits on approaching Companies or People during specfied period.
-- Statement count: 9

CREATE TABLE "pears"."OffLimits" (
    "OffLimitsID"                    char(20) NOT NULL
   ,"PersonID"                       char(20) NULL
   ,"CompanyID"                      char(20) NULL
   ,"Until"                          date NOT NULL
   ,"Reason"                         char(255) NULL
   ,"StaffID"                        char(20) NOT NULL
   ,"WhenEntered"                    timestamp NOT NULL DEFAULT current timestamp
   ,PRIMARY KEY ("OffLimitsID" ASC) 
)
go


COMMENT ON TABLE "pears"."OffLimits" IS 
	'Limits on approaching Companies or People during specfied period.'
go


ALTER TABLE "pears"."OffLimits"
    ADD FOREIGN KEY "Person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."OffLimits"
    ADD FOREIGN KEY "Company" ("CompanyID" ASC)
    REFERENCES "pears"."Company" ("companyid")
    ON DELETE CASCADE
go


ALTER TABLE "pears"."OffLimits"
    ADD NOT NULL FOREIGN KEY "Staff" ("StaffID" ASC)
    REFERENCES "pears"."staff" ("staffid")
go


CREATE INDEX "OffLimits_CompanyIDUntil" ON "pears"."OffLimits"
    ( "CompanyID","Until" DESC )
go


CREATE INDEX "OffLimits_PersonIDUntil" ON "pears"."OffLimits"
    ( "PersonID","Until" DESC )
go


create trigger "AuditOffLimits" before insert,delete,update order 1 on
"pears"."OffLimits"
referencing old as "OldOffLimits" new as "NewOffLimits"
for each row
begin
  if inserting then
    case
    when "NewOffLimits"."CompanyID" is not null then
      call "AuditLog"('COMPANY',"NewOffLimits"."CompanyID",'Off Limits Restriction Inserted',null,"string"("NewOffLimits"."Reason",' - ',"NewOffLimits"."Until"))
    when "NewOffLimits"."PersonID" is not null then
      call "AuditLog"('PERSON',"NewOffLimits"."PersonID",'Off Limits Restriction Inserted',null,"string"("NewOffLimits"."Reason",' - ',"NewOffLimits"."Until"))
    end case
  elseif deleting then
    case
    when "OldOffLimits"."CompanyID" is not null then
      call "AuditLog"('COMPANY',"OldOffLimits"."CompanyID",'Off Limits Restriction Deleted',"string"("OldOffLimits"."Reason",' - ',"OldOffLimits"."Until"),null)
    when "NewOffLimits"."PersonID" is not null then
      call "AuditLog"('PERSON',"OldOffLimits"."PersonID",'Off Limits Restriction Deleted',"string"("OldOffLimits"."Reason",' - ',"OldOffLimits"."Until"),null)
    end case
  elseif updating then
    case
    when "OldOffLimits"."CompanyID" is not null then
      call "AuditLog"('COMPANY',"OldOffLimits"."CompanyID",'Off Limits Restriction Updated',"string"("OldOffLimits"."Reason",' - ',"OldOffLimits"."Until"),"string"("NewOffLimits"."Reason",' - ',"NewOffLimits"."Until"))
    when "NewOffLimits"."PersonID" is not null then
      call "AuditLog"('PERSON',"OldOffLimits"."PersonID",'Off Limits Restriction Updated',"string"("OldOffLimits"."Reason",' - ',"OldOffLimits"."Until"),"string"("NewOffLimits"."Reason",' - ',"NewOffLimits"."Until"))
    end case
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."OffLimits"."AuditOffLimits" IS 
{create trigger AuditOffLimits 
 before insert,delete,update order 1 on
pears.OffLimits
referencing old as OldOffLimits new as NewOffLimits
for each row
begin
  if inserting then
    case
    when NewOffLimits.CompanyID is not null then
      call AuditLog('COMPANY',NewOffLimits.CompanyID,'Off Limits Restriction Inserted',null,string(NewOffLimits.Reason,' - ',NewOffLimits.Until))
    when NewOffLimits.PersonID is not null then
      call AuditLog('PERSON',NewOffLimits.PersonID,'Off Limits Restriction Inserted',null,string(NewOffLimits.Reason,' - ',NewOffLimits.Until))
    end case
  elseif deleting then
    case
    when OldOffLimits.CompanyID is not null then
      call AuditLog('COMPANY',OldOffLimits.CompanyID,'Off Limits Restriction Deleted',string(OldOffLimits.Reason,' - ',OldOffLimits.Until),null)
    when NewOffLimits.PersonID is not null then
      call AuditLog('PERSON',OldOffLimits.PersonID,'Off Limits Restriction Deleted',string(OldOffLimits.Reason,' - ',OldOffLimits.Until),null)
    end case
  elseif updating then
    case
    when OldOffLimits.CompanyID is not null then
      call AuditLog('COMPANY',OldOffLimits.CompanyID,'Off Limits Restriction Updated',string(OldOffLimits.Reason,' - ',OldOffLimits.Until),string(NewOffLimits.Reason,' - ',NewOffLimits.Until))
    when NewOffLimits.PersonID is not null then
      call AuditLog('PERSON',OldOffLimits.PersonID,'Off Limits Restriction Updated',string(OldOffLimits.Reason,' - ',OldOffLimits.Until),string(NewOffLimits.Reason,' - ',NewOffLimits.Until))
    end case
  end if
end
}
go
</code>