Comments embedded.
On Jul 27, 9:31 am, sonty <saurabh.zen...@gmail.com> wrote:
> Hi,
>
> Correct me if I am wrong, anybody can do so :-).
>
> What you are saying is that "Getting the column name dynamically when
> the columns are static" because all of us knows that update statements
> cannot change the column name anyways.
I don't know what you mean by that, but a basic reading indicates an
answer of 'no'. The OP is attempting to use
:OLD.<variable_name>
and
:NEW.<variable_name>
to reference values in the :OLD and :NEW 'blocks' populated during an
insert/update/delete where <variable_name> stores the desired column
name. Oracle will not process that type of construct in a trigger; it
needs the actual column name:
:OLD.mycol
and
:NEW.mycol
(in this example). The example I provided proves this.
>
> Column name can be updated inside alter table statements.
Yes. What that has to do with the issue presented by the OP is a
mystery to me.
>
> So, Please let us know the exact requirement to get the solution you
> want.
>
It's clear, really: he wants to use some variable to identify the
desired column name in a trigger and have the :OLD and :NEW
constructs recognize that as valid. He has already seen such code
fails, yet continues to try to make it work..
> -Sonty
>
> On Jul 25, 4:32 am, ddf <orat...@msn.com> wrote:
>
>
>
> > 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_...
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
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
-~----------~----~----~----~------~----~------~--~---
