Trigger for delete record and copy to table

[firsttube]

I need to write a trigger that will do the following:

Delete a record and copy it into another table (exact same structure), when the "Status" field of that record is updated to "A".

I'm new to triggers and would be grateful for an example, if this is even possible.

thanks
ft


Information is not Knowledge, Knowledge is not Wisdom, Wisdom is not Truth, Truth is not Beauty, Beauty is not Love, Love is not Music, Music is the best.

 

[dbomrrsm]

Not 100% sure about this but you may have trouble doing what you want due to "mutating tables".

Whilst the trigger is firing you want it to insert into another table and delete the record that has just been updated.

If this is the case a trigger (I think may hold a lock on the row that is being updated causing the delete of that row to hang.

Depending on why you need to do what you want and probably lods of application needs will deterime how else to handle this.

If allowable I would write the new record to a new table with a trigger and have a trigger on the other table to delete the row from the first table.

Just my observations.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[danvlas]

Since the record is already marked with 'A', why would you need to move it to another table?

Select * From YourTable Where Status <> 'A'

will return only the valid records. Changing the operator to '=' will return the archived records...

Plus, having them in the same table will ease time-specific statistics.

Just a thought.



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html