CODEWITHSUNDEEP
plsqltriggersoracle11g
RoyalSwish
RoyalSwish

Reputation: 1565

PL/SQL - Trigger only to be called on success of previous trigger

In the database I'm creating I have two triggers which are both fired on BEFORE INSERT on the table Child.

The first trigger checks whether the attempted insert's Child_Birthday value is at most today; so if the value is tomorrow the trigger will raise a User-Defined Exception.

The second trigger is fired after the first trigger which will create a custom ID value for the record inserted.

However, when testing the triggers and I insert tomorrow's date, i.e. 15-JAN-15, the row is still inserted into the table. I assume this is because the second trigger still fires and inserts the record regardless of the first trigger raising an exception (correct me if I'm wrong here).

What I want is that the second trigger only fires if the first trigger doesn't raise an exception.

First trigger:

create or replace TRIGGER BirthdayCheck
BEFORE INSERT ON Child
FOR EACH ROW
DECLARE
  dateError EXCEPTION;
BEGIN
  IF :NEW.Child_Birthday > SYSDATE
  THEN
    RAISE dateError;
  END IF;

  EXCEPTION
    WHEN dateError
    THEN
      DBMS_OUTPUT.PUT_LINE('ERROR: YOU HAVE ATTEMPTED TO INSERT A FUTURE DATE');
END;

Second Trigger:

create or replace TRIGGER ChildID
BEFORE INSERT ON Child
FOR EACH ROW
FOLLOWS BirthdayCheck
DECLARE
  v_SeqNo INT;
  v_SubSurname CHAR(3);
  v_SeqChar VARCHAR(3);
BEGIN
  v_SeqNo := childid_sequence.nextval;
  v_SubSurname := UPPER(SUBSTR(:NEW.Child_Surname,1,3));
  v_SeqChar := TO_CHAR(v_SeqNo);

  IF v_SeqNo < 10
  THEN
    :NEW.ChildID := 'CH' || v_SubSurname || '00' || v_SeqChar;
  ELSIF v_SeqNo >= 10 AND v_SeqNo < 100
  ...
END;

Upvotes: 1

Views: 172

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

The first trigger does not raise an exception. You're trapping the error, displaying a message (if the client is configured to show dbms_output messages, which you can't rely on), amd then... the exception has been squashed, so the insert continues.

Perhaps you want to raise your own exception; in fact you said you were raisning a user-defined exception, when you were not really. You declare and raise one, but then squash it. You want to do something like:

create or replace TRIGGER BirthdayCheck
BEFORE INSERT ON Child
FOR EACH ROW
BEGIN
  IF :NEW.Child_Birthday > SYSDATE
  THEN
    RAISE_APPLICATION_ERROR(-20000,
      'YOU HAVE ATTEMPTED TO INSERT A FUTURE DATE');
  END IF;
END;

Read more about rasing an exception back to the caller.

Upvotes: 4

Related Questions