I want to provide users with a choice of how much logging they do for the app. The type of thing is a set of checkboxes, tick the ones to be logged and then tick for the logging level (just the action or before and after values)

With DBISAM and a customised engine function it was relatively easy - test for the tablename, test if they want to do something - do it.

With ElevateDB the only way I can spot is Triggers, but this looks as though its going to get complex, especially where I want a Trigger for some specific reason (eg cascade deletes).

Any and all suggestions welcome.

Roy Lambert

Roy

> I want to provide users with a choice of how much logging they do for the
app. The type of thing is a set of checkboxes, tick the ones to be logged
and then tick for the logging level (just the action or before and after
values)
>
> With DBISAM and a customised engine function it was relatively easy - test
for the tablename, test if they want to do something - do it.
>
> With ElevateDB the only way I can spot is Triggers, but this looks as
though its going to get complex, especially where I want a Trigger for some
specific reason (eg cascade deletes).
>
> Any and all suggestions welcome.

Would it be possible to have a stored procedure which drops/creates a
"logging" trigger for each table based on what the user selects?

John

John


Thanks for the suggestion, but creating a slew of triggers for each table isn't the problem, that can be done easily enough in Delphi, and probably slightly less easily in SQL/PSM I'm just hoping there's a more elegant way that I have stupidly overlooked.

Having three triggers (before insert, update,delete) for each table seems a bit sub-optimal.

If that's the only way to go then that's what I'll do but I'm hoping there's a better way.

Roy Lambert

Roy,

<< I want to provide users with a choice of how much logging they do for the
app. The type of thing is a set of checkboxes, tick the ones to be logged
and then tick for the logging level (just the action or before and after
values)

With DBISAM and a customised engine function it was relatively easy - test
for the tablename, test if they want to do something - do it. >>

Easy to you, not so easy for most coming from other database engines.
Triggers defined in your application couldn't automatically be used in DBSYS
or any other external application like ODBC, and required a re-compilation
to get it to work.  I think that you're seeing DBISAM with rose-colored
glasses a bit too much.  Most of your comments revolve around what was good
about DBISAM while ignoring that which was bad.

<< With ElevateDB the only way I can spot is Triggers, but this looks as
though its going to get complex, especially where I want a Trigger for some
specific reason (eg cascade deletes). >>

It's not complex at all.  It's just a bit of work.  There's a difference.
I've got an enhancement on the list to allow for a universal trigger that is
called for any type of operation, and that may alleviate some work, but the
actual code that is used wouldn't change one bit.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Easy to you,

Yup - that's the person I'm concerned with

>Triggers defined in your application couldn't automatically be used in DBSYS
>or any other external application like ODBC, and required a re-compilation
>to get it to work. I think that you're seeing DBISAM with rose-colored
>glasses a bit too much. Most of your comments revolve around what was good
>about DBISAM while ignoring that which was bad.

Probably, but remember what was bad depends on your viewpoint. eg since I didn't use ODBC then the fact that the triggers couldn't be used without a recompile didn't exactly bother me. Their lack in DBSys was a bit more of a problem I accept.

Also I see no reason why we can't have the good from DBISAM whilst leaving the bad behind - do you? And then we can add more good stuff to make it even better.

>It's not complex at all. It's just a bit of work. There's a difference.

I can't remember precise details of what I'd been writing since the code was thrown out ages ago but unless I'm misremembering or misinterpreting then in rough terms I'll need upto 150 triggers vs 1 procedure that tested a list of tables. That fact alone says more complex.

If I wasn't going to use triggers for any other purpose, and there weren't instances (eg bulk table update) where I don't want the triggers to fire at all  it would be much easier I could just generate and install or delete the triggers automatically when someone clicks log or don't log this table with the exact template being used being dependent on the logging level selected.

But since I will (almost certainly) be using them for other purposes I need to take this into account.

Naturally I'll be looking at developing a function (sql or external) that is called from each trigger. One thing I'm not sure of yet is just how I'm going to call it with differing parameters.

Its one of those instances when I'd be delighted to be wrong - just point out the error or my ways.

>I've got an enhancement on the list to allow for a universal trigger that is
>called for any type of operation, and that may alleviate some work, but the
>actual code that is used wouldn't change one bit.

Great - I remember requesting it - what's the odds of a database level one?

Roy Lambert

Roy

> But since I will (almost certainly) be using them for other purposes I
need to take this into account.

I'm probably being thich here (not unususal but why?

John

John

>> But since I will (almost certainly) be using them for other purposes I
>need to take this into account.
>
>I'm probably being thich here (not unususal but why?

Not sure which bit the why relates to so here goes:

other purposes: simplest one is before delete - I may want to delete from other tables/update other tables if an item is deleted.

take it into account: if there's already code in the trigger I don't want to overwrite it if the user elects to add logging, and if the user turns logging off you can't simply drop the trigger it has to be adjusted.

Its all doable just more complex.

Roy Lambert

Roy

> take it into account: if there's already code in the trigger I don't want
to overwrite it if the user elects to add logging, and if the user turns
>logging off you can't simply drop the trigger it has to be adjusted.

Don't you just create/drop trigger(s) called for example
loginsert,logdelete,logupdate for the appropriate table and leave other
triggers as they are?

John

John

>Don't you just create/drop trigger(s) called for example
>loginsert,logdelete,logupdate for the appropriate table and leave other
>triggers as they are?

Congratulations - that's the first bit of my thinking proven wrong. I didn't test it but I thought you would only be allowed one trigger per one event per table - Delphi thinking I suppose.

That should make life a LOT easier.

But a question - if you have two before delete triggers which sequence do they fire in? I'm not sure yet if this will be important but I'd like to be prepared.

Roy Lambert

Roy,

<< Probably, but remember what was bad depends on your viewpoint. eg since I
didn't use ODBC then the fact that the triggers couldn't be used without a
recompile didn't exactly bother me. Their lack in DBSys was a bit more of a
problem I accept. >>

Which is fine, but you have to realize that we're making a database engine
for a much wider audience than just Delphi.  DBISAM was almost exclusively
geared toward Delphi/C++Builder, whereas EDB is intended to be used just
about anywhere.

<< Also I see no reason why we can't have the good from DBISAM whilst
leaving the bad behind - do you? And then we can add more good stuff to make
it even better. >>

Not at all.  However, it takes time to recreate an entire database engine,
so the syntactic sugar comes later, not sooner.

<< I can't remember precise details of what I'd been writing since the code
was thrown out ages ago but unless I'm misremembering or misinterpreting
then in rough terms I'll need upto 150 triggers vs 1 procedure that tested a
list of tables. That fact alone says more complex. >>

I disagree.  It's a lot of work and a pain in the ass, but it's not complex.
The code is all very simple.

<< Great - I remember requesting it - what's the odds of a database
level one? >>

The odds are always good, in the very long term.  In the short term, not
very good.

--
Tim Young
Elevate Software
www.elevatesoft.com