More information

Database Trigger Behaviors

In Millennium, when data is entered into one of the standard html forms and submitted to the database, software triggers fire to make certain behaviors or additional data manipulation occur. These triggers account for most of the behind the scenes linkage and automatic actions within Millennium. Some of the trigger behaviors are the same for every data row, regardless of the data table. These are described below as the Standard Triggers. Some behaviors are standard for any data table row that may have links to or from another data row. These are described below as Linking Triggers. Beyond that, many data rows employ triggers that are specific to that data table. Each data table is listed below and the trigger behaviors for each are described.

SQL Server and Oracle systems use completely independent sets of triggers with different internal techniques, however, the end result for each Millennium system will be the same. This document does not describe each step performed by each trigger, but rather is a description of the behaviors that occur when the triggers execute.

For a listing of the SQL Triggers and the Oracle Triggers, see System Triggers.

Standard Triggers

There are certain standard trigger behaviors that will apply to each of the data tables in the Millennium database.

If you enable audit logging for a table, the standard triggers write a row to the audit_{tablename} table, where tablename is the name of the table for which auditing is enabled. Millennium uses the audit table to track the changes made to fields in the table. For more information about the data tables for which Millennium system administrators or the database administrator can configure audit logging, see Data Tables Available for Audit Logging. For more information about the audit_{tablename} table, see audit_{tablename} Field Descriptions.

Insert

The User ID is filled in via a default which calls a system function.
The date is filled in via a default which calls a system function.

Update

The User ID is filled in via a default which calls a system function.
The change date field is filled in via a default which calls a system function.
The usergroup field is filled in using a trigger which calls a stored procedure.
Checks to see if the 'doc' field (flag for linked attachments) went from 'Y' to 'N'. If so, all attachment rows are deleted.

Delete

Deleting the data row also deletes all linked attachment rows.

Linking Triggers

Certain data tables permit that their data rows can be linked to another data table row. All tables in this category use triggers that exhibit the following behaviors.
The row to be linked must contain the file number of the table to which it is to be linked.
The row to be linked must contain the key field from the row to which it is to be linked.
The appropriate flags are set in the 'parent' (the primary)table row to signal the existence of linked rows.*
If the primary row is deleted, the linked secondary rows will also be deleted, with the exception of linked Giving and Dues, which are not deleted even if they are linked to a primary row that is deleted.
All secondary rows of the same type can be unlinked by changing the corresponding flag in the primary table to 'N'. Example - For Relations linked to a Proposal. If proprelflg is changed to 'N', all four Relations are deleted. Other linked rows (such as Ratings) are not affected.

Note: If screens have been customized to include any Custom Lookup table (lookup1, lookup2, lookup3) and those screens are in turn used during the linking process, the custom lookups will only display on the primary row and not the secondary rows.

Profile Data Table Triggers

Actions

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

The actcompflg is set to 'Y' if an actcompdat (Completed Date) is entered.

Addresses

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

On Insert and Update, set the addrforign = 'Y' if the Country's type/group is 'for'.
On Insert and Update, if addrlnkadd > 0 (there is a linked address), set the addr link add equal to the addrkey of the master in both the master and the linked address row.
On Insert and Update, if the data in the addrzipcod is > 5 characters, does not include a dash, and is not a foreign address (addrforign is 'Y'), put a dash after the fifth character.
On Insert and Update, if a linked Employment row is created (addrlnkjob is changed to > 0), the addrlnkjob is equal to the jobskey, and the jobsaddrky is set equal to the addrkey in the Address row.
On Insert, if the first 5 characters of the zipcode have data and the address is not foreign, set the following fields equal to the corresponding data for the entry in the ZIP Code lookup: City, County, District, Region, Territory, Zone, Latitude, Longitude, and Time Zone (disregarding any data that is entered in the insert form for those columns).
On Update, set the addraddrdat equal to today's date.
On Update, if the zipcode is changed, set the following fields equal to the corresponding data for the entry in the ZIP Code lookup: City, County, District, Region, Territory, Zone, Latitude, Longitude, and Time Zone, UNLESS those fields also changed in the Update form.
On Update, if there is a Death row, set the Locator (addrlocatr) = 'd'.
On Update, if a linked Employment is unlinked (addrlnkjob changes from > 0 to 0), set jobsaddrky = 0 in the formerly linked Employment.
On Update, if the current address was linked and was the master and is then unlinked, the remaining linked addresses will have a new master established by finding the remaining address in the linked list with the minimum key, and making that address the new master. If the address was linked and was not the master, and there are no more linked, the master will be updated to indicate that it is no longer a master (addrlnkadd is set = 0).
On Update, if the updated row is linked to other addresses (addrlnkadd> 0), then for all linked addresses, update the following fields in the linked address row(s): Last update to row date (addrdate), last update User ID, last update usergroup, last update to address data date (addraddrdt), line 1, 2, and 3, city, place, ZIP Code, region, district, locator, type, effective months, alternate phone, foreign flag, source, phone restrictions 1, 2, and 3, county, phone, fax phone, country, carrier route, time zone, zone, and territory. (Does not update Utilizations.)
On Update, if the values of one or more of these fields - addrline1, addrline2, addrline3, addrcity, addrplace, addrzipcod, addrcrrte, addrbarcde, or addrlot - are changed, then addrccert is set to 'N' and addrdpv, addrnostat, addrdpvcnt, addrlacsch, addrmovtyp, and addrcoamtc are set to blank.
On Update, if addrccert is set to 'N' then addrdpv, addrnostat, addrdpvcnt, addrlacsch, addrmovtyp, and addrcoamtc are set to blank.
On Delete, if there is a link to an Employment row (addrlnkjob>0) then set jobsaddrky = 0.
On Delete, if the deleted address was linked and was the master, the remaining linked addresses will have a new master established by finding the remaining address in the linked list with the minimum key, and making that address the new master. If the deleted address was linked and was not the master, and there are no more linked, the master will be updated to indicate that it is no longer a master (addrlnkadd is set = 0).

