Delete row and insert it into another table for logging 1

[Delphiwhat]

hi guys

Can someone help me out with some (basic i guess)

I want to create an on-delete trigger on table 1 that when a row is deleted on table 1 the row data is captured on table 2 before the row is successfully deleted.

any ideas

Thanks in advance
steve

 

[BJCooperIT]

A "BEFORE DELETE" trigger will fire before the DELETE operation is executed.

CREATE OR REPLACE TRIGGER log_table1_deletion
BEFORE DELETE
    ON table1
    FOR EACH ROW
BEGIN
-- Insert record into audit table
    INSERT INTO table2
     ( user_id,
       datetime_deleted,
       field1,
       ... )
    VALUES
     ( user,
       sysdate,
       :old.field1,
       ... );
END;

Restrictions:
You can not create a BEFORE trigger on a view.
You can update the :NEW values.
You can not update the :OLD values.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[Delphiwhat]

Thanks

BJCooperIT you are a star. Helped me out big time.

steve

 

[Delphiwhat]

BJ hope you don't mind just another quick one. Being a none Oracle person I'm getting stuck on the basics.....

1 problem....

User details are inserted into a Table called 'users'.
A table called 'Users_stats' holds other information about the client.

When a row is inserted into the 'users' table I need to check if the users.user_id is present in the 'users_stats' table. If it is present I need to delete the row in the users_stats table otherwise do nothing.


I know it sounds probably simple but as a newbie I'm making slow progress and going around in circles.

Thanks in advance

steve

 

[SantaMufasa]

Delphi,

I answered your most recent post (before seeing this post) in your other sibling thread to this one. We typically do not double post questions. Since your most recent question is a separate issue, it deserves a separate thread. Therefore, I recommend your clicking [Inappropriate post? If so, Red Flag it], above on just your most recent post in this thread. That way, no one will get confused, and you can deal with your second issue in the other thread.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.