Oracle trigger keywoard :new not found

Question

my Oracle SqlDeveloper (or Oracle Database?) doesn't know the :NEW keyword.

For instance, if I enter the following sample from Oracles website, when I execute the "create or replace trigger" paragraph, a window "Enter bind variable" pops up and asks for the bind variable ":new".

Shouldn't this ":new" variable be predefined?

(Oracle SQL Developer 4.0.1.14, Oracle DB 11gR2, Windows)

drop table tab1;

create table tab1 (c1 clob);
insert into tab1 values ('testtext');

create or replace trigger trg1
  before update on tab1
  for each row
begin
  dbms_output.put_line('Old value of CLOB column: '||:OLD.c1);
  dbms_output.put_line('Proposed new value of CLOB column: '||:NEW.c1);

-- Previously, we couldn't change the new value for a LOB.
-- Now, we can replace it, or construct a new value using SUBSTR, INSTR...
-- operations for a CLOB, or DBMS_LOB calls for a BLOB.
  :NEW.c1 := :NEW.c1 || to_clob('<hr><p>Standard footer paragraph.');

  dbms_output.put_line('Final value of CLOB column: '||:NEW.c1);
end;
/ 

set serveroutput on;
update tab1 set c1 = '<h1>Different Document Fragment</h1><p>Different text.';

select * from tab1;

try `set define off;` and run it as a script. http://stackoverflow.com/questions/12166046/creating-a-trigger-in-oracle-express — GriffeyDog, Jun 05 '14 at 16:17

score 2 · Answer 1 · answered Jun 06 '14 at 06:44

2

It turned out I just had to press "Apply" in the Bind dialog, and the trigger was created. Obviously a bug in SqlDeveloper. At least there is a workaround...

answered Jun 06 '14 at 06:44

user3711661

41
1
3

score 0 · Answer 2 · answered Jun 05 '14 at 16:28

I added the 'REFERENCING' statement, and tested the code, seems to work just fine--- BTW, you can just turn DBMS output on in SQL dev w/o having to run the command..

CREATE OR REPLACE TRIGGER trg1
   BEFORE UPDATE
   ON tab1
   REFERENCING NEW AS new OLD AS old
   FOR EACH ROW
BEGIN
   DBMS_OUTPUT.put_line ('Old value of CLOB column: ' || :old.c1);
   DBMS_OUTPUT.put_line ('Proposed new value of CLOB column: ' || :new.c1);

   -- Previously, we couldn't change the new value for a LOB.
   -- Now, we can replace it, or construct a new value using SUBSTR, INSTR...
   -- operations for a CLOB, or DBMS_LOB calls for a BLOB.
   :new.c1 := :new.c1 || TO_CLOB ('<hr><p>Standard footer paragraph.');

   DBMS_OUTPUT.put_line ('Final value of CLOB column: ' || :new.c1);
END;

Oracle trigger keywoard :new not found

2 Answers2