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
