Home » SQL & PL/SQL » SQL & PL/SQL » trigger problem (oracle 10g)
trigger problem [message #423151] Tue, 22 September 2009 06:16 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
CREATE OR REPLACE TRIGGER TR_INSERT_EMRAPPTDETAILSHIST
AFTER INSERT ON EMRAppointmentDetailsHistory  
FOR EACH ROW
BEGIN
     IF (:new.APPOINTMENT_STATUS_ID <> 9) THEN 
            INSERT INTO EMRAppointmentDetailsHistory 
(APPOINTMENT_ID,PATIENT_ID,REFERED_BY,APPOINTMENT_STATUS_ID)
            VALUES(:new.APPOINTMENT_ID,:new.PATIENT_ID,:new.REFERED_BY,:new.APPOINTMENT_STATUS_ID); 
     ELSE
            INSERT INTO EMRAppDetailsSuppHistory 
(APPOINTMENT_ID,PATIENT_ID,REFERED_BY,APPOINTMENT_STATUS_ID)
            VALUES(:new.APPOINTMENT_ID,:new.PATIENT_ID,:new.REFERED_BY,:new.APPOINTMENT_STATUS_ID); 
      END IF;
END TR_INSERT_EMRAPPTDETAILSHIST;
/



hi the above trigger is failing for some reason as if i want to insert in both tables based on the condition.if it not matches it should insert in first table.if it matches that is if column value is 9 it shold insert in second table.

[Updated on: Tue, 22 September 2009 06:17] by Moderator

Report message to a moderator

Re: trigger problem [message #423152 is a reply to message #423151] Tue, 22 September 2009 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create a view upon the table and use an "instead of" trigger.

Regards
Michel
Re: trigger problem [message #423157 is a reply to message #423152] Tue, 22 September 2009 06:34 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
I create a view as
create view v1 as select * from EMRAppointmentDetailsHistory

then i changed after insert on tablename as
instead of insert on viewname but still no result
Re: trigger problem [message #423158 is a reply to message #423151] Tue, 22 September 2009 06:37 Go to previous messageGo to next message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
what error message your are getting?
Re: trigger problem [message #423160 is a reply to message #423158] Tue, 22 September 2009 06:41 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
iam not getting error message but when i insert into the table
EMRAppointmentDetailsHistory with appointment_status_id as 9
it is still inserting in that table.i dont want that value to insert in that table as it sholud insert in the second table
Re: trigger problem [message #423161 is a reply to message #423160] Tue, 22 September 2009 06:52 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You need to post the output of sqlplus session for us to see what you tried and only then we will be able to help you.

[Updated on: Tue, 22 September 2009 06:55]

Report message to a moderator

Re: trigger problem [message #423162 is a reply to message #423160] Tue, 22 September 2009 06:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes, if this trigger doesn't raise an exception, then the insert that caused it the trigger to fire will still happen.

Quote:
I create a view as
create view v1 as select * from EMRAppointmentDetailsHistory

then i changed after insert on tablename as
instead of insert on viewname but still no result


Did you change the insert statement you used from an insert into the table to an insert into the view?

Re: trigger problem [message #423164 is a reply to message #423162] Tue, 22 September 2009 06:59 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i have given as insert into view only

when iam inserting into view with appoint_status_id other than 9 iam facing exceptions like this

ORA-04088: error during execution of trigger 'CLI_TEST.TR_INSERT_EMRAPPTDETAILSHIST'
ORA-06512: at "CLI_TEST.TR_INSERT_EMRAPPTDETAILSHIST", line 3
ORA-0408
00036. 00000 - "maximum number of recursive SQL levels (%s) exceeded"
*Cause: An attempt was made to go more than the specified number
of recursive SQL levels.
*Action: Remove the recursive SQL, possibly a recursive trigger.
Re: trigger problem [message #423165 is a reply to message #423164] Tue, 22 September 2009 07:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is your view by any chance called 'EMRAppointmentDetailsHistory'

This error is exactly what you'd expect to see if the Instead of Insert trigger on the view fired an insert into the view, instead of an insert into the table.

Re: trigger problem [message #423168 is a reply to message #423165] Tue, 22 September 2009 07:05 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
please check my full trigger as follows


create view v1 as select * from EMRAppointmentDetailsHistory 
/
drop trigger TR_INSERT_EMRAPPTDETAILSHIST
/
CREATE OR REPLACE TRIGGER TR_INSERT_EMRAPPTDETAILSHIST
INSTEAD OF INSERT ON V1  
FOR EACH ROW
BEGIN
     IF (:new.APPOINTMENT_STATUS_ID <> 9) THEN 
            INSERT INTO V1
          (APPOINTMENT_ID,PATIENT_ID,REFERED_BY,APPOINTMENT_STATUS_ID)
            VALUES(:new.APPOINTMENT_ID,:new.PATIENT_ID,:new.REFERED_BY,:new.APPOINTMENT_STATUS_ID); 
     ELSE
            INSERT INTO EMRAppDetailsSuppHistory 
            (APPOINTMENT_ID,PATIENT_ID,REFERED_BY,APPOINTMENT_STATUS_ID)
            VALUES(:new.APPOINTMENT_ID,:new.PATIENT_ID,:new.REFERED_BY,:new.APPOINTMENT_STATUS_ID); 
      END IF;
END TR_INSERT_EMRAPPTDETAILSHIST;
/



Re: trigger problem [message #423169 is a reply to message #423164] Tue, 22 September 2009 07:06 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
rajasekhar857 wrote at Tue, 22 September 2009 17:11
iam not getting error message

and then

[quote=rajasekhar857 wrote at Tue, 22 September 2009 17:29ORA-04088: error during execution of trigger 'CLI_TEST.TR_INSERT_EMRAPPTDETAILSHIST'
ORA-06512: at "CLI_TEST.TR_INSERT_EMRAPPTDETAILSHIST", line 3
ORA-0408
00036. 00000 - "maximum number of recursive SQL levels (%s) exceeded"
*Cause: An attempt was made to go more than the specified number
of recursive SQL levels.
*Action: Remove the recursive SQL, possibly a recursive trigger. [/quote]

You seem to be contradicting yourself. I requested earlier you to post the output of SQLPLUS session so that we all can see what is happening and try to help you instead of just guessing.
Re: trigger problem [message #423172 is a reply to message #423168] Tue, 22 September 2009 07:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
CREATE OR REPLACE TRIGGER TR_INSERT_EMRAPPTDETAILSHIST
INSTEAD OF INSERT ON V1  
FOR EACH ROW
BEGIN
     IF (:new.APPOINTMENT_STATUS_ID <> 9) THEN 
            INSERT INTO V1
          (APPOINTMENT_ID,PATIENT_ID,REFERED_BY,APPOINTMENT_STATUS_ID)
            VALUES(:new.APPOINTMENT_ID,:new.PATIENT_ID,:new.REFERED_BY,:new.APPOINTMENT_STATUS_ID); 
     ELSE
            INSERT INTO EMRAppDetailsSuppHistory 
            (APPOINTMENT_ID,PATIENT_ID,REFERED_BY,APPOINTMENT_STATUS_ID)
            VALUES(:new.APPOINTMENT_ID,:new.PATIENT_ID,:new.REFERED_BY,:new.APPOINTMENT_STATUS_ID); 
      END IF;
END TR_INSERT_EMRAPPTDETAILSHIST;


Were you actually thinking when you wrote this trigger, and did you actually read my previous post to the end?.

For your information, I wrote:Quote:
his error is exactly what you'd expect to see if the Instead of Insert trigger on the view fired an insert into the view, instead of an insert into the table.


What does your post show us?

A view called V1, inside which is an insert into V1.

Just tink about what this is going to do?
Re: trigger problem [message #423175 is a reply to message #423168] Tue, 22 September 2009 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rajasekhar857 wrote on Tue, 22 September 2009 14:05
please check my full trigger as follows


create view v1 as select * from EMRAppointmentDetailsHistory 
/
drop trigger TR_INSERT_EMRAPPTDETAILSHIST
/
CREATE OR REPLACE TRIGGER TR_INSERT_EMRAPPTDETAILSHIST
INSTEAD OF INSERT ON V1  
FOR EACH ROW
BEGIN
     IF (:new.APPOINTMENT_STATUS_ID <> 9) THEN 
            INSERT INTO V1
          (APPOINTMENT_ID,PATIENT_ID,REFERED_BY,APPOINTMENT_STATUS_ID)
            VALUES(:new.APPOINTMENT_ID,:new.PATIENT_ID,:new.REFERED_BY,:new.APPOINTMENT_STATUS_ID); 
     ELSE
            INSERT INTO EMRAppDetailsSuppHistory 
            (APPOINTMENT_ID,PATIENT_ID,REFERED_BY,APPOINTMENT_STATUS_ID)
            VALUES(:new.APPOINTMENT_ID,:new.PATIENT_ID,:new.REFERED_BY,:new.APPOINTMENT_STATUS_ID); 
      END IF;
END TR_INSERT_EMRAPPTDETAILSHIST;
/

Inside the trigger, you must insert into the table not into the view.

Regards
Michel

Re: trigger problem [message #423179 is a reply to message #423151] Tue, 22 September 2009 07:37 Go to previous message
nirmalorafaq
Messages: 2
Registered: September 2009
Location: Bangalore
Junior Member
The trigger event used is AFTER INSERT on the same table, that is one record is already inserted irrespective of your checking condition.
Previous Topic: Database script for Insert statements
Next Topic: PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL
Goto Forum:
  


Current Time: Sat Dec 10 08:40:37 CST 2016

Total time taken to generate the page: 0.07600 seconds