I have an update trigger on a table that updates the DateLastUpdated column to the current time.

I'm building a delete process that presents all rows in a dbgrid and displays a checkbox in the first column. The idea is simple, when the checkbox is checked it toggles the state of the checkbox on/off. When the user click the "OK" button the rows that are checked get deleted. When the user clicks the "Cancel" button all checkbox actions are ignored. I'm currently using an FDMemtable that is loaded from EDBStoredProcedure to manage this process.

I'd prefer to manage this with a boolean field in the EDBTable called CanDelete. I'd like to let the user click and toggle as many CanDelete fields as they wish updating the CanDelete field with each click. However, I would like the update trigger to ignore the CanDelete field thus not updating the DateLastUpdated column to the current time.

Is this possible to do with ElevateDB?

Do you think it's better to manage this process like I currently am:

1. Priming read EDB to FDMemtable
2. User activity managed by FDMemtable
3. Post processing FDMemtable to EDB

Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Michael,

> I have an update trigger on a table that updates the DateLastUpdated column to the current time.

Two ideas:

1. I don't think it's possible to exclude a column, but it *is* possible
to include columns. When you create a trigger in EDBManager and choose
"Update", you'll see an additional tab "Columns" where you can enter all
columns except "DateLastUpdated" e.g.

Or:

EXECUTE IMMEDIATE 'CREATE TRIGGER "Test" AFTER UPDATE OF "Column1",
"Column2" ON "MyTable"
BEGIN
  // DoSomething
END';

2. You can also use your old trigger and set a condition like this:

IF OLDROW.CanDelete = NEWROW.CanDelete THEN
SET DateLastUpdated = CURRENT_DATE

Hope that helps.

Uli

Uli Becker wrote:

<<
1. I don't think it's possible to exclude a column, but it *is* possible
to include columns. When you create a trigger in EDBManager and choose
"Update", you'll see an additional tab "Columns" where you can enter all
columns except "DateLastUpdated" e.g.
>>

Thanks Uli
Michael Riley
GySgt USMC (Retired)
www.zilchworks.com

Hi Michael,

I noticed you are using FDMemTable.

Could you please provide an example how you managed to load EDB tables in FDMemTable?

Thanks in advance.

Michael

Mike,

<< Could you please provide an example how you managed to load EDB tables in FDMemTable? >>

Just so you know: you can use features like cached updates in ElevateDB in order to buffer multiple inserts, updates, and deletes in an in-memory table.  Likewise, you can create/use in-memory tables in ElevateDB by doing so in an in-memory database.  Then you can do things like:

(from in-memory database)

CREATE TABLE MyMemoryTable AS SELECT * FROM DiskDatabase.DiskTable WHERE...

or, if you don't want to use a separate in-memory database, just use:

(from on-disk database)

CREATE TEMPORARY TABLE MyTempTable AS SELECT * FROM DiskTable WHERE...

Tim Young
Elevate Software
www.elevatesoft.com

Michael Riley

I would suggest using Delphi to add a boolean TField to the dataset, this does not need to be in the database. In the on-change event of this field fire an ExecSQL SQL statement to update the DateUpdated.

I would use a read-only dataset in your application, to avoid locking rows.

In the "Delete Selected" button, write a loop to iterate the dataset, and for each row with "Delete" ticked, fire an ExecSQL to delete that row, by referencing its primary key.

Adam