Protect record from deletion via trigger
in [Oracle]
|
Prev: ORA-27086: skgfglk: unable to lock file - already in use -> Instance "BOCHUM", status BLOCKED
Next: restore datafile
From: markus.schreyer on 1 Jul 2008 11:20 Hi all, I have a question regarding delete triggers. is there a way to protect one specific record of a table from deletion? The way to do it with a view and instead triggers is not possible. Also i can't raise an APPLICATION_ERROR, since the whole thing should not be visible from application side. Is there a way to do this? CREATE OR REPLACE TRIGGER PROTECT_REC BEFORE DELETE ON TARGETTABLE FOR EACH ROW BEGIN IF (:old.PKCOL=1) THEN Null; END IF; END; Thanks.. :)
From: fitzjarrell on 1 Jul 2008 11:52 On Jul 1, 10:20 am, "markus.schre...(a)gmail.com" <markus.schre...(a)gmail.com> wrote: > Hi all, > > I have a question regarding delete triggers. is there a way to protect > one specific record of a table from deletion? The way to do it with a > view and instead triggers is not possible. Also i can't raise an > APPLICATION_ERROR, since the whole thing should not be visible from > application side. Is there a way to do this? > > CREATE OR REPLACE > TRIGGER > PROTECT_REC > BEFORE DELETE ON TARGETTABLE > FOR EACH ROW > BEGIN > IF (:old.PKCOL=1) THEN > Null; > END IF; > END; > > Thanks.. :) Not without throwing an error since you state you cannot use a view and an instead of trigger. What is the business reason for this 'requirement'? David Fitzjarrell
From: hpuxrac on 1 Jul 2008 13:38 On Jul 1, 11:20 am, "markus.schre...(a)gmail.com" <markus.schre...(a)gmail.com> wrote: > Hi all, > > I have a question regarding delete triggers. is there a way to protect > one specific record of a table from deletion? The way to do it with a > view and instead triggers is not possible. Also i can't raise an > APPLICATION_ERROR, since the whole thing should not be visible from > application side. Is there a way to do this? > > CREATE OR REPLACE > TRIGGER > PROTECT_REC > BEFORE DELETE ON TARGETTABLE > FOR EACH ROW > BEGIN > IF (:old.PKCOL=1) THEN > Null; > END IF; > END; > > Thanks.. :) Tom Kyte says ( and I believe him ) "Triggers are evil" ... therefore don't use them at all or at least minimize their usage. Certainly don't try to protect a specific record within a trigger that is just madness! You could I suppose have a procedure that looks to see if a certain record is there and inserts it/updates it if it has gone away ... execute that on some periodic basis if you must.
From: Mark D Powell on 1 Jul 2008 13:39 On Jul 1, 11:52 am, "fitzjarr...(a)cox.net" <orat...(a)msn.com> wrote: > On Jul 1, 10:20 am, "markus.schre...(a)gmail.com" > > > > > > <markus.schre...(a)gmail.com> wrote: > > Hi all, > > > I have a question regarding delete triggers. is there a way to protect > > one specific record of a table from deletion? The way to do it with a > > view and instead triggers is not possible. Also i can't raise an > > APPLICATION_ERROR, since the whole thing should not be visible from > > application side. Is there a way to do this? > > > CREATE OR REPLACE > > TRIGGER > > PROTECT_REC > > BEFORE DELETE ON TARGETTABLE > > FOR EACH ROW > > BEGIN > > IF (:old.PKCOL=1) THEN > > Null; > > END IF; > > END; > > > Thanks.. :) > > Not without throwing an error since you state you cannot use a view > and an instead of trigger. > > What is the business reason for this 'requirement'? > > David Fitzjarrell- Hide quoted text - > > - Show quoted text - Markus, I agree with David you need to be able to use raise_application_error in the delete row trigger if you have to protect the row in the database. I have never tried this but if you have the Enterpirse Edition you could try setting up a delete policy (VPD/FGAC) that appends a condition to the delete statement that tests the row key value is not this one specific row. In this case no error would be returned. You should always include your edition and full Oracle version in post because the practical responses often depend on this information. HTH -- Mark D Powell --
From: markus.schreyer on 1 Jul 2008 14:05
This is the Oracle Version... sorry for missing that information: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi Setting such a policy sounds intresting. What does such a policy do..? Rewrite the delete statement? hpuxrac: Useing such a procedure would not solve the problem the records needs to be there all the time. I tried to insert the records in the trigger after it's deletion but i got the error 'trigger could probably not see the real values' something like this.. sorry i don't remember the error code. fitzjarrell: I know these 'requirements' sound a little bit weird but there are two problems i have to face: i can't change the underlying application, and changeing the customizing in this case will break my deadline extremly. Another approche to solve the problem could be a multiple table insert together with a sequence. But useing this sequence in the 'INSERT ALL' statement raises the error message Error(450,34): PL/SQL: ORA-02287: sequence number not allowed here Thanks again.. Markus |