| trigger problem [message #423151] |
Tue, 22 September 2009 06:16  |
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 #423157 is a reply to message #423152] |
Tue, 22 September 2009 06:34  |
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 #423160 is a reply to message #423158] |
Tue, 22 September 2009 06:41 |
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 #423162 is a reply to message #423160] |
Tue, 22 September 2009 06:55 |
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 |
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 |
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 |
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 |
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 |
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 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
rajasekhar857 wrote on Tue, 22 September 2009 14:05please 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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
