--- In firebird-support@yahoogroups.com, "Martijn Tonies"
<m.tonies@...> wrote:

>
> Hello Mitch,
>
> > I have a before insert trigger which includes NEW.DOC_CREATED_DATE =
> > CURRENT_TIMESTAMP;
> >
> > 99% of the time this works, but 1% of the time there is a null value.
> >
> > I assume that there can be only 2 causes. Either 1. the trigger is
> > failing or 2. the column is being overwritten somehow after the

insert.

>
> It has to be (2), that is, if you're not turning the trigger inactive
> somewhere.
>
> > In order to discount #1, would it be true to say that if a trigger
> > and/or generator fails for some reason that there would be an error
> > message and the insert would not occur?
>
> If a trigger raises an error, the client will receive the error and the
> record will not be inserted.
>
> > Can transaction control be used to enforce this at the trigger level
> > or do transactions only exist in the context of an external

application?

>
> Triggers work in the same transaction as the transaction that caused
> the INSERT to happen.
>
> What is the full source of the trigger?
>
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB,

Oracle &

> MS SQL Server
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>

Hi Martijn,
Here is the before insert trigger. I have checked the dependencies and
no other DB object refers to DOC_CREATED_DATE so I assume it must be
an issue in my front end code?


AS
DECLARE VARIABLE v_Proj_FeeLumpSum NUMERIC(18,2);
DECLARE VARIABLE v_doc_proj_name VARCHAR(140);
DECLARE VARIABLE v_doc_proj_desref VARCHAR(12);
DECLARE VARIABLE v_proj_feepercent FLOAT;

BEGIN
IF (NEW.DOC_ID IS NULL) THEN
BEGIN
NEW.DOC_ID = GEN_ID(GEN_DOC_ID,1);
END

NEW.DOC_CREATED_DATE = CURRENT_TIMESTAMP;
NEW.DOC_ACTIVE = 'Y';
NEW.DOC_SUPPL_INV_FREIGHT = 0;

SELECT proj.proj_feelumpsum, proj_name, proj_desref, proj_feepcnt
FROM proj
WHERE proj.proj_id = NEW.proj_id
INTO :v_Proj_FeeLumpSum, :v_doc_proj_name, :v_doc_proj_desref,
:v_proj_feepercent;

New.doc_proj_name = :v_doc_proj_name;
new.doc_proj_desref = :v_doc_proj_desref;
new.doc_proj_feepercent = :v_proj_feepercent;
NEW.doc_proj_feelumpsum = :v_proj_feelumpsum;

if (new.DOCTYPE_ID = 1) then -- rfq
new.DOC_INSTRUCTION = 'Please quote for supply & delivery of the
following items:';

if (new.DOCTYPE_ID = 3) then -- rfq
new.DOC_INSTRUCTION = 'Please supply & deliver the following
items:';
END