Win a copy of High Performance Python for Data Analytics this week in the Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Bear Bibeault
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Jj Roberts
  • Carey Brown
Bartenders:
  • salvin francis
  • Frits Walraven
  • Piet Souris

Database triggers

 
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is it possible to use triggers and store data from one table in another table, 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
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
So it takes a day for light to pass through this glass? So this was yesterday's tiny ad?
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic