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
<http://www.techonthenet.com/oracle/triggers/drop.php>.
/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
<http://www.techonthenet.com/oracle/triggers/after_delete.php>,
AFTER INSERT
<http://www.techonthenet.com/oracle/triggers/after_insert.php>,
AFTER UPDATE
<http://www.techonthenet.com/oracle/triggers/after_update.php>,
BEFORE DELETE
<http://www.techonthenet.com/oracle/triggers/before_delete.php>,
and BEFORE UPDATE
<http://www.techonthenet.com/oracle/triggers/before_update.php>
triggers.
* See also how to drop a trigger
<http://www.techonthenet.com/oracle/triggers/drop.php>.
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
