Re: Trigger row as parameter for procedure call?

marknyhan_at_hotmail.com (Mark Nyhan) wrote in message news:<3d327694.0202180825.2efd810a_at_posting.google.com>...
> Apologies for still not figuring this out, I have been reading the
> manuals on the ":new.<columnname> syntax". I can reference the columns
> in this way and I know it passes the parameter to the procedure as it
> creates an XML file with the TestID as the name but for some reason it
> breaks down on the first cursor of the procedure it is calling.
>
> CREATE OR REPLACE TRIGGER assessment_trigger
> AFTER INSERT OR UPDATE ON tests
> FOR EACH ROW
> BEGIN
> export.assessment(:new.testid); -- Testid is a VARCHAR2
> END assessment_trigger;
> /
>
> I can use the :old and :new to insert into other tables but not
> This procedure takes in the testid parameter and creates an XML file
> with that name so I know it is being passed. If I take the FOR EACH
> ROW out and hard code in a testid such as export.assessment('ID2354');
> then it works fine, am I missing something obvious?
>
> Thanks again,
> Mark
>
> Mark.

Mark, let us see if I understand correctly: if you call the procedure from the trigger using a variable it fails, but the procedure will work when you pass a constant; however, you made the trigger into a statement level trigger for that test. If you leave in the 'for each row' parameter does the procedure work twice in a row for the constant? If it fails then perhaps the logic in the procedure fails to close a cursor, file, etc...

The other possibility is to make sure the data in the table looks exactly the way you think and more importantly the way the procedure expects, that is, correct column name being passed, trailing nulls or carriage returns not stored in varchar2 column, data value case sensitivity problem ruled out .... The dump(column,base) function can help verify the data.

I would suspect there is something wrong in the procedure rather than with the trigger, but you could try variable := :new.column and use the variable in the call just to verify that if makes no difference.

Are you getting any Oracle error messages? Does the procedure attempt to read the table the trigger is on? I think this could lead to a mutating table error being received. I am not an XML knowledgable person so you may need to post your procedure or at least a description of what it does so someone more knowledgable can recognize probable problem areas.

IMHO
-- Mark D Powell -- Received on Tue Feb 19 2002 - 08:22:48 CST