I am trying to do multiple inserts to the same table in a trigger.
The purpose of the script is to provide an audit of a table. The
trigger is AFTER UPDATE on my JOBS table. The inserts are going to
DO_AUDIT which is a table to just monitor changes to tables. If i
then run the application and modify any one field it funtions fine,
if I modify 2 or more fields it hangs.

Basically it tests each field for old <> new and if that is true it
does an insert into the table with information necessary to audit.

If two of the tests or more are true, it hangs from the aplllication
point of view. I am sure it is something to do with locking or
multiple insert context but I can find nothing in the firebird
manual, in fact they imply there is no issue.

thanks ross


AS

declare variable strTYPE varchar(14);
declare variable strIPNumber varchar(15) = '000.000.000.000';
declare variable strClientCmptrName varchar(50) = 'NA';
declare variable strDBAUserName varchar(30) = 'NA';
declare variable strNote1 varchar(200);
declare variable blBlobNote blob = NULL;
declare variable strTransactionCode1 varchar(20);

BEGIN
/* Trigger for Jobs Table
(c) Digital Ocean, Inc. 2005 */

/* define type of order */
if (old.jobtype = 'Work') then
begin
strType = 'Work Order';
/* must concatinate the transaction code onto the end of this
prior to using */
strTransactionCode1 = 'WOUPDT';
end

else
begin
strType = 'Sales Order';
strTransactionCode1 = 'SOUPDT';
end

strNote1 = strType || ' ' || old.jobno || ' updated; ';


if (old.jobname <> new.jobname) then
BEGIN
strTransactionCode1 = strTransactioncode1 || '002';
strNote1 = strNote1 || 'Description(jobname) changed from `'
|| old.jobname || '` to `' || new.jobname || '`';
INSERT into DO_AUDITDTL (UNIID, ORDERNUM, ORDERDETLUNID,
DATECREATED, TRANSACTIONCODE, SOURCEREF, IPNUMBER, CLIENTCMPTRNAME,
DBAUSERNAME, CHANGENOTE, CHANGENOTEBLOB, CHANGEBLOBEXIST)
values (GEN_ID
(do_auditdtl_uniid_gen,1), old.JOBNO, 0,
CURRENT_TIMESTAMP, :strTransactioncode1, :strType, :strIPNumber, :strC
lientCmptrName, :strDBAUserName, :strNote1, NULL, 'F');
END

if (old.custname <> new.custname) then
BEGIN
strTransactionCode1 = strTransactioncode1 || '003';
strNote1 = strNote1 || 'Billing Customer(custname) changed
from `' || old.custname || '` to `' || new.custname || '`';
INSERT into DO_AUDITDTL (UNIID, ORDERNUM, ORDERDETLUNID,
DATECREATED, TRANSACTIONCODE, SOURCEREF, IPNUMBER, CLIENTCMPTRNAME,
DBAUSERNAME, CHANGENOTE, CHANGENOTEBLOB, CHANGEBLOBEXIST)
values (GEN_ID
(do_auditdtl_uniid_gen,1), old.JOBNO, 0,
CURRENT_TIMESTAMP, :strTransactioncode1, :strType, :strIPNumber, :strC
lientCmptrName, :strDBAUserName, :strNote1, NULL, 'F');
END
/*
if (old.jobnotes <> new.jobnotes) then
BEGIN
strTransactionCode1 = strTransactioncode1 || '004';
strNote1 = strNote1 || 'Job Notes(jobnote) changed ';
blBlobNote = old.jobnotes || ' TO ' || new.jobnotes;
INSERT into DO_AUDITDTL (UNIID, ORDERNUM, ORDERDETLUNID,
DATECREATED, TRANSACTIONCODE, SOURCEREF, IPNUMBER, CLIENTCMPTRNAME,
DBAUSERNAME, CHANGENOTE, CHANGENOTEBLOB, CHANGEBLOBEXIST)
values (GEN_ID
(do_auditdtl_uniid_gen,1), old.JOBNO, 0,
CURRENT_TIMESTAMP, :strTransactioncode1, :strType, :strIPNumber, :strC
lientCmptrName, :strDBAUserName, :strNote1, NULL, 'T');
END
*/
if (old.custadd1 <> new.custadd1) then
BEGIN
strTransactionCode1 = strTransactioncode1 || '005';
strNote1 = strNote1 || 'Address Line 1(custadd1) changed from
`' || old.custadd1 || '` to `' || new.custadd1 || '`';
INSERT into DO_AUDITDTL (UNIID, ORDERNUM, ORDERDETLUNID,
DATECREATED, TRANSACTIONCODE, SOURCEREF, IPNUMBER, CLIENTCMPTRNAME,
DBAUSERNAME, CHANGENOTE, CHANGENOTEBLOB, CHANGEBLOBEXIST)
values (GEN_ID
(do_auditdtl_uniid_gen,1), old.JOBNO, 0,
CURRENT_TIMESTAMP, :strTransactioncode1, :strType, :strIPNumber, :strC
lientCmptrName, :strDBAUserName, :strNote1, NULL, 'F');
END

