Home » SQL & PL/SQL » SQL & PL/SQL » Trigger exception (oracle 10g)
Trigger exception [message #395633] Thu, 02 April 2009 02:12 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi,
i have created a trigger but it is failing and giving like this
SQL> CREATE OR REPLACE TRIGGER "TR_Update_EMRPatientProblems" AFTER UPDATE OF ENCOUNTER_ID ON EMRPATIENTPROBLEMS
  2  FOR EACH ROW
  3  BEGIN
  4     IF (:old.ENCOUNTER_ID<>' ') OR (:old.STATUS=0) THEN
  5        INSERT INTO EMRPATIENTPROBLEMSHISTORY 
                 (PROBLEM_ID,ONSET_DATE,RESOLVED_DATE,PATIENT_ID,COMMENTS,PROBLEM_DESC,CREATOR,PR
OBLEM_CODE,PROBLEM_NAME,IS_CODED,
  7                  PRB_TYPE_ID,STATUS,ENCOUNTER_ID)
  8   VALUES
  9          (:old.PROBLEM_ID,:old.ONSET_DATE,RESOLVED_DATE,:old.PATIENT_ID,:old.COMMENTS,:old.PROBL
EM_DESC,:old.CREATOR,:old.PROBLEM_CODE,
 10           :old.PROBLEM_NAME,:old.IS_CODED,:old.PRB_TYPE_ID,:old.STATUS,ENCOUNTER_ID)
 11      END IF;
 12  END TR_Update_EMRPatientProblems;
 13  /

Warning: Trigger created with compilation errors.

SQL> sho err
No errors.
SQL> set lines 500
SQL> select * from emrpatientproblems;

PROBLEM_ID ONSET_DAT RESOLVED_ PATIENT_ID
---------- --------- --------- --------------------
COMMENTS
----------------------------------------------------------------------------------------------------
PROBLEM_DESC                                                                                                                    
----------------------------------------------------------------------------------------------------
PROBLEM_NAME                                                                                           IS_CODED PRB_TYPE_ID     
----------------------------------------------------------------------------------------------------
      1523 02-MAR-09 09-MAR-09 1545
uuu
021.9-Unspecified tularemia                                                                                                     
Unspecified tularemia                                                                                                           


SQL> update emrpatientproblems set encounter_id=1900 where encounter_id=1545;
update emrpatientproblems set encounter_id=1900 where encounter_id=1545
       *
ERROR at line 1:
ORA-04098: trigger 'SCOTT.TR_Update_EMRPatientProblems' is invalid and failed re-validation




Please help me out why its happening like this
Re: Trigger exception [message #395643 is a reply to message #395633] Thu, 02 April 2009 02:37 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Warning: Trigger created with compilation errors.

SQL> sho err
No errors.



I find it difficult to believe that Trigger creation mentioned there was an error but sho err does not show it.
Re: Trigger exception [message #395645 is a reply to message #395633] Thu, 02 April 2009 02:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
select *
from user_errors

You probably ran the trigger in a script with multiple statements. The SHOW ERRORS then only shows the error for the last executed statement.
Re: Trigger exception [message #395657 is a reply to message #395633] Thu, 02 April 2009 03:03 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_Update_EMRPatientProblems" 

REALLY don't create object names in mixed case - especially objects that don't need it as they can't ever be called directly. It just makes work for the poor developers who get to maintain your code later.
Re: Trigger exception [message #395664 is a reply to message #395657] Thu, 02 April 2009 03:21 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
when i run select * from user_errors the message is like this
TR_Update_EMRPatientProblems   TRIGGER               2          3          7
PL/SQL: SQL Statement ignored
ERROR                  0

TR_Update_EMRPatientProblems   TRIGGER               3         10          5
PLS-00103: Encountered the symbol "TR_UPDATE_EMRPATIENTPROBLEMS" when expecting one of the following

   if
ERROR                103
Re: Trigger exception [message #395666 is a reply to message #395657] Thu, 02 April 2009 03:26 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
SQL> CREATE OR REPLACE TRIGGER TR_Update_EMRPatientProblems AFTER UPDATE OF ENCOUNTER_ID ON EMRPATIE
NTPROBLEMS
  2  FOR EACH ROW
  3  BEGIN
  4     IF (:old.ENCOUNTER_ID<>' ') OR (:old.STATUS=0) THEN
  5        INSERT INTO EMRPATIENTPROBLEMSHISTORY 
  6                 (PROBLEM_ID,ONSET_DATE,RESOLVED_DATE,PATIENT_ID,COMMENTS,PROBLEM_DESC,CREATOR,PR
OBLEM_CODE,PROBLEM_NAME,IS_CODED,
  7                  PRB_TYPE_ID,STATUS,ENCOUNTER_ID)
  8   VALUES
  9          (:old.PROBLEM_ID,:old.ONSET_DATE,RESOLVED_DATE,:old.PATIENT_ID,:old.COMMENTS,:old.PROBL
EM_DESC,:old.CREATOR,:old.PROBLEM_CODE,
 10           :old.PROBLEM_NAME,:old.IS_CODED,:old.PRB_TYPE_ID,:old.STATUS,ENCOUNTER_ID)
 11      END IF;
 12  END TR_Update_EMRPatientProblems;
 13  /

Warning: Trigger created with compilation errors.

SQL> sho err
Errors for TRIGGER TR_UPDATE_EMRPATIENTPROBLEMS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/7      PL/SQL: SQL Statement ignored
3/7      PL/SQL: SQL Statement ignored
9/5      PL/SQL: ORA-00933: SQL command not properly ended
9/5      PL/SQL: ORA-00933: SQL command not properly ended
10/5     PLS-00103: Encountered the symbol "TR_UPDATE_EMRPATIENTPROBLEMS"
         when expecting one of the following:
         if

10/5     PLS-00103: Encountered the symbol "TR_UPDATE_EMRPATIENTPROBLEMS"
         when expecting one of the following:
         if

Re: Trigger exception [message #395667 is a reply to message #395664] Thu, 02 April 2009 03:27 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I think you have missed semicolon in line 10

      :old.PROBLEM_NAME,:old.IS_CODED,:old.PRB_TYPE_ID,:old.STATUS,ENCOUNTER_ID)


Re: Trigger exception [message #395670 is a reply to message #395667] Thu, 02 April 2009 03:31 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
after semicolon the exceptions are like this
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/7 PL/SQL: SQL Statement ignored
3/7 PL/SQL: SQL Statement ignored
7/71 PL/SQL: ORA-00984: column not allowed here
9/5 PL/SQL: ORA-00933: SQL command not properly ended
10/5 PLS-00103: Encountered the symbol "TR_UPDATE_EMRPATIENTPROBLEMS"
when expecting one of the following:
if
Re: Trigger exception [message #395685 is a reply to message #395633] Thu, 02 April 2009 04:40 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
10           :old.PROBLEM_NAME,:old.IS_CODED,:old.PRB_TYPE_ID,:old.STATUS,ENCOUNTER_ID)


notice something different about the last item?
Re: Trigger exception [message #395696 is a reply to message #395685] Thu, 02 April 2009 05:16 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi i got the trigger in oracle like tis and in need it in sql server also but i dont know much about it,can anyone help me what i need yo specify for mssql in if condition for the folowing trigger

CREATE OR REPLACE TRIGGER TR_Update_EMRPatientProblems AFTER UPDATE OF ENCOUNTER_ID ON EMRPATIENTPROBLEMS
FOR EACH ROW
BEGIN
   IF (:old.ENCOUNTER_ID<> NVL(:old.ENCOUNTER_ID,0)) AND (:new.STATUS=0) AND (:old.ENCOUNTER_ID<> :new.ENCOUNTER_ID) THEN
      INSERT INTO EMRPATIENTPROBLEMSHISTORY 
               (PROBLEM_ID,ONSET_DATE,RESOLVED_DATE,PATIENT_ID,COMMENTS,PROBLEM_DESC,CREATOR,PROBLEM_CODE,PROBLEM_NAME,IS_CODED,
                PRB_TYPE_ID,STATUS,ENCOUNTER_ID)
      VALUES
	       (:old.PROBLEM_ID,:old.ONSET_DATE,:old.RESOLVED_DATE,:old.PATIENT_ID,:old.COMMENTS,:old.PROBLEM_DESC,:old.CREATOR,:old.PROBLEM_CODE,
	        :old.PROBLEM_NAME,:old.IS_CODED,:old.PRB_TYPE_ID,:old.STATUS,:old.ENCOUNTER_ID);
   END IF;
END TR_Update_EMRPatientProblems;
/
	       
CREATE TRIGGER TR_Update_EMRPatientProblems
ON EMRPatientsPersnlInfo  
FOR UPDATE  
AS  
IF (:old.ENCOUNTER_ID<> NVL(:old.ENCOUNTER_ID,0)) AND (:new.STATUS=0) AND  (:old.ENCOUNTER_ID<> :new.ENCOUNTER_ID) 
INSERT EMRPATIENTPROBLEMSHISTORY 
SELECT  
 PROBLEM_ID,ONSET_DATE,RESOLVED_DATE,PATIENT_ID,COMMENTS,PROBLEM_DESC,CREATOR,PROBLEM_CODE,PROBLEM_NAME,IS_CODED,
 PRB_TYPE_ID,STATUS,ENCOUNTER_ID
GO

Re: Trigger exception [message #395725 is a reply to message #395696] Thu, 02 April 2009 07:57 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I don't understand. You have a non working trigger in Oracle and you want it in SQL Server?

First, you may want to post the question in SQL Server fourm.

Second, if it doesn't work, you might as well just close your eyes and hit random keys on the keyboard to have it give the same results as the non-working Oracle trigger.

Third, what is "GO" and where is the rest of the code?
Re: Trigger exception [message #395727 is a reply to message #395725] Thu, 02 April 2009 08:18 Go to previous message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
'go' is the command available in sql server just like oracle's /
secondly my oracle trigger is working fine don't bother about it.
Previous Topic: How to use Substitution Variables in Pl/SQL
Next Topic: how to replace & with and in the given string
Goto Forum:
  


Current Time: Sat Dec 10 16:19:36 CST 2016

Total time taken to generate the page: 0.08918 seconds