9 Replies - 366 Views - Last Post: 19 September 2018 - 07:35 AM Rate Topic: -----

#1 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

before delete trigger for backup

Posted 18 September 2018 - 09:36 PM

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;




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. :(

Is This A Good Question/Topic? 0
  • +

Replies To: before delete trigger for backup

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: before delete trigger for backup

Posted 18 September 2018 - 09:41 PM

Please copy/paste the error message here.
Was This Post Helpful? 0
  • +
  • -

#3 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: before delete trigger for backup

Posted 18 September 2018 - 09:58 PM

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


pk is userid btw.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: before delete trigger for backup

Posted 18 September 2018 - 10:03 PM

Ugly.

Any particular reason you want to rely on a trigger instead of just doing a quick insert call?
Was This Post Helpful? 0
  • +
  • -

#5 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: before delete trigger for backup

Posted 18 September 2018 - 10:12 PM

because app I am making is simplified with datagridview deleting using command builder. app users won't have an option to insert the line they are deleting before they delete the line. they won't use it even if there was one so I am relying on trigger.
Was This Post Helpful? 0
  • +
  • -

#6 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4235
  • View blog
  • Posts: 13,423
  • Joined: 08-June 10

Re: before delete trigger for backup

Posted 19 September 2018 - 12:39 AM

Quote

insert into test_dummy_back_up ('userid', 'firstname', 'lastname','salary','location')

That's not a valid insert statement.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6813
  • View blog
  • Posts: 28,190
  • Joined: 12-December 12

Re: before delete trigger for backup

Posted 19 September 2018 - 12:40 AM

The quotes around field names are wrong.

Study triggers, and how you can access DELETED/OLD data. (Study rather than guessing.)
Was This Post Helpful? 0
  • +
  • -

#8 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: before delete trigger for backup

Posted 19 September 2018 - 06:46 AM

So, you essentially want an audit table? We use them because of government regulations, but you didn't mention which RDBMS you are using. The link is a guess for which one.
Was This Post Helpful? 0
  • +
  • -

#9 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7471
  • View blog
  • Posts: 15,493
  • Joined: 16-October 07

Re: before delete trigger for backup

Posted 19 September 2018 - 06:59 AM

@astonecipher: Actually, the error posted later contains the definitive clue: ORA-04098.

So, Oracle has a rather extensive audit tools. More here: https://docs.oracle.....htm#TDPSG50000

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.
Was This Post Helpful? 0
  • +
  • -

#10 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: before delete trigger for backup

Posted 19 September 2018 - 07:35 AM

I wasn't paying attention to the error message after I figured out what they were after!!!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1