Home » SQL & PL/SQL » SQL & PL/SQL » CREATE TRIGGER
CREATE TRIGGER [message #36215] Sun, 11 November 2001 19:49 Go to next message
Steve T
Messages: 1
Registered: November 2001
Junior Member
I have the following trigger:
(1)
CREATE OR REPLACE TRIGGER report_request_insert
AFTER INSERT ON report_request
FOR EACH ROW
BEGIN
runreport(str => :new.cmd, id => :new.id);
END;

where runreport is defined as a function referenceing an external procedure:

(2)
CREATE OR REPLACE FUNCTION runreport(str IN VARCHAR, id IN BINARY_INTEGER)
RETURN BINARY_INTEGER AS
EXTERNAL LIBRARY somelibrary
NAME "runreport";

and somelibrary.runreport is an external procedure written in C.

Now, if I create the function 'runreport' as specified above (2), I can use it through the sqlplus prompt.

However, once I try to create a trigger that uses the function, as specified above (1), I get the followin g error:

"Errors for TRIGGER REPORT_REQUEST_INSERT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/4 PLS-00221: 'RUNREPORT' is not a procedure or is undefined
2/4 PL/SQL: Statement ignored

Warning: Trigger created with compilation errors."

I have checked the SQL syntax of the create trigger statement but can not see anythign wrong with it.

Any help will be greatly appreciated.

----------------------------------------------------------------------
Re: CREATE TRIGGER [message #36217 is a reply to message #36215] Sun, 11 November 2001 23:21 Go to previous message
Rob Baillie
Messages: 33
Registered: November 2001
Member
I don't know anything about external libraries... so forgive me if I'm way off the mark here, but this likes like a pretty fundamental flaw.

Since you've defined your 'runreport' as a FUNCTION you must run it using:

ln_target_variable := runreport( str => :new.cmd, id => :new.id);

or similar.

If this is not how you wish to use 'runreport' (suspected, as you don't return anything from the FUNCTION), then you probably want to define 'runreport' as a PROCEDURE instead.

Hope this helps
Rob

----------------------------------------------------------------------
Previous Topic: how to insert data from ref table(collection) into a table in a loop
Next Topic: How to Execute the PL/SQL i.c cursor from Report .
Goto Forum:
  


Current Time: Thu Mar 28 04:35:46 CDT 2024