Re: Two questions in Oracle...

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 26 Aug 1999 15:42:25 +0200
Message-ID: <7q3g98$f1q$1_at_oceanite.cybercable.fr>


Jimmy a écrit dans le message <37C60AC9.5CB7A5B_at_comp.polyu.edu.hk>...
>Hello all,
> I have two questions: (in Oracle 7.3.3)
>
>1) Why the following SQL statement returns 'aaa'?
>
> select 'aaa' from dual where 'LOW_D_LIMIT' like 'L_%';
>
> Is '_' means special character? 'LOW_D_LIMIT' not contains the
>string like 'L_' and it should return 'no rows returned' I
>think......... but it returns 'aaa'....
>

_ is a special character for any character. 'L_%' means beginning with L and length >= 2.

>2) I want to develop a trigger like this:
>
>create or replace trigger testing
>after update or insert or delete on aaa
>begin
> if updating then
> if updating(fieldA) then
> pro1;
> end if;
> pro_all;
> elsif inserting then
> ...
> elsif deleting then
> ...
> end if;
>end;
>/
>
> If update column fieldA, then do pro1 and pro_all Other columns
>updating do only pro_all.
> However, errors in creating this trigger.... Is it possible to check
>which column is updated in the updating part? I cannot do this:
>
>create or replace trigger testing
>after update(fieldA) or insert or delete on aaa
>...
>
> since this trigger only fired if fieldA is updated.
>

There is just a little bug in your trigger: the name of the field must be in upper case and between quotes.

create or replace trigger testing
after update or insert or delete on aaa
begin

   if updating then

      if updating('FIELDA') then
         pro1;
      end if;
      pro_all;
   elsif inserting then
      ...
   elsif deleting then
      ...

   end if;
end;
/

If FieldA is the only one updated, you can also use:

create or replace trigger testing
after update of fielda or insert or delete on aaa begin

   if updating then

      pro1;
      pro_all;
   elsif inserting then
      ...
   elsif deleting then
      ...

   end if;
end;
/

Regards. Received on Thu Aug 26 1999 - 15:42:25 CEST

Original text of this message