Databases
StackOverflow2013
Postsdbo
PODifferent timestamp for different DML queries in single transaction in oracle
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODifferent timestamp for different DML queries in single transaction in oracle
    primarykey
    data
    text
    <p>I am doing an insert and delete operation on a table in single transaction.I have trigger on this table which update the log.The log has primary key as sequenceId which always gets incremented on insertion.I do delete first and then insert in the transaction.</p> <p>I have two issues :</p> <ul> <li>The timestamp in the log for the insert and delete is being same. Can I force it to be different.</li> <li>The order of operation(insert/delete) in the log is getting reversed. It shows delete operation coming after insert operation(according to sequenceId).How can I ensure that the order is consistent in the log(insert after delete).</li> </ul> <p>Example :</p> <pre><code>create table address (ID number, COUNTRY char(2)); create table address_log(SEQ_ID number, ID number, COUNTRY char(2), DML_TYPE char(1), CHANGE_DATE timestamp(6)); create sequence seq_id start with 1 increment by 100 nominvalue nomaxvalue cache 20 noorder; create or replace trigger trg_add before insert or delete on address FOR EACH ROW BEGIN if inserting then insert into address_log values(SEQ_ID.nextval, :new.ID, :new.COUNTRY, 'I', sysdate); else insert into address_log values(SEQ_ID.nextval, :old.ID, :old.COUNTRY, 'D', sysdate); end if; end; insert into address values(1,'US'); insert into address values(2,'CA'); delete from address where id = 1; insert into address values(3,'UK'); delete from address where id = 3; </code></pre> <p>if I commit last DML queries in single transaction, then I should see the same order in address_log.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USrajsekhar
    1. USrajsekhar
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CO@wolφi I use the primary key sequenceId of log to determine it.It always gets incremented.Also updated the question.
      singulars
      1. PODifferent timestamp for different DML queries in single transaction in oracle
      1. USrajsekhar
    2. COThanks for your code examples. So, both issues have been solved by @Vincent's answer, haven't they? (Hint: If you accept it, both Vincent and you will get some points...)
      singulars
      1. PODifferent timestamp for different DML queries in single transaction in oracle
      1. USwolφi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload