| Subject | Re: [firebird-support] Why IN AUTONOMOUS TRANSACTION doesnt work here? |
|---|---|
| Author | W O |
| Post date | 2013-09-20T17:05:24Z |
That's was the idea Helen, to have records of the errors, all of them.
But it seems it's impossible to record some errors because:
CREATE PROCEDURE SAVE_LASTNAME(
IDENTI TYPE OF COLUMN LASTNAMES.LSN_IDENTI,
LASTNM TYPE OF COLUMN LASTNAMES.LSN_LASTNM)
AS
BEGIN
UPDATE OR INSERT INTO LASTNAMES
(LSN_IDENTI, LSN_LASTNM)
VALUES (:Identi , :LastNm ) ;
WHEN ANY DO
IN AUTONOMOUS TRANSACTION DO
INSERT INTO ERRORS
(ERR_MODULE, ERR_COMENT)
VALUES ('SAVE_LASTNAME', 'There was an error!!!') ;
END;
doesn't insert a row in the table ERRORS when I write:
EXECUTE PROCEDURE SAVE_LASTNAME(0, 'SMITH678901234567890123456')
The column LSN_LASTNM is VARCHAR(20) and the length of data is greater than 20
Of course, I can do the validation in my program, but I would prefer to do it in a stored procedure or trigger because the language can change any day.
So, the question is:
Is it impossible to insert a row in the table ERRORS when the lenght of the data is greater than 20 or that can do of some way?
Greetings.
Walter.
On Thu, Sep 19, 2013 at 3:33 PM, Helen Borrie <helebor@...> wrote:
At 05:51 a.m. 20/09/2013, W O wrote:
>
>On Thu, Sep 19, 2013 at 12:21 PM, Leyne, Sean <<mailto:Sean@...>Sean@...> wrote:
>
>It is as expected, data type constraints are enforced before all other operations.
>
>What would be the purpose of firing a trigger if the data is not valid?
>Catching exceptions, maybe?
Maybe NOT! PSQL has exception handling built in. The default handler passes execution immediately to the final EXIT statement and returns the error to the client. Write your own exception handlers in triggers, SPs and block executes to get the exact behaviour you desire, including logging records to external tables that will survive rollback.
Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________