James and Albert,
Thank you for your help. I will wait for the next update for implementing the trigger Tony IJntema From: [email protected] [mailto:[email protected]] On Behalf Of James Bentley Sent: zondag 23 november 2014 15:47 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Using Triggers Understanding BEFORE and AFTER Triggers >From RDCC re changes in upcoming update Here is a table of the 6 trigger types and which cursors are available and whether you can update them or not. BEFORE DELETE: Only SYS_OLD (read only) AFTER DELETE: nothing because the old row is already gone BEFORE INSERT: Only SYS_NEW (updateable) AFTER INSERT: Only SYS_NEW (read only) BEFORE UPDATE: Both SYS_NEW (updateable), SYS_OLD (read only) AFTER UPDATE: Only SYS_NEW (read only) (the old row has already been modified so is not available) Thus if your procedure needs to modify the new row or the updated row before the table is modified you MUST do so in the BEFORE INSERT/BEFORE UPDATE trigger using command. Note changing the virtual row column values do not invoke the trigger again. UPDATE tablename SET columname1=newvalue1, columname2=newvalue2 + WHERE CURRENT OF SYS_NEW to retrieve information from SYS_OLD or SYS_NEW use appropriate column names and indicator variable name and SYS_OLD or SYS_NEW for appropriate virtual row. SET VAR Ocolumn1 TEXT = NULL -- or appropriate data type SET VAR OIvname1 INTEGER SET VAR Ocolumn2 TEXT = NULL -- or appropriate data type SET VAR OIvname2 INTEGER SELECT column1, column2 INTO Ovarname1 INDICATOR OIvname1, Ovarname2 INDICATOR OIvname2 + FROM tablename WHERE CURRENT OF SYS_OLD Note that some of this syntax only becomes available with next update. Seems like what you want to do should be handled in a BEFORE trigger or possibly with a COMPUTED COLUMN Jim Bentley, American Celiac Society 1-504-737-3293 _____ From: James Bentley <[email protected]> To: "[email protected]" <[email protected]> Sent: Sunday, November 23, 2014 7:55 AM Subject: Re: [RBASE-L] - Re: Using Triggers Upcoming release will fix some problems with triggers. Perhaps yours falls under fix. BEFORE INSERT/BEFORE UPDATE TRIGGERS SYS_NEW Virtual row become update-able. All other times SYS_NEW and SYS_OLD virtual rows are read only Jim Bentley, American Celiac Society 1-504-737-3293 _____ From: Albert Berry <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Friday, November 21, 2014 6:44 PM Subject: [RBASE-L] - Re: Using Triggers Tony, the column is only computed once the row is saved, and then the form needs to refresh. You need to add these lines to either the eep calling the stored procedure, or if the procedure is only used in the form, in the procedure itself. PROPERTY TABLE <FORMTABLENAME> 'POST' PROPERTY TABLE <FORMTABLENAME> 'REFRESH' That will show you the result on the form Albert On 11/21/2014 1:27 PM, Tony IJntema wrote: Using a trigger I encounter a problem which I don’t understand. I have to fill a text column which has to be computed as follows: Year as text + Daynumber as text + Primary Key as text I have written a stored procedure which needs to be executed after the insert. I am catching the last used PK using the where clause as count=insert It works almost ok, but if I insert a new row the column is still empty and if I insert another row the concerned column of the new rows is empty, but then the column is filled in row which I have created before the last one. Why does it works this way and what do I have to do to have the column filled when the particular row is created and not one row later? Tony
