trigger after insert

[Ivan Smahin]

Hello Bart,

Tuesday, September 25, 2007, 6:32:17 PM, you wrote:

> On 25-sep-07, at 17:21, Ivan Smahin wrote:

>> Hello Bart,
>>
>> Tuesday, September 25, 2007, 6:07:25 PM, you wrote:
>>
>>> Hi List,
>>
>>> can somebody explain why this trigger
>>
>>> CREATE OR REPLACE TRIGGER "trigger_tbl_users_AI" AFTER INSERT
>>
>>>      ON "tbl_users" FOR EACH ROW
>>
>>>      BEGIN
>>>         if @user_id IS null then
>>>                 NEW.objptr_created_by = New.recid;
>>
>>>                 NEW.objptr_modified_by = New.recid;
>>>         End IF
>>>      END;
>>
>>> generates this error when executed in the sql editor:
>>
>>> Error: Kernel error: 0x91504. Trigger "trigger_tbl_users_AI": time
>>> "AFTER" is not supported in this context.
>>
>> Sorry, I was wrong:
>> It  must  be  "BEFORE  INSERT"  trigger  to  get  ability  to operate
>> with NEW.field.

> confused now

> do you mean I CAN use New.recid in the context of a BEFORE INSERT  
> trigger ?

Yes, correct.
Look - you have prepared record to be inserted.
Now you call tbl.AddRecord.
So fields contain NEW values now.
This time "BEFORE INSERT" trigger can be fired.
Next, record has been added.
Assume there is one more "AFTER INSERT" trigger.
But  fields  are  free  of  NEW values already. So you can not use NEW
values in such a trigger.

> I CAN create a record and in the before insert trigger refer to the  
> recid that is not yet created (maybe it is created but not yet  
> inserted) ?
> That would be the ideal solution so my first example would be correct:

> CREATE OR REPLACE TRIGGER "trigger_tbl_users_BI" BEFORE INSERT

>      ON "tbl_users" FOR EACH ROW

>      BEGIN
>         if @user_id IS NOT null then
>                 NEW.objptr_created_by = @user_id;

>                 NEW.objptr_modified_by = @user_id;
>         else
>                 NEW.objptr_created_by = NEW.recid;-----> ??
>                 NEW.objptr_modified_by = NEW.recid;---->??
>         End IF
>          NEW.creation_dt = NOW();

>          NEW.modification_dt = NOW();

>                 

>      END;

I'm not sure about NEW.recID. Need to check it.


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com