A colleague is running the following in ISQL (for ASA9.0.2.3527)

----------
create variable current_transaction varchar(30);
set current_transaction='PASU256';

DELETE auction.note
FROM auction.note_type
WHERE note_type.note_type_id = note.note_type_id
AND note_type.code = 'Extra'
AND note.text = 'This Vehicle is Located In Alice Springs';

rollback;

drop variable current_transaction;
----------

(The ROLLBACK is only there until we resolve this issue)

During execution the warning comes up:

"Null value eliminated in aggregate function" (SQLCODE=109, ODBC 3
State="01003") Line 13, column 1 (on the DELETE statement).

Examining the NOTE table, it has a trigger before delete which reads
as follows:

----------
ALTER TRIGGER "tdb_note" .tdb_note before delete on auction.note
referencing old as old_del
for each row
begin
if current remote user is null then
insert into auction.note_audit(note_id,
transaction_date,transaction_type,transaction_user ,
transaction_name) values(
old_del.note_id,getdate(*),'D',current user,current_transaction)
end if
end
----------

There are no other triggers on the table.

Selecting the rows that are supposed to be deleted shows 432 records.
Using the Profiling feature shows that the INSERT statement in this
trigger is called 432 times.

Any suggestions why we're getting this aggregate function warning?
Similar triggers on other tables haven't caused this issue (that we're
aware of!)

The NOTE table schema is:

----------
CREATE TABLE "auction"."note" (
"note_id" integer NOT NULL,
"note_type_id" integer NOT NULL,
"note_ref_id" integer NOT NULL,
"internal_only_flag" char(1) NULL,
"text" varchar(250) NULL,
"date_created" timestamp NULL,
"date_last_modified" timestamp NULL,
"created_by" varchar(30) NULL,
"last_modified_by" varchar(30) NULL,
"added_by_pda_flag" char(1) NULL DEFAULT 'N',
"email_message_id" integer NULL,
PRIMARY KEY ( "note_id" )
);
----------

and the NOTE_AUDIT schema is:

----------
CREATE TABLE "auction"."note_audit" (
"note_id" integer NOT NULL,
"transaction_date" timestamp NOT NULL,
"transaction_type" char(1) NOT NULL,
"transaction_user" varchar(30) NOT NULL,
"transaction_name" varchar(30) NOT NULL,
"internal_only_flag" char(1) NULL,
"text" varchar(250) NULL,
"note_type_id" integer NOT NULL DEFAULT '',
"note_ref_id" integer NOT NULL DEFAULT ''
);
----------

TIA.

Chris.

Did you try what was recommended in the response to your previous post
on this question? If so, what was the outcome?



youcantryreachingme wrote:

Sorry - I didn't realise I posted twice to the same group. I'll put a
reply on the other thread.