On Jul 24, 5:31 pm, Priya <chavanpr...@gmail.com> wrote:
> David,
> thank you for the reply.I know that the problem can easily be solved
> by using the actual column name mycol instead of the variable.But,
> I want know if there is a way to get the column name dynamically from
> a variable.
>
> On Jul 24, 2:54 pm, ddf <orat...@msn.com> wrote:
>
>
>
> > Comments embedded.
>
> > On Jul 24, 1:55 pm, Priya <chavanpr...@gmail.com> wrote:
>
> > > Hi,
>
> > > I am a oracle PL/SQL newbie.Can someone please help me solve the below
> > > problem?
>
> > > Consider a table called mytable with one column called mycol .I have
> > > written a on-update trigger.
>
> > > CREATE OR REPLACE TRIGGER "testtrigger" BEFORE
> > > UPDATE ON mytable FOR EACH ROW
> > > DECLARE
> > > Test VARCHAR(1000);
>
> > > BEGIN
>
> > > TEST:='mycol';
> > > IF :OLD.TEST <> :NEW.TEST THEN
> > > DBMS_OUTPUT.PUT_LINE ("Hi");
> > > END IF
>
> > > i get the error bad bind variable.This is obviously because the
> > > compiler is thinking that test is a column and not a variable.
>
> > Correct. That's not the only error in your code, though.
>
> > > How
> > > should I make the compiler aware that Test is a variable and the
> > > compiler must resolver it to mycol?
>
> > You cannot. This is a trigger, against a specific table, and the
> > blocks you're referencing (:OLD and :NEW) only apply to the columns
> > defined in that table, not to variables you may have a whim to create.
>
> > > I know i can replace the variable
> > > with mycol but that not what I want to do.
>
> > You're trigger won't compile and won't run, and updates to mytable
> > will fail:
>
> > SQL> --
> > SQL> -- Create test table
> > SQL> --
> > SQL> create table mytable(
> > 2 mycol varchar2(20)
> > 3 );
>
> > Table created.
>
> > SQL>
> > SQL> --
> > SQL> -- Create bad trigger
> > SQL> --
> > SQL> -- Won't work because TEST is not a column
> > SQL> -- in mytable
> > SQL> --
> > SQL> CREATE OR REPLACE TRIGGER "testtrigger" BEFORE
> > 2 UPDATE ON mytable FOR EACH ROW
> > 3 DECLARE
> > 4 Test VARCHAR(1000);
> > 5
> > 6
> > 7 BEGIN
> > 8
> > 9
> > 10 TEST:='mycol';
> > 11 IF :OLD.TEST <> :NEW.TEST THEN
> > 12 DBMS_OUTPUT.PUT_LINE ("Hi");
> > 13 END IF;
> > 14
> > 15 END;
> > 16 /
>
> > Warning: Trigger created with compilation errors.
>
> > SQL>
> > SQL> show errors
> > No errors.
> > SQL>
> > SQL> --
> > SQL> -- Insert data
> > SQL> --
> > SQL>
> > SQL> insert all
> > 2 into mytable
> > 3 values('Yarg')
> > 4 into mytable
> > 5 values('Yink')
> > 6 into mytable
> > 7 values('Yorg')
> > 8 select * From dual;
>
> > 3 rows created.
>
> > SQL>
> > SQL> commit;
>
> > Commit complete.
>
> > SQL>
> > SQL> --
> > SQL> -- Perform update which fails
> > SQL> --
> > SQL>
> > SQL> set serveroutput on size 1000000
> > SQL>
> > SQL> update mytable set mycol='Fred';
> > update mytable set mycol='Fred'
> > *
> > ERROR at line 1:
> > ORA-04098: trigger 'BING.testtrigger' is invalid and failed re-
> > validation
>
> > SQL>
> > SQL> --
> > SQL> -- Create trigger
> > SQL> --
> > SQL> CREATE OR REPLACE TRIGGER "testtrigger" BEFORE
> > 2 UPDATE ON mytable FOR EACH ROW
> > 3 BEGIN
> > 4
> > 5
> > 6 IF :OLD.mycol <> :NEW.mycol THEN
> > 7 DBMS_OUTPUT.PUT_LINE ('Hi');
> > 8 END IF;
> > 9
> > 10 END;
> > 11 /
>
> > Trigger created.
>
> > SQL>
> > SQL> show errors
> > No errors.
> > SQL>
> > SQL> --
> > SQL> -- Perform update
> > SQL> --
> > SQL>
> > SQL> update mytable set mycol='Fred';
> > Hi
> > Hi
> > Hi
>
> > 3 rows updated.
>
> > SQL>
>
> > Sometimes what you want to do and what you must do are two different
> > things.
>
> > > Thanks in advance!
>
> > > Priya
>
> > David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
It's clear you do not understand the OLD and NEW 'blocks' used by a
trigger. These 'blocks' reference the existing column values for the
row being processed (:OLD) and the pending column values (:NEW) for
that same row. As such these structures reference COLUMN names, not
variables, and there is no possible way to do what you want. As I
said in my prior post you're writing a trigger against a specific
table which has a specific definition and the trigger knows the column
names set by that definition. Please read here:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1222
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
