Home » SQL & PL/SQL » SQL & PL/SQL » trigger
trigger [message #219564] Thu, 15 February 2007 00:28 Go to next message
sherya
Messages: 11
Registered: February 2007
Location: noida, sec-15
Junior Member
hello

can u tell me where i should declare "name".
my trigger is like this-

SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger half_1_tr
  2  after insert or update or delete on half_1
  3  referencing
  4  new as N
  5  old as O
  6  for each row
  7  when (length(N.name)>3)
  8  begin
  9  dbms_output.put_line('before change name= '||N.name);
 10  :N.name:=initcap(:N.name);
 11  dbms_output.put_line('after change name= '||N.name);
 12* end;
SQL> /

Warning: Trigger created with compilation errors.

SQL> sho er
SP2-0158: unknown SHOW option "er"
SQL> sho err
Errors for TRIGGER HALF_1_TR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PL/SQL: Statement ignored
2/46     PLS-00201: identifier 'N.NAME' must be declared
4/1      PL/SQL: Statement ignored
4/45     PLS-00201: identifier 'N.NAME' must be declared


waiting for ur response

regards
Re: trigger [message #219568 is a reply to message #219564] Thu, 15 February 2007 00:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Prefix it with a colon ":N.Name" in lines 7, 9 and 11 as you have done in line 10.

Ross Leishman
Re: trigger [message #219572 is a reply to message #219568] Thu, 15 February 2007 00:41 Go to previous messageGo to next message
sherya
Messages: 11
Registered: February 2007
Location: noida, sec-15
Junior Member
thanxx Ross Leishman

it's working now...
but i unable to predict why "after" not working in the place of "before". can u please elaborate it.
i will show u wat error getting.

SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger tr_half_1
  2  before insert or update or delete on half_1
  3  referencing
  4  new as N
  5  old as O
  6  for each row
  7  when (length(N.name)>3)
  8  begin
  9  dbms_output.put_line('before change name= '||:N.name);
 10  :N.name:=initcap(:N.name);
 11  dbms_output.put_line('after change name= '||:N.name);
 12* end;
SQL> /

Trigger created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger tr_half_1
  2  after insert or update or delete on half_1
  3  referencing
  4  new as N
  5  old as O
  6  for each row
  7  when (length(N.name)>3)
  8  begin
  9  dbms_output.put_line('before change name= '||:N.name);
 10  :N.name:=initcap(:N.name);
 11  dbms_output.put_line('after change name= '||:N.name);
 12* end;
SQL> /
create or replace trigger tr_half_1
                          *
ERROR at line 1:
ORA-04084: cannot change NEW values for this trigger type


thanxx in advance

regards
Re: trigger [message #219581 is a reply to message #219572] Thu, 15 February 2007 00:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How would you change the value to be inserted/updated AFTER the insert/update?
Re: trigger [message #219582 is a reply to message #219564] Thu, 15 February 2007 01:06 Go to previous message
sherya
Messages: 11
Registered: February 2007
Location: noida, sec-15
Junior Member
oh my god...

i am realy sorry for it.

u r rite Mr.frank.
Previous Topic: Explain plan
Next Topic: Migration from MSSQL SERVER 2000 to ORACLE 10G
Goto Forum:
  


Current Time: Mon Dec 05 10:37:39 CST 2016

Total time taken to generate the page: 0.12326 seconds