| TRIGGER PROCEDURE [message #36012] |
Tue, 30 October 2001 04:52  |
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  |
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;
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
