Home » SQL & PL/SQL » SQL & PL/SQL » TRIGGER PROCEDURE
TRIGGER PROCEDURE [message #36012] Tue, 30 October 2001 04:52 Go to next message
MICK J
Messages: 1
Registered: October 2001
Junior Member
Hi all,

I am trying to execute a simple procedure from within a trigger.

CREATE OR REPLACE DBLINK_PROC

as
cursor_var integer;
return_val integer;
v_datasource varchar2(200);
v_colu varchar2(200);
v_colp varchar2(200);
begin

cursor_var := dbms_sql.open_cursor;
dbms_sql.parse (cursor_var, 'INSERT INTO DATA2@'|| V_DATASOURCE || ' (USR, PWD) VALUES (' || V_COLU || V_COLP||')', DBMS_SQL.native);
return_val := dbms_sql.execute(cursor_var);
dbms_sql.close_cursor(cursor_var);

end;

This procedure as you can see is waiting for three variables which I would like to obtain from a trigger.

Can anyone please advise on the format of the trigger. I guessed it would look something like

CREATE OR REPLACE TRIGGER "DBLINKPROC" AFTER
INSERT
OR UPDATE OF "COLP", "COLU"
ON "SQLCQR"."DATA"
FOR EACH ROW BEGIN

DBLINK_PROC(:NEW.SOURCE, :NEW.COLU, :NEW.COLP);

END;

ANY HELP MUCH APPRECIATED.

----------------------------------------------------------------------
Re: TRIGGER PROCEDURE [message #36016 is a reply to message #36012] Tue, 30 October 2001 05:29 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
CREATE OR REPLACE procedure DBLINK_PROC(p1 varchar2,p2 varchar2,p3 varchar2)

as
cursor_var integer;
return_val integer;
v_datasource varchar2(200);
v_colu varchar2(200);
v_colp varchar2(200);
begin
v_datasource:=p1;
v_colu :=p2;
v_colp :=p3;

cursor_var := dbms_sql.open_cursor;
dbms_sql.parse (cursor_var, 'INSERT INTO DATA2@'|| V_DATASOURCE || ' (USR, PWD) VALUES (' || V_COLU || V_COLP||')', DBMS_SQL.native);
return_val := dbms_sql.execute(cursor_var);
dbms_sql.close_cursor(cursor_var);

end;

----------------------------------------------------------------------
Previous Topic: Re: Selling Sybase, Oracle OCP stuff at lower cost!
Next Topic: question
Goto Forum:
  


Current Time: Fri Mar 29 08:38:57 CDT 2024