====== pears.IQXNetUserLink ======
<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 =====
Links the IQXNetUser table to the person/staff table

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **IQXNetUserLinkID** | char(20) | NOT NULL |  |  |
| IQXNetUserID | char(20) | NOT NULL |  |  |
| PersonID | char(20) | NULL |  |  |
| EmploymentID | char(20) | NULL |  |  |

===== Primary Key =====
  * IQXNetUserLinkID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| IQXNetUser | IQXNetUserID | [[database:tables:pears_iqxnetuser|pears.IQXNetUser (IQXNetUserID)]] | NOT NULL; ON DELETE CASCADE |
| Person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] | ON DELETE CASCADE |
| Employment | EmploymentID | [[database:tables:pears_employment|pears.employment (employmentid)]] | ON DELETE CASCADE |

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

===== Indexes =====
  * No indexes found.

===== Triggers =====
^ Name ^ Timing ^ Event ^
| IQXNetUserLinkDelete | after | delete order 1 |

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."IQXNetUserLink"
-- Table comment: Links the IQXNetUser table to the person/staff table
-- Statement count: 7

CREATE TABLE "pears"."IQXNetUserLink" (
    "IQXNetUserLinkID"               char(20) NOT NULL
   ,"IQXNetUserID"                   char(20) NOT NULL
   ,"PersonID"                       char(20) NULL
   ,"EmploymentID"                   char(20) NULL
   ,PRIMARY KEY ("IQXNetUserLinkID" ASC) 
)
go


COMMENT ON TABLE "pears"."IQXNetUserLink" IS 
	'Links the IQXNetUser table to the person/staff table'
go


ALTER TABLE "pears"."IQXNetUserLink"
    ADD NOT NULL FOREIGN KEY "IQXNetUser" ("IQXNetUserID" ASC)
    REFERENCES "pears"."IQXNetUser" ("IQXNetUserID")
    ON DELETE CASCADE
go


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


ALTER TABLE "pears"."IQXNetUserLink"
    ADD FOREIGN KEY "Employment" ("EmploymentID" ASC)
    REFERENCES "pears"."employment" ("employmentid")
    ON DELETE CASCADE
go


create trigger "IQXNetUserLinkDelete" after delete order 1 on
"pears"."IQXNetUserLink"
referencing old as "old_link"
for each row
begin
  if "old_link"."IQXNetUserID" <> 'ADMINUSER' and not exists(select * from "IQXNetUserLink" where "IQXNetUserID" = "old_link"."IQXNetUserID" and "IQXNetUserLinkID" <> "old_link"."IQXNetUserLinkID") then
    delete from "IQXNetUser" where "IQXNetUserID" = "old_link"."IQXNetUserID"
  end if
end
go


COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."IQXNetUserLink"."IQXNetUserLinkDelete" IS 
{create trigger IQXNetUserLinkDelete 
 after delete order 1 on
"IQXNetUserLink"
referencing old as "old_link"
for each row
begin
  if "old_link"."IQXNetUserID" <> 'ADMINUSER' and not exists(select * from "IQXNetUserLink" where "IQXNetUserID" = "old_link"."IQXNetUserID" and "IQXNetUserLinkID" <> "old_link"."IQXNetUserLinkID") then
    delete from "IQXNetUser" where "IQXNetUserID" = "old_link"."IQXNetUserID"
  end if
end
}
go
</code>