Daily-Dev-Notes

Daily-Dev-Notes-Old
Daily-Dev-Notes-Old

Archived-Daily-Dev-Notes

⁠

2023-11-18

Need proc to identify duplicate table-keys and update them automatically

sib-table-setup created dup for the key of docs

ONGOING

Scheduled Auto-Actions -

we have to set them up differently to loop through all of the matching recs

so a separate “action_sql_scheduled”

how is @trigger_name set (should we exclude this var for scheduled-trigger-type) and @triggered_by

Conclusion: No, because every action is its own proc so it has this param and it will be passed in

Test Scheduled Auto-Action for sending an email

? How do we set the

Use-Case: Send Email when Date-Closed over 1 year ago (for “archived case notification”)

Checklist-Section-Name

Every table that is used as a Checklist-Section Target-Table must have a Ref-Field/Name-Field defined if the default “name” column is not used

Checklist-Section-Triggers

Adding Checklist Sections is the heart of the Checklists Functionality

The Checklist-Section-Config essentially contains a “shortcut” to creating an auto-action

so its exactly the same as manually setting up an auto-action that enters a checklist section

is_exec_multi

the key to having the checklist section update retroactively

but most shouldn’t be

the action_history links up the checklist_section created to the config

so its really clear

Checklists-Triggers

for each checklist where we have dynamic fields (like responsible-user-from-field or due-date)

we setup a linked auto-action(s)

since the responsible-user-from-field should update all of the fields using it,

setup just one auto-action per user-field

the action-sql is to update all of the field_meta that use this field_id

(responsible) user_field_id

due_date

is_reupdate / is_exec_multi - option for the trigger fields in the checklists

NOT-is_delayed

Update all fields that ref “fields” (10) to list-long (instead of list) (JIC)

How are we handling multi-val-selects for the criteria and “NOT IN”

like case_type_id in (1,2,3)

Needed for the Auto-action-trigger-fields Criteria Check

p_field_edit

is_index - to create an index on this field easily

go back through all of our tables setup and create indexes on the fields that we know are used heavily

Nvarchar fields instead of varchar (some script to update all of these)

@subject varchar(1024) = null, @body varchar(max) = null,

email addresses

name

abbrev

memo

hint

import

select * from DaySec

⁠

2023-11-17

p_action_history_create included in the zap_[id]

spErr_FromSqlProc_Wrt - use 'log' table to store the dynamic sql execution errors and other fatal/imp errors in the DB that are caught

create the proc automatically via our magic-gen after setting it up with all of the fields

should pass the @zap_level to the zap_[action.id]

zap_ procs need to print their name + date-time at the top (when is_print)

for troubleshooting

if(@is_print = 1) print concat('exec zap_docs @rec_id = ', @rec_id, getutcdate())

actions - insert record

setup duplicate-checking by the field vals we are inserting

actions test where doc received date will add a new doc record with the same type and due-date 1 year from received date

Setup actions test where updating a doc record will update the case field

like doc-received-date where doc_type_id = 1 to update the case - application_received_date

test zap_5

by entering the “case stage

test with using the “+ 5-biz-days” as the val for the due-date

Checklist - Add Section Auto-Action

⁠

Scheduled-Auto-Actions

Should have a start and an end date to make it easier to scope

Loop through all of the scheduled ones by the “next-check-date” or something like that

(actions.date_run_next) (where <= current-date)

⁠

2023-11-13

trigger_history_fields - val has to be in single-quotes - since its an nvarchar col

trigger_history_fields doesn’t work when there are multiple criteria since there would be two separate trigger_history_fields

select * from trigger_history_fields where db_col = 'type_id' and val = 1 and is_updated = 1 and db_col = 'date_received' and val is not null and is_updated = 1 - Has to be like this: declare @trigger_history_id int = 12 select * from trigger_history_fields where trigger_history_id = @trigger_history_id and ( (db_col = 'type_id' and val = 1 and is_updated = 1) or (db_col = 'date_received' and val is not null and is_updated = 1) )

db_col needs the []

actions - insert record

When insert-record, then we are using the parent_rec_id (so the rec_id should actually be null and be set as an output param)

left join docs as t_1044 on (t_1043.id = t_1044.id) --- this should be t_1044.case_id instead

X - view_tables.is_exclude when we

the for_table_id is for the record being inserted

so, how do we structure the view so that we exclude this line

Essentially, how do we make it so that the we can set the table as the “for_table_id” but we exclude it from the view_tables - from-sql-string and we are building the criteria so its based on the id from the parent_table

maybe we just keep it simple and we have a different - “for_insert_table_id” which makes it much clearer and allows us to exclude it from the view_tables-sql but still use it as the table it is for

But then how do we select it’s fields because it has to be in the view_tables for us to select the fields we are going to insert

It’s like we need to a special way in view_tables to mark it

like view_tables.is_exclude (or rec_type_id) so it won’t be the table-sql-string

v_filters

select * from v_filters where filter_id = 21

⁠

t_null.date_due⁠

⁠

See why this is not working with our new sib-table (for docs under cases)

p_actions__test_insert_record

check that the “@date_closed” is being set now

need a test that pulls from another table’s val (so has to pull the view)

the auto-generated procs should have a signature at the top which includes the timestamp - to confirm that it was auto-gen at the point

setup a standard void for this for all the auto-gen

triggers

zap procs

zap_[id] - @is_print - what can we have outputted to help us with troubleshooting

if(@is_print = 1) print concat('update cases', 'id = ', @rec_id)

pass in @is_print_all to have it send to is_print var for

p_mod_create

p_mod_val_create

