Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Get the current updating row, to be inserted into another table using trigger

 
Girish Vasmatkar
Ranch Hand
Posts: 201
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello all,


I have two tables, entity and entity_historical_data. I want to maintain the historical data of each entity that was updated. So, if an entity is updated 5 times, I will have five records in the entity_historical_data for the corresponding entity ID. Also, the columns in the entity and entity_historical_data are more or less the same.

I am using Hibernate for the application and my current implementation involves manually inserting in the entity_historical_data table using hibernate before an entity with a given id is updated.

I'd like to do this using triggers, such that before update, existing record (row) which is gonna updated (old) will be added in the entity_historical_data table and then the update will be performed.

So, I will need all the data of the row which is going to be updated and will insert that data into another table using trigger.

Can someone help me with this ?

 
Deepak Pant
Ranch Hand
Posts: 446
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Assuming that you are using Oracle database, you will need at least following types of Triggers:

1. CREATE OR REPLACE TRIGGER <<your trigger name>> AFTER DELETE ON <<your table name>> FOR EACH ROW
- access the current column values of the row being deleted as :OLD.<<column name>>

2. CREATE OR REPLACE TRIGGER <<your trigger name>> AFTER UPDATE ON <<your table name>> FOR EACH ROW
- access the old column values of the row being updated as :OLD.<<column name>>
- access the new column values of the row being updated as :NEW.<<column name>>

The syntax/semantics will change based on the database being used.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic