Understanding BEFORE and AFTER TriggersFrom 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 columnvalues 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_NEWuse 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 Society1-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:
#yiv1395563867 #yiv1395563867 -- filtered {panose-1:2 4 5 3 5 4 6 3 2
4;}#yiv1395563867 filtered {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2
4;}#yiv1395563867 filtered {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2
4;}#yiv1395563867 filtered {font-family:Verdana;panose-1:2 11 6 4 3 5 4 4 2
4;}#yiv1395563867 p.yiv1395563867MsoNormal, #yiv1395563867
li.yiv1395563867MsoNormal, #yiv1395563867 div.yiv1395563867MsoNormal
{margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv1395563867 a:link,
#yiv1395563867 span.yiv1395563867MsoHyperlink
{color:blue;text-decoration:underline;}#yiv1395563867 a:visited, #yiv1395563867
span.yiv1395563867MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv1395563867
p.yiv1395563867MsoAcetate, #yiv1395563867 li.yiv1395563867MsoAcetate,
#yiv1395563867 div.yiv1395563867MsoAcetate
{margin:0cm;margin-bottom:.0001pt;font-size:8.0pt;}#yiv1395563867
span.yiv1395563867BalloonTextChar {}#yiv1395563867
span.yiv1395563867EmailStyle19 {color:#1F497D;}#yiv1395563867
span.yiv1395563867EmailStyle20
{color:#1F497D;font-weight:normal;font-style:normal;}#yiv1395563867
span.yiv1395563867EmailStyle21
{color:#1F497D;font-weight:normal;font-style:normal;}#yiv1395563867
.yiv1395563867MsoChpDefault {font-size:10.0pt;}#yiv1395563867 filtered
{margin:72.0pt 72.0pt 72.0pt 72.0pt;}#yiv1395563867
div.yiv1395563867WordSection1 {}#yiv1395563867 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
