Trigger Question?

From: John Stueve <jstueve_at_bbn.com>
Date: 21 Oct 1994 15:57:42 GMT
Message-ID: <388odm$pml_at_info-server.bbn.com>

Hello,

I am having a problem getting a trigger to work. I suspect the problem is related to the number of columns I am specifying. Can anyone confirm or deny this? If this is the problem, are there any easy solutions?

The reason I suspect the number of columns is that if I remove 30 columns at the beginning of the UPDATE list it works, or if I remove 30 columns at the end of the UPDATE list it works. But if I leave all of the columns in I get the following error message at the line containing "ON pdq$sadm.PDQ_EVENT_DATA":

  ORA-00604: error occurred at recursive SQL level 1   ORA-01461: can bind a LONG value only for insert into a LONG column

I am using the following on a HP machine:

    SQL*Plus: Release 3.1.2.3.1
    ORACLE7 Server Release 7.0.15.4.0
    With the procedural option
    PL/SQL Release 2.0.17.1.0

My SQL script contains this:

   drop TRIGGER XYZ_DIST.TRIGGER_3;
   CREATE TRIGGER XYZ_DIST.TRIGGER_3
   AFTER INSERT
   OR UPDATE OF PDQ$AFFILIATE_NAME, PDQ$AGE_AT_ONSET_TIP,

     PDQ$AGE_AT_ONSET_TIQ, PDQ$BIRTH_DATE_TPP, PDQ$BIRTH_DATE_TPQ, 
     PDQ$CANCER, PDQ$CAUSE_OF_DEATH, PDQ$CENTER_ID, 
     PDQ$CITATION, PDQ$COMPANY_CAUSAL, PDQ$COMPANY_REP, 
     PDQ$CONCOMITANT_TEXT, PDQ$CONCOM_DISEASE, PDQ$CONGENITAL_ANOM, 
     PDQ$CONTACT_NUMBER, PDQ$CONTACT_TYPE, PDQ$CORRECTIVE_TX, 
     PDQ$COUNTRY, PDQ$CT_PROTOCOL, PDQ$DEATH, 
     PDQ$DEATH_DATE_TPP, PDQ$DEATH_DATE_TPQ, PDQ$DISABILITY, 
     PDQ$DISTRIBUTOR, PDQ$DROPOUT_REASON, PDQ$EVENT_DUR_TIP, 
     PDQ$EVENT_DUR_TIQ, PDQ$EVENT_STOP_TPP, PDQ$EVENT_STOP_TPQ, 
     PDQ$EVT_SP_01, PDQ$EVT_SP_02, PDQ$EVT_SP_03, 
     PDQ$EVT_SP_04, PDQ$EVT_SP_05, PDQ$EVT_SP_06, 
     PDQ$EVT_SP_07, PDQ$EVT_SP_08, PDQ$EVT_SP_09, 
     PDQ$EVT_SP_10, PDQ$EVT_SP_11, PDQ$EVT_SP_12, 
     PDQ$EVT_SP_13, PDQ$EVT_SP_14, PDQ$EVT_SP_15, 
     PDQ$EVT_SP_16, PDQ$EVT_SP_17, PDQ$EVT_SP_18, 
     PDQ$EVT_SP_19, PDQ$EVT_SP_20, PDQ$FIRST_RECEIVED, 
     PDQ$FROM_REG_COUNTRY, PDQ$FROM_REG_NUMBER, PDQ$HEIGHT_QUANTITY, 
     PDQ$HEIGHT_UNITS, PDQ$HOSPITALIZATION, PDQ$INTERVENTION, 
     PDQ$LAST_MENSES_TPP, PDQ$LAST_MENSES_TPQ, PDQ$LAST_RECEIVED, 
     PDQ$LICENSEE, PDQ$LIFE_THREATENING, PDQ$LITERATURE, 
     PDQ$NARRATIVE1, PDQ$NARRATIVE2, PDQ$NARRATIVE3, 
     PDQ$NOTE, PDQ$ONE_OF_SUS_DRUGS, PDQ$ONSET_TPP, 
     PDQ$ONSET_TPQ, PDQ$OUTCOME, PDQ$OVERDOSE, 
     PDQ$PATIENT_ID, PDQ$PATIENT_INITIALS, PDQ$PREGNANCY_RESULT, 
     PDQ$PREGNANT, PDQ$PREG_ENDDATE_TPP, PDQ$PREG_ENDDATE_TPQ, 
     PDQ$PREG_EXPOSED_TIP, PDQ$PREG_EXPOSED_TIQ, PDQ$PROJECT_ID, 
     PDQ$PROTOCOL_ID, PDQ$RACE, PDQ$RANDOMIZATION, 
     PDQ$RELEVANT_HIST, PDQ$RELEVANT_TESTS, PDQ$REPORTED_DIRECT, 
     PDQ$REPORTER_ADDRESS, PDQ$REPORTER_CAUSAL, PDQ$REPORTER_CITY, 
     PDQ$REPORTER_COUNTRY, PDQ$REPORTER_FNAME, PDQ$REPORTER_LNAME, 
     PDQ$REPORTER_MNAME, PDQ$REPORTER_OCC, PDQ$REPORTER_PHONE, 
     PDQ$REPORTER_POSTAL, PDQ$REPORTER_STATE, PDQ$REPORTER_TITLE, 
     PDQ$REPORTER_TYPE, PDQ$RISK_FACTOR, PDQ$SERIOUS, 
     PDQ$SERIOUS_LOCAL, PDQ$SERIOUS_OTHER, PDQ$SERIOUS_OTHERTXT, 
     PDQ$SEX, PDQ$SITE_ID, PDQ$SOURCE, 
     PDQ$STUDY_DROPOUT, PDQ$STUDY_ID, PDQ$STUDY_PATIENT_ID, 
     PDQ$STUDY_TYPE, PDQ$TERSE_NARRATIVE, PDQ$UNBLINDED, 
     PDQ$UNEXPECTED, PDQ$WEIGHT_QUANTITY, PDQ$WEIGHT_UNITS

   ON pdq$sadm.PDQ_EVENT_DATA
   FOR EACH ROW
   DECLARE
   BEGIN
   INSERT INTO XYZ_DIST.PDQ_DIST_LOG VALUES

      ( :new.pdq$event_id, :new.ct_recid, :new.merge_datetime, 
        'PDQ_EVENT_DATA', 'pdq$sadm',  NULL ); 

   END;
   /

Any help would be greatly appreciated.

John Stueve

-- 

Received on Fri Oct 21 1994 - 16:57:42 CET