pears.ConsentPersonOption

Consent (GDPR) settings per Person

• ConsentPersonOptionID

• No incoming foreign keys found.

• No indexes found.

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."ConsentPersonOption"
-- Table comment: Consent (GDPR) settings per Person 
-- Statement count: 7
 
CREATE TABLE "pears"."ConsentPersonOption" (
    "ConsentPersonOptionID"          CHAR(20) NOT NULL
   ,"ConsentTypeID"                  CHAR(20) NULL
   ,"ConsentTypeOptionID"            CHAR(20) NULL
   ,"PersonID"                       CHAR(20) NULL
   ,"WhoEntered"                     CHAR(20) NULL
   ,"WithdrawDate"                   DATE NULL
   ,"UpdateDate"                     TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP
   ,PRIMARY KEY ("ConsentPersonOptionID" ASC) 
)
GO
 
 
COMMENT ON TABLE "pears"."ConsentPersonOption" IS 
	'Consent (GDPR) settings per Person '
GO
 
 
ALTER TABLE "pears"."ConsentPersonOption"
    ADD FOREIGN KEY "ConsentType" ("ConsentTypeID" ASC)
    REFERENCES "pears"."ConsentType" ("ConsentTypeID")
GO
 
 
ALTER TABLE "pears"."ConsentPersonOption"
    ADD FOREIGN KEY "ConsentTypeOption" ("ConsentTypeOptionID" ASC)
    REFERENCES "pears"."ConsentTypeOption" ("ConsentTypeOptionID")
GO
 
 
ALTER TABLE "pears"."ConsentPersonOption"
    ADD FOREIGN KEY "Person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
    ON DELETE CASCADE
GO
 
 
CREATE TRIGGER "ConsentPersonOptioninsupd" after INSERT,UPDATE ORDER 1 ON
"pears"."ConsentPersonOption"
REFERENCING OLD AS "old_d" NEW AS "new_d"
FOR each ROW
BEGIN
  INSERT INTO "ConsentPersonOptionHistory"( "ConsentPersonOptionHistoryID","ConsentTypeID","ConsentTypeOptionID","PersonID","WhoEntered","ConnectionNumber","WithdrawDate" ) VALUES
    ( "uniquekey"('x'),"new_d"."ConsentTypeID","new_d"."ConsentTypeOptionID","new_d"."PersonID","new_d"."WhoEntered","connection_property"('Number'),"new_d"."WithdrawDate" ) 
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."ConsentPersonOption"."ConsentPersonOptioninsupd" IS 
{CREATE TRIGGER ConsentPersonOptioninsupd 
 after INSERT,UPDATE ORDER 1 ON
pears.ConsentPersonOption
REFERENCING OLD AS old_d NEW AS new_d
FOR each ROW
BEGIN
    INSERT INTO ConsentPersonOptionHistory(ConsentPersonOptionHistoryID, ConsentTypeID, ConsentTypeOptionID, PersonID, WhoEntered, ConnectionNumber, WithdrawDate)
 VALUES (uniquekey('x'), new_d.ConsentTypeID, new_d.ConsentTypeOptionID, new_d.PersonID, new_d.WhoEntered, connection_property('Number'),new_d.WithdrawDate) 
END
}
GO