Home » SQL & PL/SQL » SQL & PL/SQL » bind variable
bind variable [message #258956] Mon, 13 August 2007 20:43 Go to next message
Rafeek
Messages: 159
Registered: April 2007
Location: egypt
Senior Member
hi all
i create trigger on table the columns i will work with it are variable (the columns name in table con_col ) .
i create :new.column_name at run time when i create trigger
i create ':new.column_name' as varchar2 how can it pass the value of the ':new.column_name'audit_pkg.check_val


CREATE OR REPLACE TRIGGER t1x1
AFTER INSERT OR UPDATE OR DELETE
ON t1
FOR EACH ROW
DECLARE
n NUMBER (2);
coln varchar2(100);
p1 varchar2(1000);
p2 varchar2(1000);
p3_new varchar2(100) :=':NEW.';
P3_OLD VARCHAR2(100) := ':OLD.';
cursor_name INTEGER;
rows_processed INTEGER;

CURSOR col_cursor
IS
SELECT col_nam
FROM con_col
WHERE id_tab = (SELECT ID
FROM con_tab

WHERE tab_nam = 'T2');
BEGIN
FOR colnam IN col_cursor
LOOP
coln:= colnam.col_nam;
--P3_NEW := P3_NEW||COLN;
P3_NEW :=':new.'||COLN;P3_OLD := P3_OLD||COLN;
p1:=P3_NEW;
p2:= P3_OLD;

-- p2:= NEW.||COLN;

--OWA_UTIL.BIND_VARIABLES(bv1Name=>P1);

audit_pkg.check_val('t2',coln,p1,p2);

END LOOP;
END trigger101;
/
Re: bind variable [message #258970 is a reply to message #258956] Mon, 13 August 2007 23:56 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
You cannot access to :NEW and :OLD in dynamic SQL, as they are not in its scope.
Maybe you could workaround it as described on AskTom generic trigger for auditing column level changes thread.
Previous Topic: SELECT <id> INTO KEY_VAR fails
Next Topic: ORA-01720 grant option does not exist
Goto Forum:
  


Current Time: Fri Dec 02 14:22:04 CST 2016

Total time taken to generate the page: 0.21440 seconds