|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
July 7th, 2004, 02:58 AM
|
|||
|
|||
|
Before Delete Trigger
Hi!
I have a realy annoying problem... here is the code... Code:
CREATE OR REPLACE TRIGGER RechtMgrDel BEFORE DELETE ON benrecht FOR EACH ROW BEGIN if :old.gname<>'--' then RAISE_APPLICATION_ERROR(-20901,'dont do it!'); end if; END; / SHOW ERROR; if the trigger finds a row, it is not allowed to delete, the message is displayed and the triggeractions are rolledback... I want the triger to delete the rows, which are allowed to delete and to leave the rows, which are not allowed to delete.. HOW???? 
|
|
#2
July 7th, 2004, 03:08 AM
|
||||
|
||||
|
Use an INSTEAD OF trigger that does something instead of deleting. See http://www.psoug.org/reference/instead_of_trigger.html
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins
|
|
#4
July 7th, 2004, 03:51 AM
|
|||
|
|||
|
Yes. So
Code:
create view mc as select * from benrecht Code:
CREATE OR REPLACE TRIGGER RechtMgrDel
instead of DELETE ON mc
FOR EACH ROW
BEGIN
if :old.gname<>'--' then
delete from benrecht
where pk = :old.pk;
end if;
END;
/
SHOW ERROR;
|
|
#7
July 7th, 2004, 08:01 AM
|
|||
|
|||
|
I would like to do it as follows.
Please be noted that it is only pseudo code not the complete implementation, you can write the code if you think it is worth for you. CREATE OR REPLACE TRIGGER RechtMgrDel BEFORE DELETE ON benrecht FOR EACH ROW BEGIN Check_Validity_Of_Data (parameter list); END; / SHOW ERROR; CREATE OR REPLACE PROCEDURE Check_Validity_Of_Data (pamater list) IS ..... ..... ..... BEGIN ..... ..... ..... IF param1 = this_value THEN RAISE_APPLICATION_ERROR('This record could not be deleted.....'); END IF; END Check_Validity_Of_Data; / Regards,
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Before Delete Trigger |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
sorry, I was in MsSQL mode .... you are right, my fault
Linear Mode
