writing a trigger to check the content of a transaction

[mberni]

Hi there!
We have an app that writes certain records into a table within a transaction.
one transaction could look like this:

begin work;
insert record1 into mytable;
insert record2 into mytable;
commit;

now i'd like to implement some code (a trigger?), which checks whether both records have been inserted and if not raises an error and/or writes an entry into some log (ascii-file) and rolls back the transaction.

the problem is, when i try to write a trigger to do the job, i cannot determine the point of time of the commit, so i won't be able to tell, whether the second record will follow the current and so cannot decide if i have an error or not. the sequence of the records must not be (1 then 2) it can also be (2 then 1).

i cannot manipulate the app which creates the records.

any ideas?

 

[olded]

Hi:

I don't think you can use a trigger this way. A trigger happens only if an insert, update, or delete successfully executes.

It's upto the front end app to test whether the insert is successful. You don't say what the app is, but if it's an Informix stored procedure you should be able to use the DBINFO function:

# untested
insert record1 into mytable
cnt = DBINFO('sqlca.sqlerrd2')
if cnt != 1
then
rollback work
end if

You could then use the SP to create the log.

Regards,

Ed

 

[mberni]

Thanks for your ideas olded.

The application is a powerbuilder app which uses informix as backend.

I got the problem, that due to misunderstanding some developer regularly "forgets" to code certain inserts which are crucial for stability of our financial-system.

Cause i seem not to be able to make them do it the right way in every case i just want to block "incomplete" transactions using database means.

 

[sjose]

Hi mberni,

Since I came to know you use PB & Informix, let me clarify one doubt, is it possible to save a summary report generated in PB to EXCEL, in the same format as the summary report,I am getting the detailed info. each row wise into excel, which our management cannot manually group
again.

Thanks,

Jose

 

[mberni]

sorry sjose,
i never coded in pb myself so you better ask that question in a powerbuilder forum.