created a table as below with trigger, inserted a row, check values, it looks fine (in SQL Command window).
when updated this row from SQL Command and checked value, it looks again fine.
created a Report with Form ( page 8 (report) and 9 (Form) ), Created a row and checkd, still it looks fine ... but when updated the row and checked , it messed-up with "created_at" column value --reset time to '12.00.00.000000 AM +00:00'
below is SQL Command window session:
create table testtimestamp (pid number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY INCREMENT BY 1 START WITH 1 NOCACHE NOT NULL ENABLE,
pname varchar2(20),
created_at timestamp(6) with time zone,
updated_at timestamp(6) with time zone)
CREATE OR REPLACE TRIGGER BIU_TESTTIMESTAMP
BEFORE INSERT or UPDATE on TESTTIMESTAMP
FOR EACH ROW
--
DECLARE
x number;
v_timestamp timestamp(6) with time zone;
BEGIN
--
Select CURRENT_TIMESTAMP Into v_timestamp From dual;
--
If inserting Then
:NEW.CREATED_AT := v_timestamp;
End If;
--
If updating Then
:NEW.UPDATED_AT := v_timestamp;
End If;
END;
insert into testtimestamp (pname) values ('abc');
insert into testtimestamp (pname) values ('xyz');
select * from testtimestamp order by pid desc;
update testtimestamp set pname = 'mno' where pname='abc';
regards
my workspace:
learn999
oralover2005@tutanota.com / Ora123cle&
app: TestFeatures21.2
page: 8 & 9