Attachments

The standard triggers rules and the linking triggers rules apply to this table:

Attribute

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

Attstrt (Start date) must be less than attrstop (Stop date) date.

Attrtypenm (Attribute Type value) is filled in from Attribute Types lookup table.

Basic Data (see Corebio)

Benefits

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

The bennumber (number of benefits) is set equal to benefit_type.table_amt1 (Amount 1 in the lookup table).

Citizenship (see Nationality)

Comments

This data table uses only the standard triggers.

Corebio

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

On Insert and Update, set the corecfae = the table_cfae from the lookup table entry used in the constituent type (coreconst) field.
On Insert and Update, set the coretbltyp = the table type(group) from the lookup table entry used in the constituent type (coreconst) field.
On Insert and Update, set the coreacctnm = the account name from the entry in the Chart of Accounts lookup table, identified by the corerest field.
On Update, if the marital status is set to 'd', check for a Relationship with a type of 'sp' or 'spnm'. If found, update the spouse's (relrelatid) marital status to 'd', and change the relationship type for both parties to 'dvsp' (relisa and relwhose = 'dvsp').
On Update, if the coretbltyp = 'C' or 'F' call the procedure corebio_fix_ratio to set the data entered for ratio 1, 2, and 3 into the format '0.0'.
On Update, if the first match ratio (coremrat1) is > 0.0, set the match flag (jobsmflag) = 'Y' for all Employment rows that reference this row (jobscpnyid = coreid) and where the match flag is not 'Y', otherwise set the match flag (jobsmflag) = 'N'.
On Update, if the parent corporation is filled in for first time (corecorpid), then insert a Relationship row setting the relkey = corerlkey2 and relrevkey = corerlkey1, with types of 'pc' and 'sub'. Update the known subsidiaries (corekwnsub) in the parent corporation's Basic Data row.
On Update, if the parent corporation changes, update the Relationship row to the new parent corporation (relrelatid = corecorpid) and update the known subsidiaries (corekwnsub) in the new and old parent corporation's Basic Data.
On Update, if the name of the parent corporation is removed (set the field to blank) the parent corporation/subsidiary relationship is deleted.
On Delete, update the known subsidiaries (corekwnsub) in the parent corporation's Basic Data row.
On Delete, execute the delete triggers for all data rows that belong to the deleted Basic Data row.

Correspondence

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

Calls the stored procedure name_firstrow using corrsentid to get the first name row and populates the name fields in the Correspondence row.

Coverage

On Insert or Update, if the covisa= 'cover', set covwhose = 'covee'. If the covisa = 'covee', set covwhose = 'cover'.
On Insert or Update, execute the stored procedure name_firstrow to update the coverage row with the appropriate name pieces for the second constituent's name.
On Insert or Update, create or update the reciprocal (reverse) row for the second constituent.

Credit Card

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

Calls the stored procedure creditcardvalidation to verify that the form of the data entered corresponds to the credit card type that was entered.

Death

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

On Insert, set the deceased flag (namedflag) to 'Y' if constituent is a person (coretbltyp is blank) or 'D' if constituent is corporation, foundation, or other (coretbltyp = 'C' or 'F').
On Insert, set all Address locators = 'd'.
On Insert, unlink all addresses (addrlnkadd = 0) that are linked to this constituent's addresses.
On Insert, for all Employment rows with an active status (jobsstatus = 'act') change the jobsstatus to no longer at this position (jobsstatus = 'no')
On Insert, change all employee/employer (emp/boss) Relationships to former employee/former employer (femp/fbos).
On Insert, check the marital status in the Basic Data (coremaritl). If that is married or surviving spouse ('m' or 'ss'), change it to deceased spouse ('ds').
On Insert, check for a Relationship type where the constituent is a spouse, surviving spouse, spouse no mail, life partner, or combined mail ('sp', 'ssp', 'spnm', 'lp', 'cm'). If found, check the marital status in the Basic Data of the second constituent. If it is married ('m'), change it to surviving spouse ('ss').
On Insert, check for a Relationship type where the constituent is a spouse, surviving spouse, or spouse no mail (relisa = 'sp', 'ssp', or 'spnm'). Change the relationship types for both relationships to deceased spouse/surviving spouse ('dsp'/'ssp') unless the second spouse is already deceased, in which case they will both have the type deceased spouse.
On Insert, check for a Relationship type where the constituent is a spouse, surviving spouse, or spouse no mail (relisa = 'sp', 'ssp', or 'spnm'). If found, check the Name rows belonging to the constituent and also to the spouse. If the Name type is anything other than 'comb' or 'csal', set the plural name form equal to blank so that both the combined name and combined salutation fields in these Name rows can be updated with the standard, singular form for these fields.
On Delete, set the deceased flag (namedflag) in all Name rows for the constituent equal to 'N'.

Demographic

This data table uses only the standard triggers.

Dues Transactions

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

