pears.IQXNetUserLink

Links the IQXNetUser table to the person/staff table

• IQXNetUserLinkID

• No incoming foreign keys found.

• No indexes found.

-- 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