php - MySQL Trigger create multiple records based on fields changed in SQL query? -

-

i have mysql trigger creates "task event log" record in db table when task record modified.

the event record trigger creates can specific "task edited" , need more advanced.

i need create separate event log records each task field modified.

if task title, description, , due date changed, need make 3 event records, 1 each field modified. "task due date modified"

is there way create separate event table records each task field "modified" using triggers?

the main reason using triggers have mass update task page allows mass update task records , there field values. allows drag , drop change there sort order , when sot order changes on records, not make taks modified date change unless real task fields changed well.

begin if not (new.date_modified <=> old.date_modified)             insert apoll_web_projects_events (event_type, project_id, task_id, created_by_user_id, description, date_created) values ('7', new.project_id, new.task_id, new.modified_user_id, new.name, utc_timestamp());     end if; end

enter image description here

here mysql query mass updates task records making sure update modified date field when changed field criteria met.

then trigger fires on records did meet criteria , updated date_modified field.

        insert             $this->tasksdbtablename(task_id, project_id, created_by_user_id, modified_user_id, name, description, status, priority, type, date_entered, date_modified, date_started, date_completed, date_due, milestone_id, assigned_user_id, sort_order, heading)         values             ($db->quote($taskid), '$projectid', '$created_by_user_id', '$modified_user_id', '$name', '$description', '$status', '$priority', '$type', utc_timestamp(), utc_timestamp(), '$date_started', '$date_completed', '$date_due', '$milestone_id', '$assigned_user_id', '$sort_order', '$heading')         on duplicate key update             date_modified = (case                 when name <> values(name)                 or description <> values(description)                 or status <> values(status)                 or type <> values(type)                 or priority <> values(priority)                   utc_timestamp()                   else date_modified             end),             modified_user_id='$modified_user_id',             name='$name',             description='$description',             status='$status',             priority='$priority',             type='$type',             date_started='$date_started',             date_completed='$date_completed',             date_due='$date_due',             milestone_id='$milestone_id',             assigned_user_id='$assigned_user_id',             sort_order='$sort_order',             heading='$heading'

in sql above, when criteria met make go ahead , update task record, there way have fire off sort of event create event record each field is updating?


Comments

Post a Comment

Popular posts from this blog

PHP DOM loadHTML() method unusual warning -

-
i had following code works fine on localhost: $document = new domdocument(); $document->loadhtml($p_result); $form = $document->getelementsbytagname('form')->item(0); // code continues using $form variable after same code updated on outside server, loadhtml() failed , issued warning. warning: domdocument::loadhtml() expects parameter 1 valid path, string given in path/name/to/script.php returned null instead of object code pretty gets fatal error. note contents of $p_result absolutely same on outside server , on localhost. but why displays kind of warning , why not work? doesn't loadhtml() expects argument 1 string in first place? why method expects parameter 1 valid path ? just make clear i'm not calling loadhtmlfile() , i'm calling loadhtml() . thanks. you're affected one of php bugs . issue present in php 5.6.8 , 5.6.9. have affected php version on server, , bug-free version on localhost. the bug forbids null
Read more

python - How to create jsonb index using GIN on SQLAlchemy? -

-
here's current code creating index jsonb. index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') but got error. sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint: must specify operator class index or define default operator class data type. [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] is there way create index on sqlalchemy? the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use: index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops',
Read more

c# - TransactionScope not rolling back although no complete() is called -

-
i'm using transactionscope rollback transaction fail bool errorreported = false; action<importerrorlog> newerrorcallback = e => { errorreported = true; errorcallback(e); }; using (var transaction = new transactionscope()) { foreach (importtaskdefinition task in taskdefinition) { loader.load(streamfile, newerrorcallback, task.destinationtable, processingtaskid); } if (!errorreported) transaction.complete(); } i'm sure there no transactionscope started ahead or after code. i'm using entity framework insert in db. regardless state of errorreported transaction never rolled in case of error. what missing ? transactionscope sets transaction.current . that's does. wants transacted must @ property. i believe ef each time connection opened reason. probably, connection open when scope installed. open connection inside of scope or enlist manually. ef has nasty "design decision": default opens
Read more