On Insert, check that the batch is open (dues_batch_number.table_type = 'open'). If not, an error message is generated.
On Insert, set the dues constituent type (duesconst) equal to the constituent type from the Basic Data row (coreconst).
On Insert, set the transaction number (duestrnum) to the next transaction number shown for that user in the userlist.
On Insert, set the exchange rate (duesexrate) equal to the rate taken from the Currency lookup table entry that was entered for the currency field (duescurr).
On Insert, if the Campaign is left blank (duescamp), do the following in the order given:
Check the Solicitation lookup table entry that was specified in the solicitation field (duessolic). If the campaign mandatory flag in that lookup table entry is 'Y' (solicitation.sol_mand), then set the dues campaign and year (duescamp and duescampyr) equal to the solicitation's campaign and year(sol_camp and sol_campyr).
- If above is not true (solicitation.sol_mand = 'N'), then check the Chart of Accounts entry that was specified for the dues. If the campaign mandatory flag in that lookup entry is 'Y' (chart_of_accounts.chart_mand), then set the dues campaign (duescamp) equal to the account's campaign and year(chart_camp).
- On Insert, if the transaction is without links or allocations, set the duesallkey = dueskey and set the duessumkey = dueskey.
On Insert, if the transaction is a match pledge, match potential, or expectancy ('mp', 'o', 'e'), set the conditional flag to 'Y' (duesrevoke).
On Insert, if the transaction is a pledge, expectancy, match pledge or match potential ('p', 'e', mp', 'o'), and the conditional flag is 'Y' (duesrevoke), then set the asset and net present value amounts to 0 (duesasset and duesnetpv).
On Insert, if the transaction is a pledge, expectancy, match pledge or match potential ('p', 'e', mp', 'o'), and the conditional flag is 'N' (duesrevoke), then set the asset and net present value amounts (duesasset and duesnetpv) to the dues amount (duesamount).
On Insert, if the transaction is a pledge, expectancy, match pledge, match potential, credit, match credit, or honor transaction ('p', 'e', mp', 'o', 'c', 'a', 'h'), set the deductible and nondeductible amounts to 0 (duesdeduct and duesnonded). (Regardless of data in insert form.)
On Insert, if the transaction is NOT a pledge, expectancy, match pledge, match potential, credit, match credit, or honor transaction ('p', 'e', mp', 'o', 'c', 'a', 'h'), accept the data in the insert form for the deductible amount. If none entered, set deductible amount equal to duesamount and recalculate nondeductible amount (duesdeduct and duesnonded).
On Insert, if the transaction is NOT a pledge, expectancy, match pledge, or match potential ('p', 'e', mp', 'o'), set the dues amount pledged (duesamtplg) equal to 0.

On Insert, copy the following fields from the specified Chart of Accounts entry to the corresponding Dues columns:

Chart of Accounts Field	Dues Table Field
chart_acct	duesacctno
chart_cfae	duesacctcf
chart_code	duesrest
chart_dept	duesacctid
chart_div	duesacctdv
chart_goal	duesacctgl
chart_grp	duesacctgp
chart_ntfy	duesacctnt
chart_val	duesacctnm

On Insert, copy the value from the specified Solicitations lookup table entry (solicitations.sol.val) to the duessolnam field.
On Insert, if the account is an 80/20 account (chart_qu20 = 'Y'), a linked Quid Pro Quo row is created. The value of the quid is based on whether the quid was accepted or declined. If declined, the value of the quid is set to 0 (quidvalue). If it was accepted, the value of the quid is calculated as 20% of the deductible amount (duesdeduct) of the transaction. The non-deductible amount of the transaction (duesnonded) is recalculated.
On Insert, if the duescorres field contains a correspondence name, the trigger will create a linked Correspondence row, filling in the fields corrid,corrname,corrlnkfil,and corrlnkkey.
On Insert, if the insert transaction is an allocation that is linked to a master allocation, (duesallkey = **dueskey of master allocation**) the trigger adjusts the following amount fields in the master transaction and all other allocations that are linked to it: duesamount, duesamtplg, duesasset, duesdeduct, duesnonded, duesnetpv, duestasset, duestdeduc, duestnonde, and duestnetpv.
On Insert, if the transaction is a pledge (and therefore a payment schedule is needed), the trigger uses the following information that was inserted in the following system-use fields: duesplgtyp, duespaycnt, duespstrtm, duespstrty. That information is used to create the appropriate number of linked rows in the Payment table, the appropriate interval between payments and the start month and year. If the dues type is match pledge ('mp'), or if the duesplgtyp is not recognized as a standard, or if either the payment count or start date is left blank on data entry, a single Payment row is created for the entire amount.
On Insert, if the transaction is a pledge payment, a bequest, or a credit ('y', 'b', 'c') that is applied to a master transaction with a type of match pledge or match potential, the stored procedure, process_match_payments is called. This procedure adjusts the amounts in the linked Payment table rows to reflect the payment, it decreases the original transaction's balance amounts, if the payment is an overpayment, it increases the total amount, and adjusts any allocations for the original transaction accordingly.
On Insert, if the transaction is a pledge payment, a bequest, or a credit ('y', 'b', 'c') that is applied to a master transaction with a type other than match pledge or match potential, the stored procedure, process_payments is called. This procedure adjusts the amounts in the linked Payment table rows to reflect the payment, it decreases the original transaction's balance amounts, if the payment is an overpayment, it increases the total amount, and adjusts any allocations for the original transaction accordingly. If the original transaction has linked transactions (joint, credit for other, honor), it performs the same for those transactions.
On Insert, if transaction is on behalf of another constituent, the trigger checks the duesgiveid to find the id of the constituent for whom it will create a transaction with a type of dues. Also, it changes the dues type of the transaction for the credited constituent to credit and sets the dueshnrmkr equal to 'B'.
On Insert, if the transaction is a match pledge or match potential, the duesmtchky is set equal to the dueskey of the transaction that is being matched. Also, the duesmtchky field in the original transaction (which holds a count of the linked match transactions) is increased by 1.
On Insert, if the transaction is a credit for other, the trigger checks the duescredid for the id of the constituent for whom it will create the credit transaction, setting the duescrdkey equal to the dueskey of the original transaction. Also, the duescrdkey field in the original transaction (which holds a count of the linked credit transactions) is increased by 1.
On Insert, if the transaction is a credit for the same constituent, the trigger will set the duessumkey equal to the dueskey in the original transaction.
On Insert, if the transaction is a match pledge payment that also pays down the donor's pledge, the trigger sets the duesplgkey of the payment transaction equal to the dueskey of the match pledge transaction. Also, the trigger creates a credit transaction with the pledge key equal to the dueskey of the donor's original pledge, with the duesmrcvd set to 'M', and the duescrdkey equal to the dueskey of the match pledge payment transaction.
On Insert, if a transaction is a pledge payment or bequest that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the duesplgkey, the trigger will add the amount of the transaction to duesttlpay and increase duescntpay by 1.
On Insert, if a transaction is a credit that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the duesplgkey, the trigger will add the amount of the transaction to duesttlcrd and increase duescntcrd by 1.
On Insert, if the transaction is a dues, pledge payment, bequest, pledge, sustainer pledge, or expectancy ('g',' y', 'b', 'p', 'sp', 'e') and the transaction is the only allocation or the first (primary) allocation of a multiple-allocation transaction and duesplgkey < 5000, the trigger searches the coverage table and identifies all rows where covid is equal to the duesid, and covisa = 'cover', and covcamp is blank or equal to duescamp, and the covcampyr is blank or equal to the duescampyr, and the covstrtdat is blank or is on or before the computer's system date, and the covstopdat is blank or is on or after the computer's system date. For every coverage row that meets the search criteria, the trigger will insert a credit for other ('c') transaction for the covcovid constituent.
On Insert or Update, if there is a solicitor for the transaction, the trigger will check the duessolid field for the id number of the constituent to be identified in the Solicitor row that will be created (by the trigger). On Update, the trigger will check for a change to the duessolid field to determine whether an additional Solicitor row is to be created.
On Insert or Update, if the transaction is joint the trigger will check the duesjntid field for the id of the constituent to whom the joint transaction will be created (by the trigger). The duesjntkey is set equal to the dues key of the original transaction. Also, the original transaction, the duesjntkey is set equal to 1.
On Insert or Update, if the transaction is in honor or memory of a constituent, the trigger will check the dueshonrid for the id number of another constituent. If found, it creates an honor transaction ('h') for that constituent. The dueshnrmkr is set to 'M' if a death row exists for the honoree constituent. The dueshnrkey in the honor transaction is set equal to the dueskey of the original and in the original the dueshnrkey is increased by 1. Once the honor transaction is created, the system will set the dueshonrid field to Null.
On Insert or Update, if the transaction is in honor or memory of a non-constituent, a linked Honoree row is created (honlinkfil and honlinkkey).
On Insert or Update, if the transaction has a linked credit for another constituent, the trigger will check the duescredid field for the id number of the credited constituent, and will create a credit transaction for the credited constituent. During an update, the trigger will check the duescredid field for changes. If found, a new credit transaction is created for that constituent. The duescrdkey is set equal to the dueskey for the original transaction and in the original transaction, the duescrdkey is increased by 1.
On Insert or Update, if the transaction has linked joint, credit or honor transactions and if that transaction has a match pledge added to it, the trigger will create a match pledge credit transaction, linked to the joint, credit or honor transaction.
On Update, if a transaction is a journal it is linked to the original transaction (set the duesjnlkey = dueskey).
On Update, if a transaction is a journal of a transaction that is a pledge payment or bequest that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the duesplgkey, the trigger will subtract the amount of the original transaction from duesttlpay and decrease duescntpay by 1.
On Update, if a transaction is a journal of a transaction that is a credit that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the duesplgkey, the trigger will subtract the amount of the original transaction from duesttlcrd and decrease duescntcrd by 1.
On Update, if a transaction is a write-off against a pledge, match pledge, or expectancy ('p', 'mp', 'e'), the duestype is set to 'w'.
On Update, if the transaction is a write-off against a pledge, match pledge, expectancy, sustainer pledge, pledge potential, or pledge credit ('p','mp','e','sp','t','r') the trigger will increase duesttlwrt by the write-off amount, and increase duescntwrt by 1.
On Delete, check that the batch for the transaction is open (dues_batch_number.table_type = 'open'). If not, an error message is given.
On Delete, if transaction is a pledge, all linked pledge payments are also deleted.
On Delete, all linked Quid Pro Quo rows are also deleted.
On Delete, if the transaction is a pledge payment linked to a pledge, the pledge transaction's amounts are adjusted by the appropriate amount and the linked Payment schedule rows are adjusted by the appropriate amounts.
On Delete, if the transaction is a writeoff, the amounts in the pledge transaction and any linked allocations are adjusted by the appropriate amounts and the linked Payment schedule rows are adjusted by the appropriate amounts.
On Delete, if the transaction is a match pledge or a match credit ('mp', 'a'), the total amounts on the original donor's transaction are adjusted.
On Delete, if there are linked transactions or multiple allocations, and if the deleted transaction is not the master, the duestotal and duestalloc are updated.
On Delete, if the transaction is a master allocation (duesallkey = duesallkey), all linked allocations are also deleted.
On Delete, if the transaction is a master transaction with linked payments or credits (dueskey = duessumkey), all linked transactions are also deleted.
On Delete, if a transaction is a pledge payment or bequest that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the duesplgkey, the trigger will subtract the amount of the transaction from duesttlpay and decrease duescntpay by 1.
On Delete, if a transaction is a credit that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the duesplgkey, the trigger will subtract the amount of the transaction from duesttlcrd and decrease duescntcrd by 1.
On Delete, if a transaction is a write-off for a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r') , the trigger will reduce duesttlwrt by the write-off amount and decrease duescntwrt by 1.

Education (see School)

Employment (see Jobs)

Financial

This data table uses only the standard triggers.

Gift in Kind

The standard triggers rules and the linking triggers rules apply to this table.

Giving

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

On Insert, check that the batch is open (gift_batch_number.table_type = 'open'). If not, an error message is generated.
On Insert, set the gift's constituent type (giftconst) equal to the constituent type from the Basic Data row (coreconst).
On Insert, set the transaction number (gifttrnum) to the next transaction number shown for that user in the userlist.
On Insert, set the exchange rate (giftexrate) equal to the rate taken from the Currency lookup table entry that was entered for the currency field (giftcurr).
On Insert, if the Campaign is left blank (giftcamp), do the following in the order given:
- Check the Solicitation lookup table entry that was specified in the solicitation field (giftsolic). If the campaign mandatory flag in that lookup table entry is 'Y' (solicitation.sol_mand), then set the gift's campaign and year (giftcamp and giftcampyr) equal to the solicitation's campaign and year (sol_camp and sol_campyr). The year values must be a four-digit year (yyyy) between 1600 and 2200.
- If above is not true (solicitation.sol_mand = 'N'), then check the Chart of Accounts entry that was specified for the gift. If the campaign mandatory flag in that lookup entry is 'Y' (chart_of_accounts.chart_mand), then set the gift's campaign (giftcamp) equal to the account's campaign and year(chart_camp).
On Insert, if the transaction is without links or allocations, set the giftallkey = giftkey and set the giftsumkey = giftkey.
On Insert, if the transaction is a match pledge, match potential, expectancy, pledge potential, or sustainer pledge ('mp', 'o', 'e', 't', 'sp'), set the conditional flag to 'Y' (giftrevoke).
On Insert, if the transaction is a pledge, expectancy, match pledge or match potential, pledge potential, or sustainer pledge ('mp', 'o', 'e', 't', 'sp'), and the conditional flag is 'Y' (giftrevoke), then set the asset and net present value amounts to 0 (giftasset and giftnetpv).
On Insert, if the transaction is a pledge ('p'), and the conditional flag is 'N' (giftrevoke), then set the asset and net present value amounts (giftasset and giftnetpv) to the gift amount (giftamount).
On Insert, if the transaction is a pledge, expectancy, match pledge, match potential, credit, match credit, or honor transaction ('p', 'e', mp', 'o', 'c', 'a', 'h'), set the deductible and nondeductible amounts to 0 (giftdeduct and giftnonded). (Regardless of data in insert form.)
On Insert, if the transaction is NOT a pledge, expectancy, match pledge, match potential, credit, match credit, or honor transaction ('p', 'e', mp', 'o', 'c', 'a', 'h'), accept the data in the insert form for the deductible amount. If none entered, set deductible amount equal to giftamount and recalculate nondeductible amount (giftdeduct and giftnonded).
On Insert, if the transaction is NOT a pledge, expectancy, match pledge, or match potential ('p', 'e', mp', 'o'), set the gift amount pledged (giftamtplg) equal to 0.

On Insert, copy the following fields from the specified Chart of Accounts entry to the corresponding Gift columns:

Chart of Accounts Field	Giving Table Field
chart_acct	giftacctno
chart_cfae	giftacctcf
chart_code	giftrest
chart_dept	giftacctid
chart_div	giftacctdv
chart_goal	giftacctgl
chart_grp	giftacctgp
chart_ntfy	giftacctnt
chart_val	giftacctnm

On Insert, copy the value from the specified Solicitations lookup table entry (solicitations.sol.val) to the giftsolnam field.
On Insert, if the account is an 80/20 account (chart_qu20 = 'Y'), a linked Quid Pro Quo row is created. The value of the quid is based on whether the quid was accepted or declined. If declined, the value of the quid is set to 0 (quidvalue). If it was accepted, the value of the quid is calculated as 20% of the deductible amount (giftdeduct) of the transaction. The non-deductible amount of the transaction (giftnonded) is recalculated.
On Insert, if the giftcorres field contains a correspondence name, the trigger will create a linked Correspondence row, filling in the fields corrid,corrname,corrlnkfil,and corrlnkkey.
On Insert, if the insert transaction is an allocations that is linked to a master allocation, (giftallkey = **giftkey of master allocation**) the trigger adjusts the following amount fields in the master transaction and all other allocations that are linked to it: giftamount, giftamtplg, giftasset, giftdeduct, giftnonded, giftnetpv, gifttasset, gifttdeduc, gifttnonde, and gifttnetpv.
On Insert, if the transaction is a pledge (and therefore a payment schedule is needed), the trigger uses the following information that was inserted in the following system-use fields: giftplgtyp, giftpaycnt, giftpstrtm, giftpstrty. That information is used to create the appropriate number of linked rows in the Payment table, the appropriate interval between payments and the start month and year. If the gift type is match pledge ('mp'), or if the giftplgtyp is not recognized as a standard, or if either the payment count or start date is left blank on data entry, a single Payment row is created for the entire amount.
On Insert, if the transaction is a pledge payment, a bequest, or a credit ('y', 'b', 'c') that is applied to a master transaction with a type of match pledge or match potential, the stored procedure, process_match_payments is called. This procedure adjusts the amounts in the linked Payment table rows to reflect the payment, it decreases the original transaction's balance amounts, if the payment is an overpayment, it increases the total amount, and adjusts any allocations for the original transaction accordingly.
On Insert, if the transaction is a pledge payment, a bequest, or a credit ('y', 'b', 'c') that is applied to a master transaction with a type other than match pledge or match potential, the stored procedure, process_payments is called. This procedure adjusts the amounts in the linked Payment table rows to reflect the payment, it decreases the original transaction's balance amounts, if the payment is an overpayment, it increases the total amount, and adjusts any allocations for the original transaction accordingly. If the original transaction has linked transactions (joint, credit for other, honor), it performs the same for those transactions.
On Insert, if transaction is on behalf of another constituent, the trigger checks the giftgiveid to find the id of the constituent for whom it will create a transaction with a type of gift. Also, it changes the gift type of the transaction for the credited constituent to credit and sets the gifthnrmkr equal to 'B'.
On Insert, if the transaction is a match pledge or match potential, the giftmtchky is set equal to the giftkey of the transaction that is being matched. Also, the giftmtchky field in the original transaction (which holds a count of the linked match transactions) is increased by 1.
On Insert, if the transaction is a credit for other, the trigger checks the giftcredid for the id of the constituent for whom it will create the credit transaction, setting the giftcrdkey equal to the giftkey of the original transaction. Also, the giftcrdkey field in the original transaction (which holds a count of the linked credit transactions) is increased by 1.
On Insert, if the transaction is a credit for the same constituent, the trigger will set the giftsumkey equal to the giftkey in the original transaction.
On Insert, if the transaction is a match pledge payment that also pays down the donor's pledge, the trigger sets the pledge key of the payment transaction equal to the giftkey of the match pledge transaction. Also, the trigger creates a credit transaction with the pledge key equal to the giftkey of the donor's original pledge, with the giftmrcvd set to 'M', and the giftcrdkey equal to the giftkey of the match pledge payment transaction.
On Insert, if the transaction is a gift, pledge payment, bequest, pledge, sustainer pledge, or expectancy ('g',' y', 'b', 'p', 'sp', 'e') and the transaction is the only allocation or the first (primary) allocation of a multiple-allocation transaction and the giftplgkey < 5000, the trigger searches the coverage table for all rows where covid is equal to the giftid, and covisa = 'cover', and covcamp is blank or equal to giftcamp, and the covcampyr is blank or equal to the giftcampyr, and covstrtdat is blank or is on or before the computer's system date, and covstopdat is blank or is on or after the computer's system date. For every coverage row that meets the search criteria, the trigger will insert a credit for other ('c') transaction for the covcovid constituent.
On Insert or Update, if there is a solicitor for the transaction, the trigger will check the giftsolid field for the id number of the constituent to be identified in the Solicitor row that will be created (by the trigger). On Update, the trigger will check for a change to the giftsolid field to determine whether an additional Solicitor row is to be created.
On Insert or Update, if the transaction is joint the trigger will check the giftjntid field for the id of the constituent to whom the joint transaction will be created (by the trigger). The giftjntkey is set equal to the gift key of the original transaction. Also, the original transaction, the giftjntkey is set equal to 1.
On Insert or Update, if the transaction is in honor or memory of a constituent, the trigger will check the gifthonrid for the id number of another constituent. If found, it creates an honor transaction ('h') for that constituent. The gifthnrmkr is set to 'M' if a death row exists for the honoree constituent. The gifthnrkey in the honor transaction is set equal to the giftkey of the original and in the original the gifthnrkey is increased by 1. Once the honor transaction is created, the system will set the gifthonrid field to Null.
On Insert or Update, if the transaction is in honor or memory of a non-constituent, a linked Honoree row is created (honlinkfil and honlinkkey).
On Insert or Update, if the transaction has a linked credit for another constituent, the trigger will check the giftcredid field for the id number of the credited constituent, and will create a credit transaction for the credited constituent. During an update, the trigger will check the giftcredid field for changes. If found, a new credit transaction is created for that constituent. The giftcrdkey is set equal to the giftkey for the original transaction and in the original transaction, the giftcrdkey is increased by 1.
On Insert or Update, if the transaction has linked joint, credit or honor transactions and if that transaction has a match pledge added to it, the trigger will create a match pledge credit transaction, linked to the joint, credit or honor transaction.
On Insert, if a transaction is a pledge payment or bequest that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the giftplgkey, the trigger will add the amount of the transaction to giftttlpay and increase giftcntpay by 1.
On Insert, if a transaction is a credit that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the giftplgkey, the trigger will add the amount of the transaction to giftttlcrd and increase giftcntcrd by 1.
On Update, if a transaction is a journal it is linked to the original transaction (set the giftjnlkey = giftkey).
On Update, if a transaction is a journal of a transaction that is a pledge payment or bequest that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the giftplgkey, the trigger will subtract the amount of the original transaction from giftttlpay and decrease giftcntpay by 1.
On Update, if a transaction is a journal of a transaction that is a credit that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the giftplgkey, the trigger will subtract the amount of the original transaction from giftttlcrd and decrease giftcntcrd by 1.
On Update, if a transaction is a journal of pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r') write-off, the trigger will reduce giftttlwrt by the write-off amount and decrease giftcntwrt by 1.
On Update, if a transaction is a write-off against a pledge, match pledge, or expectancy ('p', 'mp', 'e'), the trigger will set the gifttype to 'w'.
On Update, if the transaction is a write-off against a pledge, match pledge, expectancy, sustainer pledge, pledge potential, or pledge credit ('p','mp','e','sp','t','r') the trigger will increase giftttlwrt by the write-off amount, and increase giftcntwrt by 1.
On Delete, check that the batch for the transaction is open (gift_batch_number.table_type = 'open'). If not, an error message is given.
On Delete, if transaction is a pledge, all linked pledge payments are also deleted.
On Delete, all linked Quid Pro Quo rows are also deleted.
On Delete, if the transaction is a pledge payment linked to a pledge, the pledge transaction's amounts are adjusted by the appropriate amount and the linked Payment schedule rows are adjusted by the appropriate amounts.
On Delete, if the transaction is a write-off, the amounts in the pledge transaction and any linked allocations are adjusted by the appropriate amounts and the linked Payment schedule rows are adjusted by the appropriate amounts.
On Delete, if the transaction is a match pledge or a match credit ('mp', 'a'), the total amounts on the original donor's transaction are adjusted.
On Delete, if there are linked transactions or multiple allocations, and if the deleted transaction is not the master, the gifttotal and gifttalloc are updated.
On Delete, if the transaction is a master allocation (giftallkey = giftallkey), all linked allocations are also deleted.
On Delete, if the transaction is a master transaction with linked payments or credits (giftkey = giftsumkey), all linked transactions are also deleted.
On Delete, if a transaction is a pledge payment or bequest that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the giftplgkey, the trigger will subtract the amount of the transaction from giftttlpay and decrease giftcntpay by 1.
On Delete, if a transaction is a credit that links to a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), through the giftplgkey, the trigger will subtract the amount of the transaction from giftttlcrd and decrease giftcntcrd by 1.
On Delete, if a transaction is a write-off for a pledge, match pledge, expectancy, sustainer pledge, pledge potential or pledge credit ('p','mp','e','sp','t','r'), the trigger will subtract the write-off amount from giftttlwrt and decrease giftcntwrt by 1.

Honoree

The standard triggers rules and the linking triggers rules apply to this table.

Internet Address

The standard triggers rules and the linking triggers rules apply to this table.

Jobs

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

On Insert or Update, if the company is another constituent (jobscpnyid > '!') and the status is active (jobsstatus = 'act') then create Relationship rows for both constituents with types of employee/employer ('emp'/'boss') or the reverse.
On Insert or Update, if the company is another constituent (jobscpnyid > '!') and the status is active (jobsstatus = 'act'), and if the group from the company's constituent type is either 'C' or 'F', then update the known employee (corekwnemp) in the company's Basic Data row and if the original constituent has a CAE type of 'A', also update the alumni employee count (corealmemp) in the company's Basic Data row.
On Insert or Update, if the company is another constituent (jobscpnyid > '!') and the status is no longer at this position (jobsstatus = 'no') then create Relationship rows for both constituents with types of former employee/former employer ('femp'/'fbos') or the reverse.
On Insert or Update, if the company is another constituent (jobscpnyid > '!') and the status is ret (jobsstatus = 'ret') then create Relationship rows for both constituents with types of former employee/former employer ('femp'/'fbos') or the reverse.
On Insert or Update, if the company is another constituent (jobscpnyid > '!'), check the first match ratio in the company's Basic Data. If the ratio is > 0.0, set the match flag (jobsmflag) equal to 'Y'.
On Insert or Update, set the industry (jobsindust) = the industry field in the company's Basic Data row (coreindust).
On Insert or Update, call the stored procedure name_firstrow to get the company's name (jobsconame).
On Insert or Update, if an address is linked, set the addrlnkjob = jobskey.
On Update, if Address rows are unlinked from the Employment row, change the addrlnkjob from the previously linked Address rows to 0.
On Delete, if there are linked Relationship rows (jobsrelky1 > 0) then delete those linked Relationships.
On Delete, if the status was active (jobsstatus = 'act') and the company's constituent type group was 'C' or 'F', update the known employee count in the company's Basic Data row (corekwnemp). Also, if the original constituent's CAE type (corecfae) is alumni ('A'), update the company's alumni employee count (corealmemp).

Media

This data table uses only the standard triggers.

Membership

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

Calls the stored procedure name_firstrow using the membgiveid to get the first name row and then populates the name fields in the membership row.

Names

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

On Insert or Update, if the name type is spouse ('sp'), update the Basic Data marital status to married (coremaritl = 'm').
If any of the following fields are inserted or updated: title, first, middle, or last name, suffix, any of the name override flags (nametitle, namefirst, namemiddle, namelast, namesuffix, nameflag1, nameflag2, nameflag3, nameflag4), or if the plural name (nameplural) is blank, then do all of the following:
- Update the formatted name (nameformn).
- Call the stored procedure name_plural to determine if there is a spouse row with the same name type.
- If none is found and the plural name override flag is 'N' (nameflag2), then set the override flag equal to the override flag of the formatted name (nameflag1) and set the plural name (nameplural) equal to the formatted name (nameformn).
- If a spouse name of the same type is found and the plural override flag is 'N', then fill in the plural name as created by the stored procedure, for both the original constituent and the spouse.
- If its override flag is 'N' (nameflag3), fill in the salutation name (namesalut) as created by the stored procedure name_sal.
- Call the stored procedure name_salplural to determine if there is a spouse row with the same name type.
- If none is found and the plural salutation name override flag is 'N' (nameflag4), then set the override flag equal to the override flag of the salutation name (nameflag3) and set the plural salutation name (namecsalut) equal to the salutation name (namesalut).
- If a spouse name of the same type is found and the plural salutation override flag is 'N', then fill in the plural name as created by the stored procedure, for both the original constituent and the spouse.
- Fill in the soundex name (namesndex) as generated by the soundex function.
- Call the stored procedure name_firstrow. If this row is determined to be the first row, update the name as stored in the following table rows: Relationships, Solicitor, Correspondence, Employment, and Events.
On Delete, call the stored procedure name_plural to determine if there is a spouse row with the same name type. If one is found, set the plural name equal to blank (to force recalculation of the plural name.)
On Delete, if the deleted name was the first name row, call the stored procedure name_firstrow to calculate a new first name. Update the name as stored in the following table rows: Relationships, Solicitor, Correspondence, Employment, and Events.

Nationality

This data table uses only the standard triggers.

Payments

This data table uses only the standard triggers.

PG Rating

The standard triggers rules and the linking triggers rules apply to this table.

Phone Numbers

This data table uses only the standard triggers.

Planned Giving

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

On Insert, copy the following fields from the specified Chart of Accounts entry to the corresponding Planned Giving columns:

Chart of Accounts Field	Planned Giving Table Field
chart_acct	planacctno
chart_cfae	planacctcf
chart_code	planrest
chart_dept	planacctid
chart_goal	planacctgl
chart_grp	planacctgp
chart_ntfy	planacctnt
chart_val	planacctnm

Proposals

The standard triggers rules and the linking triggers rules apply to this table, along with the following:
On Insert, copy the following fields from the specified Chart of Accounts entry to the corresponding Proposals columns:
Chart of Accounts Field
Proposal Table Field
chart_acct
propacctno
chart_cfae
propacctcf
chart_code
proprest
chart_dept
propacctid
chart_goal
propacctgl
chart_grp
propacctgp
chart_ntfy
propacctnt
chart_val
propacctnm

Chart of Accounts Field	Proposal Table Field
chart_acct	propacctno
chart_cfae	propacctcf
chart_code	proprest
chart_dept	propacctid
chart_goal	propacctgl
chart_grp	propacctgp
chart_ntfy	propacctnt
chart_val	propacctnm

Quid pro quo

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

On Insert, if the value of the is left as 0, fill in the amount from the amount 1 field in the chosen Quid Items lookup table entry. If the accepted flag (quidaccept) is 'Y', then subtract the quid value (quidvalue) from the deductible amount of the gift (giftdeduct) and recalculate the nondeductible amount (giftnonded) for the Gift row to which the Quid is linked.
On Update, if the quid accepted flag (quidaccept) is changed from 'N' to 'Y', subtract the quid value (quidvalue) from the deductible amount of the gift (giftdeduct) and recalculate the nondeductible amount (giftnonded) for the Gift row to which the Quid is linked.
On Update, if the quid accepted flag (quidaccept) is changed from 'Y' to 'N', add the quid value (quidvalue) to the deductible amount of the gift (giftdeduct) and recalculate the nondeductible amount (giftnonded) for the Gift row to which the Quid is linked.
On Update, if the quid type is changed, set the quid value equal to the amount 1 field in that entry in the quid items lookup table. (This will affect all future Quid Pro Quo rows that are created, not existing Quid data.)
On Delete, if the quid accept flag is 'Y', add the quid value to the deductible amount of the linked Gift row (giftdeduct), and recalculate the nondeductible amount (giftnonded).

Rating

This data table uses only the standard triggers.

Receipts

The standard triggers rules and the linking triggers rules apply to this table.

Relationships

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

On Insert or Update, if the relationship type (relisa) is one of the following, set the reverse relationship (relwhose) equal to the same type: spouse, spouse no mail, life partner, life partner no mail, divorced spouse ('sp', 'spnm', 'lp', 'lpnm', 'dvsp').
On Insert or Update, if the relationship type (relwhose) is one of the following, set the reverse relationship (relisa) equal to the same type: spouse, spouse no mail, life partner, life partner no mail, divorced spouse ('sp', 'spnm', 'lp', 'lpnm', 'dvsp').
On Insert or Update, set the relationship group fields (relisagrp and relwhogrp) for each of the two relationship type fields (relisa and relwhose) in accordance with the entry each uses from the Relationship Type lookup table.entries.
On Insert or Update, if the relationship type (relisa) is spouse or spouse no mail ('sp' or 'spnm') then set the marital status in the Basic Data row (coremaritl) for both spouses equal to married ('m').
On Insert or Update, if the relationship type (relisa) is divorced spouse ('dvsp') then set the marital status in the Basic Data row (coremaritl) for both spouses equal to divorced ('d').
On Insert or Update, execute the stored procedure name_firstrow to update the relationship row with the appropriate name pieces for the second constituent's name.
On Insert or Update, create or update the mirror row for the second constituent.
On Insert Update, if the relationship type (relisa) is spouse, life partner or combined mail ('sp', 'lp', 'cm'), update the nameplural in the Name rows.
On Delete, if the relationship type (relisa) is spouse, life partner or combined mail ('sp', 'lp', 'cm'), set the plural name override flag is 'N', and update the nameplural in the Name rows to ' ' for both constituents. (This will force the recalculation of the plural names.)

Research Sources

The standard triggers rules and the linking triggers rules apply to this table.

School

The standard triggers rules and the linking triggers rules apply to this table.

Screening

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

The creation of a screener row also creates a 'mirror' row for the screenee.
Updating either side of a screening row also updates the 'mirror' row.

Securities

The standard triggers rules and the linking triggers rules apply to this table.

Solicitor

The standard triggersrules and thelinking triggers rules apply to this table, along with the following:

Calls the stored procedure name_firstrow using the gsolsolid to get the first name row and populate the name fields in the solicitor row.

Subscription

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

Sets the subissues (number of issues) equal to the publication_name.table_amt1 (publication name lookup table field amount 1).

Summaries

This data table uses only the standard triggers.

Tasks

The standard triggers rules and the linking triggers rules apply to this table, along with the following:

taskcompfl is set to 'Y' if a taskcompdt (Task Completed Date) is filled in and set to 'N' if it is blank.
If all Tasks for the parent Action row have been completed, then the actcompdat is set to the maximum taskcompdt (the latest Task Completed Date). If another Task is added after all other Tasks for the parent Action row have been completed, the triggers will not update the actcompdat when this added Task is completed.

Tracking

The standard triggers rules and the linking triggers rules apply to this table.

Triggers on the Millennium System Tables

Alerts

On Insert and Update, if the alertactv is set to 'Y, set alertactv on all other alert rows to 'N'.

Top of Page