I've recently discovered a flaw in one of my nightly stored procedures which causes it to crash. This procedure was written to give Patron constituencies based on existing associations. The problem is that it will sometimes try to give a constituency of that particular type to someone who already had that constituency except that it's now inactive. The procedure is therefore crashing on a violation of PRIMARY KEY constraint, as it tries to add a new record of that customer_no and constituency (which together make the primary key for TX_CONST_CUST).
Bottom line, and possibly dumb question, I'm wondering if there's really any reason to keep inactive records in a TX table, since it seems that a lot of them don't do that (e.g. TX_CUST_KEYWORD). And, if so, it would seem that I could either delete all inactive records (e.g. where end_dt <= getdate()) of that constituency before I add them, or update the start_dt and end_dt for preexisting records accordingly.
Ideas?
Thanks, Tessiturians.
Unknown said: I've recently discovered a flaw in one of my nightly stored procedures which causes it to crash. This procedure was written to give Patron constituencies based on existing associations. The problem is that it will sometimes try to give a constituency of that particular type to someone who already had that constituency except that it's now inactive. The procedure is therefore crashing on a violation of PRIMARY KEY constraint, as it tries to add a new record of that customer_no and constituency (which together make the primary key for TX_CONST_CUST).
Use AP_MAINTAIN_CONSTITUENCY to add or reactivate the constituency. From the comments:
"Maintains constituencies in the TX_CONST_CUST table.
I (insert) - new row will be added to TX_CONST_CUST if not present.
if row is present, and inactive, the start data will be updated to today.
if row is present and active, no action will be taken."