if (old.custadd2 <> new.custadd2) then
BEGIN
strTransactionCode1 = strTransactioncode1 || '006';
strNote1 = strNote1 || 'Address Line 2(custadd2) changed from
`' || old.custadd2 || '` to `' || new.custadd2 || '`';
INSERT into DO_AUDITDTL (UNIID, ORDERNUM, ORDERDETLUNID,
DATECREATED, TRANSACTIONCODE, SOURCEREF, IPNUMBER, CLIENTCMPTRNAME,
DBAUSERNAME, CHANGENOTE, CHANGENOTEBLOB, CHANGEBLOBEXIST)
values (GEN_ID
(do_auditdtl_uniid_gen,1), old.JOBNO, 0,
CURRENT_TIMESTAMP, :strTransactioncode1, :strType, :strIPNumber, :strC
lientCmptrName, :strDBAUserName, :strNote1, NULL, 'F');
END

if (old.custadd3 <> new.custadd3) then
BEGIN
strTransactionCode1 = strTransactioncode1 || '007';
strNote1 = strNote1 || 'City(custadd3) changed from `' ||
old.custadd3 || '` to `' || new.custadd3 || '`';
INSERT into DO_AUDITDTL (UNIID, ORDERNUM, ORDERDETLUNID,
DATECREATED, TRANSACTIONCODE, SOURCEREF, IPNUMBER, CLIENTCMPTRNAME,
DBAUSERNAME, CHANGENOTE, CHANGENOTEBLOB, CHANGEBLOBEXIST)
values (GEN_ID
(do_auditdtl_uniid_gen,1), old.JOBNO, 0,
CURRENT_TIMESTAMP, :strTransactioncode1, :strType, :strIPNumber, :strC
lientCmptrName, :strDBAUserName, :strNote1, NULL, 'F');
END

if (old.contact <> new.contact) then
BEGIN
strTransactionCode1 = strTransactioncode1 || '008';
strNote1 = strNote1 || 'Customer Contact(contact) changed from
`' || old.contact || '` to `' || new.contact || '`';
INSERT into DO_AUDITDTL (UNIID, ORDERNUM, ORDERDETLUNID,
DATECREATED, TRANSACTIONCODE, SOURCEREF, IPNUMBER, CLIENTCMPTRNAME,
DBAUSERNAME, CHANGENOTE, CHANGENOTEBLOB, CHANGEBLOBEXIST)
values (GEN_ID
(do_auditdtl_uniid_gen,1), old.JOBNO, 0,
CURRENT_TIMESTAMP, :strTransactioncode1, :strType, :strIPNumber, :strC
lientCmptrName, :strDBAUserName, :strNote1, NULL, 'F');
END

if (old.custfax <> new.custfax) then
BEGIN
strTransactionCode1 = strTransactioncode1 || '009';
strNote1 = strNote1 || 'Fax Number(custfax) changed from `' ||
old.custfax || '` to `' || new.custfax || '`';
INSERT into DO_AUDITDTL (UNIID, ORDERNUM, ORDERDETLUNID,
DATECREATED, TRANSACTIONCODE, SOURCEREF, IPNUMBER, CLIENTCMPTRNAME,
DBAUSERNAME, CHANGENOTE, CHANGENOTEBLOB, CHANGEBLOBEXIST)
values (GEN_ID
(do_auditdtl_uniid_gen,1), old.JOBNO, 0,
CURRENT_TIMESTAMP, :strTransactioncode1, :strType, :strIPNumber, :strC
lientCmptrName, :strDBAUserName, :strNote1, NULL, 'F');
END

if (old.custpho <> new.custpho) then
BEGIN
strTransactionCode1 = strTransactioncode1 || '010';
strNote1 = strNote1 || 'Customer Phone(custpho) changed from
`' || old.custpho || '` to `' || new.custpho || '`';
INSERT into DO_AUDITDTL (UNIID, ORDERNUM, ORDERDETLUNID,
DATECREATED, TRANSACTIONCODE, SOURCEREF, IPNUMBER, CLIENTCMPTRNAME,
DBAUSERNAME, CHANGENOTE, CHANGENOTEBLOB, CHANGEBLOBEXIST)
values (GEN_ID
(do_auditdtl_uniid_gen,1), old.JOBNO, 0,
CURRENT_TIMESTAMP, :strTransactioncode1, :strType, :strIPNumber, :strC
lientCmptrName, :strDBAUserName, :strNote1, NULL, 'F');
END




END