pears.ConsentPersonOption [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.ConsentPersonOption ======
<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 =====
Consent (GDPR) settings per Person 

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **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 | current timestamp |  |

===== Primary Key =====
  * ConsentPersonOptionID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| ConsentType | ConsentTypeID | [[database:tables:pears_consenttype|pears.ConsentType (ConsentTypeID)]] |  |
| ConsentTypeOption | ConsentTypeOptionID | [[database:tables:pears_consenttypeoption|pears.ConsentTypeOption (ConsentTypeOptionID)]] |  |
| Person | PersonID | [[database:tables:pears_person|pears.Person (personid)]] | ON DELETE CASCADE |

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

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

===== Triggers =====
^ Name ^ Timing ^ Event ^
| ConsentPersonOptioninsupd | after | insert,update order 1 |

===== Original SQL =====
<code sql>
-- 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
</code>