p_mod_rec_main_vals_create if(@is_print = 1) print concat('update cases id = ', @rec_id) zap_cases - if(@is_print = 1) print concat('loop - @trigger_history_id = ', @trigger_history_id)

trigger_history.exec_date should be timestamp

⁠

Email-Sending

pulls from the p_com_email_to_send_list

if from_view_id then will generate the Template and do the DataBinding

will check if there are view_fields.is_for_email_address and if so then loop through to create the unique list of TO, CC, BCC (including CC and BCC using the view_fields.email_address_type)

⁠

Exception-Handling

? how could we identify which auto-action is breaking the trigger?

we could have a special “debug-mode” where we create a new duplicate trigger but disable it (so is okay in the production environment)

we loop through all of the actions on the trigger and add only one and see if it executes successfully,

so if there are multiple actions with errors then we will catch all of them instead of just the first one

then we put an alert on the auto-action that it was disabled due to being broken

this alert would be auto-removed once they

⁠

action_version_id has to be set before we generate the action but ideally we only create a new action_version rec if the sql output actually changes, so maybe we could have a placeholder for {{action_version_id}}

⁠

Checklist-Sections Auto-Action

setup the proc for this

⁠

Use cases Setup⁠

⁠

Insert Doc Record when Case Closed Date is Entered

Trigger_History

insert the table_id and main_table_rec_id/table_id

? I think just ensure that this is working properly?

rec-creation is based around the “t.join_on_col”

so make sure these exist on all the tables where parent_table_id

Look through the SAM Auto-Actions code to make sure we aren’t missing anything

Setup test for “send email” when “Case Closed Date” is entered (not null)

for testing cascading triggers

need the filters-sql to be generated / (p_filters_sql called) after we create the filter-recs

Ensure that the “for_table_id” is used to select the “field-val-from” properly when we are inserted/updating fields on a different table

Weekly SQL Export Job and Weekly DB Backup

⁠

2023-11-11 ← 2023-10-30

Update Rec_Saver to call the p_zap_table for each of the tables with the @rec_id

exec p_filters_sql @filter_id = 18, @is_print = 1

was duplicating the criteria

??? why did we have 0 for the sec_id and sec_p_id

timestamp for trigger_history

Jira - p_filters_sql - needs extensive testing for the sections and section-groups

zap_4 ? where is the criteria for this (or do we not need it because we have it in the trigger)

Get_Filters_Sql() doesn’t seem to be working

it should be getting the ones where filter_type_id int = 31 --- action_filters

add comas to the

should p_com_edit include the p_zap part

in case we have triggers on the communications table

sProc.Append("exec p_zap_table @db_table = '").Append(table.db_table).Append("', @rec_id = @rec_id, @is_print = @is_print;").Append(Environment.NewLine);

the zap pseudo-trigger could end up triggering for other recs, so we need a way to pass in the IDs

Also, just calling zap_[table] at the end of each insert/update action can’t work so maybe we instead switch to a generic proc, like “zap_table” where we pass in the table/rec_id so it’s very scoped

Loop through the @t, should pull the exec_date to make sure that still valid zap_[db_table]

Setup Transactions for the trigger-History part?

for the zap_[table] part

so that if its called twice accidentally then won’t duplicate

? What about cascading-trigger ?

In the insert/update (zap) procs we have to call the zap_[table] right after the update

(or at the end of the of the zap_[table], so its recursive but shouldn’t need to be run more than once and can keep track of recursive level in there)

Use the “is_delayed_exec”

when we initially call the trigger then is_delayed_exec=1

have to wait until the zap is called manually

at the end of all the records being saved

for View_Mods

then if there are any inserts/updates from the triggers, we want them to fire right away

But to know whether its being executed from the trigger, we need to have a universal-column (on all tables) that is set to last_mod_user_id - 0 (or -1) to mark it as being modified by a trigger

isSelectFromViewTables

for getting the values from other tables

? Can a zap_[action.id] proc have multiple tables that we are updating or inserting?

No, so whatever the rec_id passed in to the zap is for the action.table_id

Which is based on the “view.table_id”

zap_[db_table]

is_delayed_exec

so call this proc from RecSaver if no view_id

or from the end of view_mods

if its not a sub-form

since should only be called from a parent-form as built-in functionality

Setup the Test for the actions

see use-cases

last_updated_user_id (or last_mod_id) - universal field on all tables for the auto-action triggers to use?

So RecSaver appends it to all tables

p_table_setup always adds it

? or do we just do the extra query for the mod-mains ?

? maybe also last_updated_datetime ? - which is a bit redundant to mod-mains but we could keep these fields hidden

would be super-helpful from a troubleshooting standpoint

use the same “@mod_id” for all of the triggers

? use a universal “last_mod_id” which exists on all of the tables

so all of the triggers would be part of the same mod rec

where in the actions do we specify the “contact_type_id”

how do we get the “triggered_by”

p_field_edit -

Drop and re-create the index when redoing the field types: alter table [cases] alter column [case_type_id] int null

re-run the “cases” data sql

Setup Scheduled Task for DB Backup

why is “case_closed_date” being included in the trigger when it doesn’t have the “is_action_trigger”

Separate mod_mains setups for each of the checklist sections and items, or just have a timestamp field (or last_mod_id) for troubleshooting

wrapping the procs with a try/catch and entering the errors into the action_history?

the zap_[id] proc wasn’t generated (for zap_4)

nor for the table

select distinct so.name/*, sc.text*/ from sysobjects so inner join syscomments sc on so.id = sc.id where so.name like 'zap%' order by

so.name⁠

⁠

actions-trigger code wasn’t generated

Calling the zap_[db_table]

add to the RecSaver?

see other notes below

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.

Daily-Dev-Notes-OldDaily-Dev-Notes-Old