Albert, If as you sy we can alter the SYS_NEW record in an BEFORE INSERT or BEFORE UPDATE trigger I would appreciate your posting to this list. To the Best of knowledge the SYS_NEW record is implemented as a READ ONLY Cursor and cannot be altered within the trigger code.
Jim Bentley, American Celiac Society 1-504-737-3293 ________________________________ From: Albert Berry <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thursday, February 27, 2014 10:42 AM Subject: [RBASE-L] - RE: date time Jim, the Oracle syntax is slightly different than R:Base syntax, but R:Base can do everything in your example. I use triggers often. Maybe Bill Downall can jump in: the started using triggers in R:Base as soon as they were introduced. The need to change the new record before inserting it is handled somewhat differently, but that is exactly what the above discussion is all about: how do we create the exact insert time in the record without using a calculated data element. The timing is ever so slightly different in R:Base, but we can do it all. The SYS_NEW is the final product of the stored procedure in R:Base. In the specific problem of this thread, using .#NOW as a data element changes the record to be inserted at the time it is inserted to exactly the time of insert (to the 1/1000 of a second). Because R:Base triggers are stored procedures, you can program anything into your trigger. Different words for the same effect. Albert On 2/27/2014 7:07 AM, James Bentley wrote: > > > > > >If you think about this problem -- the need to alter data before a record is >INSERTED or UPDATED. Then one >solution would be the ability to Change record in question in a BEFORE INSERT trigger or a BEFORE UPDATE trigger. >Unfortunately as currently implemented SYS_NEW records are read only. If such records were made changeable >for only the BEFORE INSERT and BEFORE UPDATE one could change data in the appropriate record before the >database changes are effected. You could use a command similar to UPDATE SET colname=.value WHERE CURRENT OF SYS_NEW. >For example here is what ORACLE allows you to do in a BEFORE INSERT trigger > >Oracle/PLSQL: BEFORE INSERT Trigger >This tutorial explains how to create a BEFORE INSERT Trigger in Oracle with >clear, concise examples. >Description >A BEFORE INSERT Trigger means that Oracle will fire this trigger before the >INSERT operation is executed. >Syntax >The syntax to create a BEFORE INSERT Trigger in Oracle/PLSQL is: >CREATE [ OR REPLACE ] TRIGGER trigger_name BEFORE INSERT ON table_name [ FOR >EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION >WHEN ... -- exception handling END; >Parameters or Arguments >OR REPLACE is optional. If specified, it allows you to re-create the trigger >is it already exists so that you can change the trigger definition without >issuing a DROP TRIGGER statement. >trigger_name is the name of the trigger to create. >BEFORE INSERT indicates that the trigger will fire before the INSERT operation >is executed. >table_name is the name of the table that the trigger is created on. >Restrictions > * You can not create a BEFORE trigger on a view. > * You can update the :NEW values. > * You can not update the :OLD values. >Note > * See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, > BEFORE DELETE, and BEFORE UPDATE triggers. > * See also how to drop a trigger. >Example >Let's look at an example of how to create an BEFORE INSERT trigger using the >CREATE TRIGGER statement. >If you had a table created as follows: >CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2), total_cost number(8,2), create_date date, created_by varchar2(10) ); >We could then use the CREATE TRIGGER statement to create an BEFORE INSERT >trigger as follows: >CREATE OR REPLACE TRIGGER orders_before_insert BEFORE INSERT ON orders FOR EACH ROW DECLARE v_username varchar2(10); BEGIN -- Find username of person performing INSERT into table SELECT user INTO v_username FROM dual; -- Update create_date field to current system date :new.create_date := sysdate; -- Update created_by field to the username of the person performing the INSERT :new.created_by := v_username; END; Jim Bentley >American Celiac Society >>________________________________ > From: TOM HART <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Wednesday, February 26, 2014 9:33 PM >Subject: [RBASE-L] - RE: date time > > > >When you said you saw no change, does it show the datetime stamp? >Tom Hart > > > > > > >________________________________ > From: Dennis McGrath <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Wednesday, February 26, 2014 12:33 PM >Subject: [RBASE-L] - RE: date time > > >Bummer. Normally, this is sufficient for a timestamp, but your requirements are stricter than that. > >Dennis McGrath >Software Developer >QMI Security Solutions >1661 Glenlake Ave >Itasca IL 60143 >630-980-8461 >[email protected] > >-----Original Message----- >From: [email protected] [mailto:[email protected]] On Behalf Of Jim Belisle >Sent: Wednesday, February 26, 2014 12:08 PM >To: RBASE-L Mailing List >Subject: [RBASE-L] - RE: date time > >With or without the dot it puts the time when the row first comes on the >screen not when the information is entered. >The time on the form changes when I enter the row. > >James Belisle > >Making Information Systems People Friendly Since 1990 > > > >-----Original Message----- >From: [email protected] [mailto:[email protected]] On Behalf Of Dennis >McGrath >Sent: Wednesday, February 26, 2014 12:03 PM >To: RBASE-L Mailing List >Subject: [RBASE-L] - RE: date time > >That should not be. >Did you put the string .#NOW in the default value definition of the >control? >Notice the dot in front of #NOW > >Dennis McGrath >Software Developer >QMI Security Solutions >1661 Glenlake Ave >Itasca IL 60143 >630-980-8461 >[email protected] >-----Original Message----- >From: [email protected] [mailto:[email protected]] On Behalf Of Jim >Belisle >Sent: Wednesday, February 26, 2014 11:18 AM >To: RBASE-L Mailing List >Subject: [RBASE-L] - RE: date time > >Dennis, > >I did as you suggested but the time entered is not the actual time of >entry but of the time the form started. >I tried staying on the form and doing multiple entries but the times in >the table were as much as one minute off. > >James Belisle > >Making Information Systems People Friendly Since 1990 > > > >-----Original Message----- >From: [email protected] [mailto:[email protected]] On Behalf Of Dennis >McGrath >Sent: Wednesday, February 26, 2014 10:30 AM >To: RBASE-L Mailing List >Subject: [RBASE-L] - RE: date time > >I'm assuming you are using a form based on the table, not a variable >form. >Make the default value of the TimeComp dbedit control = .#NOW you will >find this setting under the effects tab of the control. >The only time this setting has any effect is when you are inserting a >new row, which is what I believe you want. > > >Dennis McGrath >Software Developer >QMI Security Solutions >1661 Glenlake Ave >Itasca IL 60143 >630-980-8461 >[email protected] >-----Original Message----- >From: [email protected] [mailto:[email protected]] On Behalf Of Jim >Belisle >Sent: Wednesday, February 26, 2014 9:41 AM >To: RBASE-L Mailing List >Subject: [RBASE-L] - date time > > > >I have never used the DATETIME data type before. > >I set up a table with the field Timecomp having a DATETIME datatype > >On a form I want the DATETIME to show (constantly refreshing). > >This will be attached to a field above. > >When they hit a button The row will be saved (along with other info) >with this timestamp in the field. > >So each row will have a different time attached to the row. > > > >I tried making a variable on the form equal to the field. > >Then I added the system variable (date and time) to the form related to >the above variable. > >I put into the FORM timer to REFRESH this variable using the comp ID. > > > >I did not see any change. > >Any suggestions? > > > >James Belisle > > > >Making Information Systems People Friendly Since 1990 > > > > > > > > > > >
