How to update a record before delete


(marcantheunis) #1

hi,
for tracing reasons (trigger in database that works on delete) i need to first update the record that is about to be deleted…(user who is deleting)
now the question is what is the best approach for this in Data Abstract…
i don’t see how to solve this in a businessprocessor or is there a way?

tia,
Marc


(EvgenyK) #2

You can do this via Business Rules Scripting.
it can be something like

function beforeDelete(row) {
  if (row.name=="mytable") {
    lda.update("mytable1",{ CustomerID: "someID"}, {CompanyName: session["somevalue"]});
    lda.applyChanges();
  }
}

see more:


(marcantheunis) #3

ok…gonna look into it
thx!!


(marcantheunis) #4

this can’t be archieved using the businessprocessors server side in code (delphi)?


(EvgenyK) #5

it can be archived in code on server-side. we have bunch of events of BP and DAService that allow to do this.


(marcantheunis) #6

don’t see how…
before deleting a row client side i want to set a boolean in that rec
like:
datableintf.rec_harddeleted := true;
datableintf.post;
datable.delete;

so will the delta received server side contain the value set in rec_harddeleted?
and secondly, i don’t see the concept of localdataadapter in the server side businessrules in code (TDABusinessProcessorRules), so how to do the update of that rec before deletion?

thx
marc


(EvgenyK) #7

in OnBeforeProcessChange (server-side) you can update field value like

procedure TDataService.DABusinessProcessor1BeforeProcessChange(
  Sender: TDABusinessProcessor; aChangeType: TDAChangeType;
  aChange: TDADeltaChange; var ProcessChange: Boolean);
begin
  if (aChange.ChangeType = ctDelete) then begin
    // Connection.NewCommand('update table set fld = true where id = :myid', stSQL)
    Schema.NewCommand(Connection, 'MyCommand',['ID'],[somevalue]);
  end;
end;

for client-side, you can call command that updates value in table before deletion

procedure TClientDataModule.tbl_CustomersBeforeDelete(DataTable: TDADataTable);
begin
  DARemoteCommand1.Execute('MyCommand',['ID'],[somevalue]);
//  DALocalCommand1.Execute('MyCommand',['ID'],[somevalue]);
end;

or I miss something and you want to use something else?


(marcantheunis) #8

the whole idea is to capture if either the rec got deleted as an action in the program OR if the user forced the deletion
in the DB there is a trigger that dumps the rec in another table ondelete
now client side i know that the user will force delete, so i set a field of that rec indication just that
and then the delete command does it’s thing
now they can iterate over a bunch of recs and some of them will get deleted by design or by the user
there is only 1 applyupdates to give the user the possibility to back out of the changes…
problem is that the marker set on the rec does not get streamed to the server as far as i can see…
so i have no way of trapping it server side…

this also implies that i can’t use the beforedelete event client side cause that would probably set the wrong marker…


(marcantheunis) #9

so the test should be in my case:

if (aChange.ChangeType = ctDelete) and (field=harddelete)


(EvgenyK) #10

lets to specify: this marker (harddelete) was always set by client-side or not?
you can call a custom server method from client-side which will process this case (harddelete = true) or usual one (harddelete = false).

another way - you can create a dummy field in table and pass value from client to server in this field.
table can be created with dummy field like it was select :

select table.*, 1 as dummy from table

(marcantheunis) #11

always client side yes

if table.delete would not
lRecord^.CurrentChange.NewValues[i] := UnAssigned;
then i could process it in the onbeforeapplyupdates…
but now i don’t have a clue as soon .delete has been called…
so in order to archieve what i want i will need to implement a kind of softdelete on clientside which involves filtering and getting in between standard delphi controls to not let them actually delete a rec…
i hoped there was a way around this hassle…


(EvgenyK) #12
  • client-side can delete records in two modes: hard and soft.
  • if record is deleted in hard mode, it should be deleted from server too
  • if record is deleted in soft mode, it should be just not shown on client-side

is it correct?

if yes, you can use this workaround:

  • for hard mode - you actually deleted it from server in mode harddelete=true
  • for soft mode:
    • set LogChanges:=False
    • set some marker for record
    • set LogChanges:=True
    • filter table and show records for which marker isn’t set

(marcantheunis) #13

nah they both need to be deleted in the DB
i just want a trace so i now what was the reason for deletion (trigger ondelete which moves the data to other table)
so what does logchanges:=false do?
the change will not be in the dataset to be sent over right?

the easiest way is to revert from table.delete completely and have a deleted boolean field or so
but that requires rewriting everything that consumes this table…

i’m not aware of a soft delete mechanism built into DA or am i wrong?


(EvgenyK) #14

changes will be local on client-side and never will be sent to server

I agree that it is the easiest and correct way.

another way - you can rename table and create view with the same name. as a result, clients will be able to receive data in old manner but they should use new Insert/Update/Delete commands…


(marcantheunis) #15

the biggest hassle with softdelete is that i have to change all code relating to this table
and i do not have the time for that now
but i can workaround with the logchanges false trick
the value sits in the Delta.Changes[I].OldValueByName[] array so i can check for it client side and will update the physical record before the real applyupdates…
so i have a rather easy work around for the time being
thx!


(marcantheunis) #16

it seems that i can access the oldvalues server side even with logchanges := false
so the value i’ve set is getting to the server…
thus i can update before the server persists the changes…
side effect or intentional?


(EvgenyK) #17

what data are you changed on client-side when LogChanged is set to False?
it seems to be a side effect.


(marcantheunis) #18

a field…
so table contains let say ‘recdeleted’ column
no dymmy but real DB field
i change that value
and serverside i can check it in the delta oldvalues…
seems logical since i’ve changed it in oldvalues client side since logchanges are false at the time of change…


(marcantheunis) #19

procedure TDataService.DABusinessProcessor1BeforeProcessChange(
Sender: TDABusinessProcessor; aChangeType: TDAChangeType;
aChange: TDADeltaChange; var ProcessChange: Boolean);
begin
if (aChange.ChangeType = ctDelete) then begin
// Connection.NewCommand(‘update table set fld = true where id = :myid’, stSQL)
Schema.NewCommand(Connection, ‘MyCommand’,[‘ID’],[somevalue]);
end;
end;

i can get a hold of the schema using sender.schema
but how do i get a hold of Connection?


(marcantheunis) #20

ah, sender.currentconnection…