codeNforce users have repeatedly asked to view properties (in the database these are now only parcel records; property is deprecated), code enforcement cases, occupancy periods, and persons based on when they were last updated. These objects, however, are composed of a number of sub-objects, each of which is created from one or more records from one or more database tables. This issue/project is to design and implement a set of triggers in postgres which write the current timestamp to the parent objects any time a linked object is added, updated, or removed (deactivated)
All columns in all table which are foreign keys should contain an _ to signal that they are a FK. If that column is not an FK but does contain an _ this is an error and should be corrected with a patch.
The following sections are listed by the parent object whose feeder tables should each have a trigger on insert, update, or delete which write the current timestamp with now() and, if applicable, which also write the userid of the user doing the update. The userid doing the updating should be written to a field called lastupdatedby_userid. Your triggers could be very helpful by trickling the userid of the updator all the way to the parent, so if userid 106 updates a citation on cecase 102, the new cecase record with an ID of 102 would have a now() written to lastupdatedts and 106 in its lastupdatedby_userid.
Code Enforcement Case: cecase
This table has lots of feeders:
codeviolation
citationviolation
citation
citationcitationstatus (you'll have to read the citation_citationid field and then ask the citationviolation table for a codeviolation_violationid and use a query against the codeviolation table to determine which cecase record to update.
citationdocketno: See the above procedure for finding out the caseid from a change in the citationcitationstatus table
citationdocketnohuman: See above for getting caseid
occinspection or any of its related tables including occinspectedspace and occinspectedspaceelement and occinspectionphotodoc: Note that this is a multi-attached table meaning we can attach an occinspection record to either a code enforcement case with the cecase_caseid field or an occupancy period with the occperiod_periodid field.
event (note that the event table is used to store events for both code enforcement cases and occupancy periods, this is determined by the cecase_caseid FK column and the occperiod_periodid column. Your trigger will have to read either the caseid or the occ period ID and update the appropriate parent table)
Occupancy periods represented by the table occperiod
An occupancy period a period of time associated with one and only one parcel that starts on a date and may or may not end on a future date to which we can grant permits, conduct inspections, and log events. Occupancy periods have an entire family of sub-tables that should trigger an update to occperiod's lastudpatedts and lastupdatedby_userid:
occpermit
occpermitapplication
occperiodphotodoc
occinspection: Note that this is a multi-attached table meaning we can attach an occinspection record to either a code enforcement case with the cecase_caseid field or an occupancy period with the occperiod_periodid field.
occperiodeventrule
event (note that the event table is used to store events for both code enforcement cases and occupancy periods, this is determined by the cecase_caseid FK column and the occperiod_periodid column. Your trigger will have to read either the caseid or the occ period ID and update the appropriate parent table)
Properties represented by the table: parcel
parcelinfo
parcelunit
parcelhuman
parcelmailingaddress or the mailingaddress table records linked to this parcel
parcelphotodoc
parcelmailingaddress
cecase: any change to a cecase record should update the corresponding parcel record, including a simple update to the lastupdatedts field, which would happen from any change of its sub-objects
occperiod: Any change to occperiod itself or the entire family of occperiod tables. Notice that an occ period is attached not to a parcel directly but a parcelunit, so there will need to be some cascading triggers to get the parent parcel record updated when an occperiod record is updated
Persons represented by the table: human
Note that the person table is completely and utterly deprecated. A human record has bunches of linkages, and we want to update the human's lastupdatedts and lastupdatedby_userid field when any of the linking tables are updated as well as the obvious contact phone and emails:
contactemail
contactphone
humanmuni
humancecase
humanparcel
humanoccperiod
humanparcelunit
Trigger on delete
For completion's sake, your triggers should operate on an INSERT, UPDATE, or DELETE but note that the Java users never get to delete a record, they only deactivate them by writing a timestamp to the deactivatedts field and the deactivatedtby_userid FK fields.
Goal
codeNforce users have repeatedly asked to view properties (in the database these are now only
parcelrecords;propertyis deprecated), code enforcement cases, occupancy periods, and persons based on when they were last updated. These objects, however, are composed of a number of sub-objects, each of which is created from one or more records from one or more database tables. This issue/project is to design and implement a set of triggers in postgres which write the current timestamp to the parent objects any time a linked object is added, updated, or removed (deactivated)Resources
Postgres Overview of trigger behavior
Database table and field conventions
All columns in all table which are foreign keys should contain an
_to signal that they are a FK. If that column is not an FK but does contain an_this is an error and should be corrected with a patch.The following sections are listed by the parent object whose feeder tables should each have a trigger on insert, update, or delete which write the current timestamp with
now()and, if applicable, which also write the userid of the user doing the update. The userid doing the updating should be written to a field calledlastupdatedby_userid. Your triggers could be very helpful by trickling the userid of the updator all the way to the parent, so if userid 106 updates a citation on cecase 102, the newcecaserecord with an ID of 102 would have anow()written tolastupdatedtsand 106 in itslastupdatedby_userid.Code Enforcement Case:
cecaseThis table has lots of feeders:
codeviolationcitationviolationcitationcitationcitationstatus(you'll have to read thecitation_citationidfield and then ask thecitationviolationtable for acodeviolation_violationidand use a query against thecodeviolationtable to determine whichcecaserecord to update.citationdocketno: See the above procedure for finding out the caseid from a change in thecitationcitationstatustablecitationdocketnohuman: See above for getting caseidoccinspectionor any of its related tables includingoccinspectedspaceandoccinspectedspaceelementandoccinspectionphotodoc: Note that this is a multi-attached table meaning we can attach anoccinspectionrecord to either a code enforcement case with thececase_caseidfield or an occupancy period with theoccperiod_periodidfield.event(note that the event table is used to store events for both code enforcement cases and occupancy periods, this is determined by thececase_caseidFK column and theoccperiod_periodidcolumn. Your trigger will have to read either the caseid or the occ period ID and update the appropriate parent table)Occupancy periods represented by the table
occperiodAn occupancy period a period of time associated with one and only one parcel that starts on a date and may or may not end on a future date to which we can grant permits, conduct inspections, and log events. Occupancy periods have an entire family of sub-tables that should trigger an update to
occperiod'slastudpatedtsandlastupdatedby_userid:occpermitoccpermitapplicationoccperiodphotodococcinspection: Note that this is a multi-attached table meaning we can attach anoccinspectionrecord to either a code enforcement case with thececase_caseidfield or an occupancy period with theoccperiod_periodidfield.occperiodeventruleevent(note that the event table is used to store events for both code enforcement cases and occupancy periods, this is determined by thececase_caseidFK column and theoccperiod_periodidcolumn. Your trigger will have to read either the caseid or the occ period ID and update the appropriate parent table)Properties represented by the table:
parcelparcelinfoparcelunitparcelhumanparcelmailingaddressor themailingaddresstable records linked to thisparcelparcelphotodocparcelmailingaddresscecase: any change to acecaserecord should update the correspondingparcelrecord, including a simple update to thelastupdatedtsfield, which would happen from any change of its sub-objectsoccperiod: Any change to occperiod itself or the entire family ofoccperiodtables. Notice that an occ period is attached not to a parcel directly but aparcelunit, so there will need to be some cascading triggers to get the parentparcelrecord updated when anoccperiodrecord is updatedPersons represented by the table:
humanNote that the
persontable is completely and utterly deprecated. Ahumanrecord has bunches of linkages, and we want to update thehuman'slastupdatedtsandlastupdatedby_useridfield when any of the linking tables are updated as well as the obvious contact phone and emails:contactemailcontactphonehumanmunihumancecasehumanparcelhumanoccperiodhumanparcelunitTrigger on
deleteFor completion's sake, your triggers should operate on an
INSERT,UPDATE, orDELETEbut note that the Java users never get to delete a record, they only deactivate them by writing a timestamp to thedeactivatedtsfield and thedeactivatedtby_useridFK fields.