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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
