Before Insert Trigger on a table Code

742505

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.

Anurag Tibrewal

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

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

It is a master Table in my database.
I have no permissions to give unique constraint on the table

Solomon Yakobson

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

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

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

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;

Brian Camire

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.