Forum Stats

  • 3,838,654 Users
  • 2,262,389 Discussions
  • 7,900,724 Comments

Discussions

Before Insert Trigger on a table Code

742505
742505 Member Posts: 33
edited Jan 18, 2010 12:06PM in SQL & PL/SQL
Hi ,
I have a table Dr_seek(Dr_name,reg_no,pat_name)

I want to write a code for a before insert trigger on Dr_seek table.

The trigger should raise an application error(raise_application_error) throwing an error message that "patient's case was already handled by a doctor."

The trigger should restrict new insertions if reg_no is already inserted in the table.
If reg_no is not inserted in table , we have to be able to insert a new record.
Tagged:

Answers

  • Anurag Tibrewal
    Anurag Tibrewal Member Posts: 3,901 Gold Trophy
    Hi,

    Why you want to have a trigger when your requriement can be handled by unique index.

    Please elaborate as why you cannot use unique index.

    Regards
    Anurag
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,601 Red Diamond
    This can not be and should not be done by a trigger. Just create primary/unique key on a patient. This will prevent from inserting same patient twice.

    SY.
  • 742505
    742505 Member Posts: 33
    It is a master Table in my database.
    I have no permissions to give unique constraint on the table
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,601 Red Diamond
    donepudi wrote:
    It is a master Table in my database.
    I have no permissions to give unique constraint on the table
    So you can not add constraint to master table but you can create a trigger on it? Then I suggest to talk to your DBA or better data architect, since such requirements do not make much sense.

    SY.
  • 728534
    728534 Member Posts: 1,386
    Hi,
    Looking at what you have given and what i can understand.
    You dont need trigger for this at all

    Applu unique constraint to reg_no along with not null.
    YOu may also want to have the same constraint on the dr_name too based on your process.

    Regards,
    Bhushan
  • 742505
    742505 Member Posts: 33
    We are creating Trigger for testing purpose. That's why we can't modify the structure of the table. My DBA assigned this to me.
  • 737979
    737979 Member Posts: 112
    Usually, you would create a unique constraint on the table and then in the application look for and handle (display a meaningful error message) when you get a dup val on index / constraint violation error. If you need an example of a trigger raising an application error, here's some sample code:
    CREATE OR REPLACE TRIGGER full_table_scan_check
      BEFORE INSERT ON dr_seek  
      REFERENCING NEW AS NEW
      FOR EACH ROW
    DECLARE
       -- local variables here
       Row_Count PLS_INTEGER;
    BEGIN
       SELECT COUNT(*) INTO Row_Count FROM dr_seek WHERE :NEW.reg_no = reg_no;
       IF Row_Count > 0  THEN
          Raise_Application_Error(-20002, 'patient''s case was already handled by a doctor - validated with a most inefficient technique ;-)');
       END IF;
    END full_table_scan_check;
    737979
  • Brian Camire
    Brian Camire Member Posts: 289 Blue Ribbon
    This trigger won't work correctly for a number of reasons:

    1. You'll get an "ORA-04091: table...is mutating, trigger/function may not see it" if you try to do an INSERT INTO...SELECT (instead of INSERT INTO...VALUES).

    2. The trigger does not handle updates, which could cause the data to violate the desired constraint.

    3. The trigger will not "see" uncommitted changes by other concurrent transactions, which could cause the data to violate the desired constraint.
This discussion has been closed.