Tim,

I am using EDB v2.02b6.

If users do not enter a new name, I want to provide a default name.  Using a BEFORE INSERT
trigger with the condition "NEWROW.NAME is null" results in the following.

Using EDB Manager:

* Enter a new row without a Name
* Click SAVE
* Message ElevateDB Error #1004 Column name in the table Activity cannot be null.
* Click SAVE
* Row is posted with the the required new name.

Do you have any suggestions as to what I should be doing?

Also, how do I get to view the LOG MESSAGES? If I click "View Log Messages" for the
session, I can see the #1004 errors listed but not the "New Activity" message which is SET
in the TRIGGER.

------------------------------

CREATE TABLE "Activity"
(
"ID"  INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"Name"  VARCHAR(40) COLLATE "ANSI_CI" NOT NULL,
more stuff
"Notes"  CLOB COLLATE "ANSI"
)


CREATE TRIGGER "InsertActivity" BEFORE INSERT ON "Activity"
WHEN NEWROW.NAME is null
BEGIN  
  SET LOG MESSAGE TO 'New Activity (' + CAST(NEWROW.ID as VARCHAR(8)) + ')';
  SET NEWROW.Name = 'New Activity (' + CAST(NEWROW.ID as VARCHAR(8)) + ')';
END;

Richard


It looks as though the NOT NULL test is being carried out before the BEFORE INSERT trigger which is definitely over to Tim. As a short term stop gap you could remove the NOT NULL from the column.

Roy Lambert [Team Elevate]

Richard,

Sorry for the delay in getting a response on this.  BTW, the issue with
derived tables that you reported will be corrected in the next build, in so
far that the correction is that any attempt to use a derived table
identifier that is already in use will result in an error, not an AV due to
the derived table identifier being "overwritten".

<< If users do not enter a new name, I want to provide a default name.
Using a BEFORE INSERT trigger with the condition "NEWROW.NAME is null"
results in the following.

Using EDB Manager:

* Enter a new row without a Name
* Click SAVE
* Message ElevateDB Error #1004 Column name in the table Activity cannot be
null.
* Click SAVE
* Row is posted with the the required new name.

Do you have any suggestions as to what I should be doing? >>

This is working fine here for me using the DDL that you posted.  The
activity name is assigned each time.

<< Also, how do I get to view the LOG MESSAGES? If I click "View Log
Messages" for the session, I can see the #1004 errors listed but not the
"New Activity" message which is SET in the TRIGGER. >>

You can only view log messages after the execution of scripts or procedures.
The log messages that are generated by SET LOG MESSAGE are transient in
nature, and are not the same thing as the logged events that are in the
global log file maintained by ElevateDB.  However, we do intend to allow you
to log custom events into the log file at some point using a different
syntax.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Roy & Tim,

I tried setting the Name to allow Nulls.  It did not generate the error message but the
NEWROW.Name to was not being set.  (It was NULL)

I changed the TRIGGER to

  SET NEWROW.Name = 'New Activity (' + CAST(CURRENT_TIMESTAMP as VARCHAR(16)) + ')';

instead of using the GENERATED NEWROW.ID and it is all working and it is now generating a
unique new name if the users are to lazy to enter one.
Something isn't quite right when using the generated ID.



CREATE TABLE "Activity"
(
"ID"  INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"Name"  VARCHAR(40) COLLATE "ANSI_CI" NOT NULL,
more stuff
"Notes"  CLOB COLLATE "ANSI"
)


CREATE TRIGGER "InsertActivity" BEFORE INSERT ON "Activity"
WHEN NEWROW.NAME is null
BEGIN  
 SET NEWROW.Name = 'New Activity (' + CAST(NEWROW.ID as VARCHAR(8)) + ')';
END;

Richard Harding

Richard,

<< instead of using the GENERATED NEWROW.ID and it is all working and it is
now generating a unique new name if the users are to lazy to enter one.
Something isn't quite right when using the generated ID. >>

Duh, I completely missed that you were using an IDENTITY column.  I was
entering in the ID values, which is why they always worked.   The issue that
you're seeing is normal - IDENTITY columns will not be assigned values until
the INSERT actually takes place.   Therefore, you cannot use them in a
BEFORE INSERT trigger.

--
Tim Young
Elevate Software
www.elevatesoft.com

>> IDENTITY columns will not be assigned values until the INSERT actually takes place.  
Therefore, you cannot use them in a BEFORE INSERT trigger.

Thank you.  I am pleased that there is a simple explanation.

--
Richard Harding.