CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-insertdatabase-trigger
Channon rye
Channon rye

Reputation: 1

Before Delete Trigger For Backup

So I am trying to create backup before row gets deleted from the table on other table. What am I doing wrong here? I keep getting inserting error.

create or replace TRIGGER test_dummy_backup_insert before delete on test_dummy
FOR EACH ROW 

begin
    insert into test_dummy_back_up ('userid', 'firstname', 'lastname','salary','location')
    select * from test_dummy where userid = @userid;
end;

ERROR -

DELETE FROM "PRACTICE"."TEST_DUMMY" WHERE ROWID = 'AAAE/FAAFAAAADFAAD'
AND ORA_ROWSCN = '5429140' and ( "FIRSTNAME" is null or "FIRSTNAME" is
not null ) ORA-04098: trigger 'PRACTICE.TEST_DUMMY_BACKUP_INSERT' is
invalid and failed re-validation

Upvotes: 0

Views: 1471

Answers (2)

Tushar Kesarwani
Tushar Kesarwani

Reputation: 596

The quotes around field names are wrong.

However, if you want to store data changes, you'll need to understand triggers better. As already noted, there are pseudo tables you should be looking at. In oracle, those are :NEW and :OLD.

Oracle has rather extensive audit tools. More here:https://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50000

This should be like this -

Insert into backup_table 
(userid, firstname, lastname, salary, location)
values(old.userid, old.firstname, old.lastname, old.salary, old.location)

Upvotes: 0

GMB
GMB

Reputation: 222582

@userid is not a thing for Oracle in your query. Also, the column names should not be surrounded with single quotes (which are used for literal strings).

To access the values on the row that is about to be deleted, you don't need to select from the table itself (which Oracle, like most other databases, does not support anyway - that's famous error ORA-04091 - table is mutating). You can use pseudo-table old instead:

create or replace trigger test_dummy_backup_insert 
before delete on test_dummy
for each row 
begin
    insert into test_dummy_back_up 
        (userid, firstname, lastname, salary, location)
    values(old.userid, old.firstname, old.lastname, old.salary, old.location)
end;

Upvotes: 1

Related Questions