Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Red face Unanswered: Writing a Trigger in Oracle which should stop the current Transaction

    Dear Friends,

    I want to know how to stop the current transaction using a trigger?

    My requirement is when a record from Table1 is deleted, before deleting this record, based on the value of PartnerType field's value I should check in either Customer or Supplier table if this record is existing. If the record is existing, I should not delete the current record.

    Logic is something like this... but I don't know the syntax.

    CREATE OR REPLACE TRIGGER TESTTRIGGER
    BEFORE DELETE ON ADDRESSESBYBUSINESSPARTNER
    FOR EACH ROW
    BEGIN
    IF (:OLD.PARTNERTYPE = 'SUPPLIER') then
    IF EXISTS (SELECT SUPPLIER_ID FROM SUPPLIER WHERE SUPPLIER_ID = :OLD.PARTNERCODE AND ADDRESSCODE = :OLD.POSTALADDRESS) THEN
    ROLLBACK TRANSACTION
    END IF
    ELSEIF (:OLD.PARTNERTYPE = 'CUSTOMER') then
    IF EXISTS (SELECT CUSTOMER_ID FROM CUSTOMER WHERE CUSTOMER_ID = :OLD.PARTNERCODE AND ADDRESSCODE = :OLD.POSTALADDRESS) THEN
    ROLLBACK TRANSACTION
    END IF
    END IF
    END;

    This doesn't work. Can someone please guide me how to write such kind of Trigger.

    Regards,
    Satish

  2. #2
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    solution provided ...

    have a look at the following coding, which works.
    personally i prefer to raise errors in the triggers, so that the application could catch such errors via exeption handling ...


    SQL> drop table test;

    Tabelle wurde gel÷scht.

    SQL> create table test (id integer,
    2 name varchar2(10) default 'Pre',
    3 create_date date default sysdate);

    Tabelle wurde angelegt.

    SQL>
    SQL> insert into test (id) values (1);

    1 Zeile wurde erstellt.

    SQL> insert into test (id) values (12);

    1 Zeile wurde erstellt.

    SQL> insert into test (id) values (21);

    1 Zeile wurde erstellt.

    SQL> insert into test (id) values (221);

    1 Zeile wurde erstellt.

    SQL>
    SQL>
    SQL> CREATE OR REPLACE TRIGGER TESTTRIGGER
    2 BEFORE DELETE ON test
    3 FOR EACH ROW
    4 BEGIN
    5 if :OLD.id <20 then
    6 raise_application_error (-20001,'DELETE not allowed');
    7 end if;
    8 END testtrigger;
    9 /

    Trigger wurde erstellt.

    SQL> show errors;
    Keine Fehler.
    SQL>
    SQL> delete test where id=1;
    delete test where id=1
    *
    FEHLER in Zeile 1:
    ORA-20001: DELETE not allowed
    ORA-06512: in "SYSTEM.TESTTRIGGER", Zeile 3
    ORA-04088: Fehler bei der Ausf³hrung von Trigger 'SYSTEM.TESTTRIGGER'


    SQL> delete test where id>20;

    2 Zeilen wurden gel÷scht.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •