Re: Trigger Question?

In article <388odm$pml_at_info-server.bbn.com> jstueve_at_bbn.com (John Stueve) writes:
>From: jstueve_at_bbn.com (John Stueve)
>Subject: Trigger Question?
>Date: 21 Oct 1994 15:57:42 GMT

>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;
> /

I suggest you normalize your table.

>Any help would be greatly appreciated.
 

>John Stueve

>--
Received on Mon Oct 31 1994 - 16:40:26 CET