Comments embedded.
On Jul 24, 1:55 pm, Priya <[email protected]> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
