If a user clicks "insert" on a form to create a new record then thinks "ah no" and clicks cancel to undo that insert is there an easy way to catch that undo on the server?

(I'm not talking about doing it in code on the client / application)

I have functions in the DEFAULT values of my table-columns, which return values but may also do other things, sometimes it would be useful to unravel these.

Adam


The only things I can think of is to remove the stuff from DEFAULT and move it to an AFTER INSERT trigger or file a stored procedure from an after cancel event on a table/query component.

Roy Lambert [Team Elevate]

Thanks Roy,

I thought the only way to do it might be to tie in to the application & catch the "cancel" at that end, but I hoped there might be some clever mechanism for managing it on the server.

Adam

Adam,

That's not the best design. The DEFAULT clause should be only used to initialize row values.
Although you are able to call user defined functions in a DEFAULT clause definition, it's not advisable to call any functions from there that have 'collateral' effects, i.e., that might change other values in the database other than the value of the column being initialized. Just think of the possible implications in a complex database structure with lots of DEFAULT clauses, possibly being activated in chain.

As Roy said, you should move those things out of the DEFAULT clause, however, IMO the right place to move them to is a BEFORE INSERT trigger. Triggers are also more adequate for what you need because they are only fired when the row is actually inserted in the table, so if the action is canceled, the insert never occurs and the trigger is never fired.

--
Fernando Dias
[Team Elevate]

Fernando


I'd still go with the AFTER INSERT.  What happens if the trigger fires then for some reason the insert isn't committed (eg power). Unlikely I know but just in case.

I suppose it depends to some degree on which would give the least hassle to clean up.

Roy Lambert [Team Elevate]

Roy,

I suggested a BEFORE INSERT trigger because it preserves the sequence of actions as they are being executed now - all additional actions are now being executed before the row is actually inserted.

About the possible issues that may occur when for some reason the row in not committed, the only easy and safe way, for now, to handle that is to enclose the whole insert operation in a transaction. I say 'for now' because I expect V3 to have atomic INSERT/UPDATE/DELETE operations (thats only a secret hope I have, Tim never said it will .
If using a transaction is not possible or not practical, then you are right... we have to design things having in mind that when things go wrong we will have to clean up stuff.

--
Fernando Dias
[Team Elevate]

Fernando

>I suggested a BEFORE INSERT trigger because it preserves the sequence of actions as they are being executed now - all additional actions are now being executed before the row is actually inserted.

Good point.

>About the possible issues that may occur when for some reason the row in not committed, the only easy and safe way, for now, to handle that is to enclose the whole insert operation in a transaction. I say 'for now' because I expect V3 to have atomic INSERT/UPDATE/DELETE operations (thats only a secret hope I have, Tim never said it will .
>If using a transaction is not possible or not practical, then you are right... we have to design things having in mind that when things go wrong we will have to clean up stuff.

I think those actions might already be wrapped in an implicit transaction. They used to be in DBISAM.

There was a third reason - personal mindset. In general my mind says "I want to make all these changes after I've actually inserted a row" rather than "I want to make these changes to prepare for inserting a row".

Not that it makes much difference most of the time.

Roy Lambert [Team Elevate]

Roy,

>I think those actions might already be wrapped in an implicit transaction.
>They used to be in DBISAM.

No, they are not, unless that has been changed in recent versions... I'll
have to check.
If they were we wouldn’t have to worry about how to clean up stuff when
things go wrong

>Not that it makes much difference most of the time.

Yes, in many cases it doesn’t matter, but it does in some cases. For example
when you want to set values for a row being inserted or updated, it makes
more sense to change or set the values before the actual insert/update;
another example would be when there are RI constraints and you HAVE to do
things in a certain order.

--
Fernando Dias
[Team Elevate]

Roy,

<< I think those actions might already be wrapped in an implicit
transaction. They used to be in DBISAM. >>

You're thinking of something else - perhaps the implicit transactions around
INSERT/UPDATE/DELETE statements ?  For singleton row updates, there are no
implicit transactions, and even for INSERT/UPDATE/DELETE statements, the
implicit transactions can be chopped up into multiple pieces, and are not
guaranteed to be "transactional", but rather done for performance reasons.

<< There was a third reason - personal mindset. In general my mind says "I
want to make all these changes after I've actually inserted a row" rather
than "I want to make these changes to prepare for inserting a row". >>

I would also vote for AFTER INSERT, if the trigger modifications don't
affect the current row.  It's the only way to ensure that that row was
actually added.  Of course, this also assumes that whatever happens in the
AFTER INSERT trigger does not need to be guaranteed to succeed as a unit of
work along with the initial row insert.  If that's the case, then Fernando
is correct - a transaction around the whole thing is the only way to go.

--
Tim Young
Elevate Software
www.elevatesoft.com

My problems only start after-insert ... the user has successfully created a new record, but then realises a mistake & immediately cancels.

My software does a lot of "tracking" recording who added records to what, where & why. The "temporary records" users create & then immediately delete cause a bit of a headache as tracking records are created & then carried around forever for no reason.

... I guess I could rethink how I structure the whole thing a bit ... probably I have to, but I was trying to find out an easy way to identify a record which has _only_ had the default insert code called on it and no other row changes (ideally on the server) so I could throw away its tracking records.