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';


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
  if current remote user is null then
    insert into auction.note_audit(note_id,
      transaction_name) values(
      old_del.note_id,getdate(*),'D',current user,current_transaction)
  end if
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 ''