CODEWITHSUNDEEP
oracle-databasetriggersoracle10g
Sangeet Menon
Sangeet Menon

Reputation: 9915

Store the data of deleted record using trigger

I want to write a trigger which fires on deletion of a record from a table, and inserts a record in another table and uses the details of the record deleted.

Database : Oracle 10g

My trigger looked like this

CREATE or REPLACE TRIGGER myTrigger
AFTER DELETE
    ON myTable
    REFERENCING NEW AS old_tab
    FOR EACH ROW
BEGIN
    INSERT INTO ACTIVITYLOG values ('ADMIN',:old_tab.tabletID,'MIGRATION','ERROR','TEST','T','NIL',sysdate)
END;

here :old_tab.tabletID the tabletID is the column of the table myTable in which deletion is done. I want to save the I and a log that it was deleted.

But when I try deleting a record I get the following error

Error code 4098, SQL state 42000: ORA-04098: trigger 'DB.MYTRIGGER' is invalid and failed re-validation

P.S. Ran the trigger creation in NetBeans SQL Editor.

Here is the,

EDIT

STRUCTURE OF myTable (Table deletion occurs)

tabletID varchar2(15) PRIMARY KEY
tabletName varchar2(100)

STRUCTURE OF ACTIVITYLOG

username varchar2(15)
tabletKey varchar2(15)
page_ref varchar2(100)
errors varchar2(100)
remarks varchar2(100)
operationcode char(2)
lastupdateip varchar2(20)
lastupdatedate date

Sorry don't have access to SQL PLUS EDITOR.

Upvotes: 1

Views: 5480

Answers (2)

APC
APC

Reputation: 146249

You should use the :OLD values rather than the :NEW values. The :NEW values in a DELETE trigger (whether BEFORE or AFTER) are blank. This makes sense, because if you think about it the record has logically ceased to exist at this point.

However that is not a source of compilation errors.

"still the same error shows up on deletion. "

I suppose we could spend all day guessing what's wrong so let's stop now. You can discover the compilation errors with this simple query:

select * from user_errors
where name = 'MYTRIGGER'
and type = 'TRIGGER'

"I changed the :NEW to :OLD, and added a semicolan and ran it on SQL PLUS, and that did the trick"

For the benefit of future here is a version of the trigger which will compile and which will correctly write the required values:

CREATE or REPLACE TRIGGER myTrigger 
AFTER DELETE 
    ON myTable 
    REFERENCING OLD AS old_tab 
    FOR EACH ROW 
BEGIN 
    INSERT INTO ACTIVITYLOG values ('ADMIN',:old_tab.tabletID,'MIGRATION','ERROR','TEST','T','NIL',sysdate); 
END; 
/

Upvotes: 2

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

The problem is this:

REFERENCING NEW AS old_tab

You've redefined the NEW values with the label "old_tab". This is somewhat like adding #define FALSE TRUE to the top of a program.

Add a semicolon after the insert statement

Because you're using an AFTER DELETE trigger, you only need to access the :OLD values, e.g.:

CREATE or REPLACE TRIGGER myTrigger
AFTER DELETE
    ON myTable
    FOR EACH ROW
BEGIN
    INSERT INTO ACTIVITYLOG values ('ADMIN',:OLD.tabletID,'MIGRATION','ERROR','TEST','T','NIL',sysdate);
END;

Upvotes: 0

Related Questions