Akshata Alandker wrote:Is it possible to use triggers and store data from one table in another table, ...
Yes.
Akshata Alandker wrote:here i want to delete the previous table data for some reason but want to keep back up of the deleted data. Can this be done using triggers? please guide
The details of how to do this will depend on your database e.g. here is the
Using Triggers documentation for Oracle 11g (read the guidelines), and here is a
simple example of using a trigger to backup data from one table to another for individual records (for inserts/updates/deletes in this example).
If you delete a lot of records in one go (e.g. "DELETE FROM my_table" will delete all the records in my_table), then a row-level trigger will fire for every individual record you've deleted, which will be slow. Remember that you will need to make sure the backup table has the correct tablespace, indexes etc. Also, if your trigger fails for some reason, then the entire transaction will have to be rolled back.
But if you want to copy an entire table, it would be much faster to do this as a single operation e.g.
using CTAS: "Create TABLE my_backup AS SELECT * FROM my_table".
Triggers can be useful e.g. for auditing transactions, but you need to be careful about using them. It's very easy to lose track of which triggers fire during which transactions e.g. if you have triggers on several tables that are all affected by a single transaction, which can have unexpected results as well as slowing down your transactions. Also, there are restrictions on which tables/data you can read from a trigger during a transaction - this can produce the famous "mutating table" error (in Oracle). Another important point is that you need to make sure people know what your trigger does, so they can take account of this in their own designs/code. Because the trigger is attached to a table inside your DB, where your developers may not be aware of it, it's easy to forget that the trigger exists (until it goes wrong).
Talk to your DBA about how/whether to do this on your system.
