Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Location
    Bangalore
    Posts
    8

    Unanswered: Stopping Normal insert into a emp table using a Trigger

    Hi,

    I have 2 tables emp and dept:

    • emp(emp_id, emp_name, emp_joining_date, dept_id);
    • dept(dept_id,dept_name,dept_start_date);


    I am trying to write a trigger which should stop from direct insert into 'emp' table if emp_joining_date is > dept_start_date, i.e.

    lets say, dept_start_date is '01-JAN-2000' and emp_joining_date is '01-FEB-1992'. In such cases if I am trying to do direct:

    Insert Into emp (......);

    It should not allow me to insert. Instead it should log an error msg in some log table.

    I am using Before Insert trigger on 'emp' table where I am comparing both dates and raising an user defiend exception. I am able to log the error message in log table, but the row is also getting inserted into 'emp' table.

    I hope I am able to explain my doubts. Please guide me on this.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You will have to show us your trigger code.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jul 2012
    Location
    Bangalore
    Posts
    8
    Below is the trigger I have wrote:

    --** abhi_emp is the emp table and abhi_dept is dept table

    CREATE OR REPLACE TRIGGER abhi_emp_date_check BEFORE
    INSERT OR UPDATE ON abhi_emp
    REFERENCING OLD AS old_buffer NEW AS new_buffer
    FOR EACH ROW

    DECLARE
    operation NUMBER;
    v_start_date DATE;
    err_num NUMBER;
    err_msg VARCHAR2(100);
    greater_date_exception EXCEPTION;

    BEGIN
    IF INSERTING THEN
    operation := 1;

    SELECT to_date(dept_start_date, 'DD-Mon-YYYY')
    INTO v_start_date
    FROM abhi_dept
    WHERE dept_id = :new_buffer.dept_id;

    IF v_start_date < :new_buffer.emp_joining_date THEN
    NULL;
    ELSE
    Raise greater_date_exception;
    END IF;

    END IF;

    IF UPDATING THEN
    operation := 2;

    SELECT to_date(dept_start_date, 'DD-Mon-YYYY')
    INTO v_start_date
    FROM abhi_dept
    WHERE dept_id = ld_buffer.dept_id;

    IF v_start_date < :new_buffer.emp_joining_date THEN
    NULL;
    ELSE
    Raise greater_date_exception;
    END IF;

    END IF;

    EXCEPTION
    WHEN greater_date_exception THEN
    INSERT
    INTO abhi_trigger_log VALUES
    (
    abhi_seq_trig.nextval,
    'abhi_emp_date_check',
    operation,
    SYSTIMESTAMP,
    'Employee Joing Date: '||:new_buffer.emp_joining_date||' cannot be Greater than Department Start Date:'||v_start_date
    );

    WHEN OTHERS THEN
    err_num := SQLCODE;
    err_msg := SUBSTR(SQLERRM, 1, 100);

    INSERT INTO abhi_trigger_log VALUES
    (
    abhi_seq_trig.nextval,
    'abhi_emp_date_check',
    operation,
    SYSTIMESTAMP,
    err_msg
    );

    END;


    Please tell me if you need some other info aswell.

    Thanks
    Abhinav

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    The answer is really simple. You are catching your custom exception and then not re-raising it. Use the RAISE statement (just by itself) at the end of each exception block to re-raise the original exception.
    Pseudo code:
    Code:
    EXCEPTION
    WHEN greater_date_exception THEN 
      INSERT INTO abhi_trigger_log VALUES ....
      raise;
    WHEN OTHERS THEN
      err_num := SQLCODE; 
      err_msg := SUBSTR(SQLERRM, 1, 100);
    
      INSERT INTO abhi_trigger_log VALUES ....
      raise;
    END;

  5. #5
    Join Date
    Jul 2012
    Location
    Bangalore
    Posts
    8

    Smile

    IT worked. Thanks a lot Dayneo.
    However I used 'PRAGMA AUTONOMOUS_TRANSACTION' in my code to capture error into my log table.
    Pseudo code:

    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    ....
    BEGIN
    ....
    ....
    EXCEPTION
    WHEN greater_date_exception THEN
    INSERT INTO abhi_trigger_log VALUES ....
    COMMIT;
    raise;
    ...
    END;

    Cheers
    Abhinav

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    This would be easy to do with an instead of trigger and replace the table name with a view pointing to the real table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question

    Quote Originally Posted by abhinavbhasker View Post
    ... I used 'PRAGMA AUTONOMOUS_TRANSACTION' in my code to capture error into my log table...
    Hmmm... Using AUTONOMOUS_TRANSACTION directly on a trigger doesn't give me a very warm and fuzzy feeling.

    I think you should rather implement your logging using a package/procedure with AUTONOMOUS_TRANSACTION isolated to the logging code rather than the entire trigger. That's just a feeling... I don't like the idea of commit/rollback from inside a trigger even if it is isolated using AUTONOMOUS_TRANSACTION.

Posting Permissions

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