Home -> Usenet -> c.d.o.tools -> Re: Help Me (Oracel Trigger Problem)

Re: Help Me (Oracel Trigger Problem)

From: Frank van Bortel <f.van.bortel_at_vnl.nl>
Date: Tue, 29 Dec 1998 09:13:18 +0100
Message-ID: <36888F1D.822582DE_at_vnl.nl> 






[Quoted] [Quoted] Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit



Is this what you're looking for?



CREATE OR REPLACE TRIGGER get_hacker


   BEFORE UPDATE OF sal ON emp


   FOR EACH ROW



BEGIN



   IF (old.job <> 'PRESIDENT')


   THEN


     INSERT INTO guard (
                 hacker
                , mod_date
                , old_sal
                , new_sal )
     VALUES (
                 user
               , to_char(sysdate,'yy/mm/dd hh24:mi:ss')
               , :old.sal
               , :new.sal )
             ;

   RAISE_APPLICATION_ERROR(-20500,
      'You may olny update Salary of PRESIDENT. !!');




--



    
  
  •   ELSE

  
  •   Do something you would do when updating
  
  •   salary of job president

    
--

      END IF;

END;

/






Assuming you want to record any changes on sal of table emp, when the job is
not president...


Watch out with the error message: there's no stopping a clever hacker
from changing his/her job to president, up his/her salary, and change it back
again!



Cheers,  Frank


Alex J wrote:


> Seems to me that this trigger will generate
> "RAISE_APPLICATION_ERROR(-20500" every time unless you test the insert
> via SQL%RowCount or something!
>
> James A. Johnson wrote in message <3686DC72.9D5287A5_at_bellsouth.net>...
> >You need to provide a little more on this problem.  Does it update the
> >president or just the people not the president.  Also see if the trigger
> >is compiled and accurate using enterprise manager.
> >
> >
> >"Kang, Dale" wrote:
> >
> >> Why this routine don't work ?
> >> Please help me!
> >> I really appreciate your reading.
> >>
> >> CREATE OR REPLACE TRIGGER get_hacker
> >>    BEFORE UPDATE OF sal ON emp
> >>    FOR EACH ROW
> >>    WHEN (old.job != 'PRESIDENT')
> >> BEGIN
> >>    INSERT INTO guard(hacker, mod_date, old_sal, new_sal)
> >>       VALUES(user,
> >>          to_char(sysdate,'yy/mm/dd hh24:mi:ss'),
> >>          :old.sal, :new.sal);
> >>
> >>    RAISE_APPLICATION_ERROR(-20500,
> >>       'You may olny update Salary of PRESIDENT. !!');
> >> END;
> >> /
> >



--------------3CBE9E31A2FCDEF561153728






Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit



<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>


Is this what you're looking for?

<p><tt>CREATE OR REPLACE TRIGGER get_hacker</tt>
<br><tt>&nbsp;&nbsp; BEFORE UPDATE OF sal ON emp</tt>
<br><tt>&nbsp;&nbsp; FOR EACH ROW</tt>
<br><tt>BEGIN</tt>
<br><tt>&nbsp;&nbsp; IF (old.job &lt;> 'PRESIDENT')</tt>
<br><tt>&nbsp;&nbsp; THEN</tt>
<br><tt>&nbsp;&nbsp;&nbsp;&nbsp; INSERT INTO guard (</tt>
<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;


hacker</tt>


<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
, mod_date</tt>


<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
, old_sal</tt>


<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
, new_sal )</tt>

<br><tt>&nbsp;&nbsp;&nbsp;&nbsp; VALUES (</tt>
<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
user</tt>
<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;


, to_char(sysdate,'yy/mm/dd hh24:mi:ss')</tt>
<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
, :old.sal</tt>


<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
, :new.sal )</tt>


<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
;</tt><tt></tt>


<p><tt>&nbsp;&nbsp; RAISE_APPLICATION_ERROR(-20500,</tt>
<br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'You may olny update Salary of PRESIDENT.
!!');</tt>

<br><tt>--</tt>
<br><tt>-- ELSE</tt>
<br><tt>-- Do something you would do when updating</tt>
<br><tt>-- salary of job president</tt>
<br><tt>--</tt>
<br><tt>&nbsp; END IF;</tt>
<br><tt>END;</tt>
<br><tt>/</tt>
<p>Assuming you want to record any changes on sal of table emp, when the


job is not president...


<br>Watch out with the error message: there's no stopping a clever hacker
<br>from changing his/her job to president, up his/her salary, and change
it back again!

<p>Cheers,&nbsp; Frank
<br>Alex J wrote:
<blockquote TYPE=CITE>Seems to me that this trigger will generate
<br>"RAISE_APPLICATION_ERROR(-20500" every time unless you test the insert
<br>via SQL%RowCount or something!
<p>James A. Johnson wrote in message &lt;3686DC72.9D5287A5_at_bellsouth.net>...
<br>>You need to provide a little more on this problem.&nbsp; Does it update


the


<br>>president or just the people not the president.&nbsp; Also see if
the trigger

<br>>is compiled and accurate using enterprise manager.
<br>>
<br>>
<br>>"Kang, Dale" wrote:
<br>>
<br>>> Why this routine don't work ?
<br>>> Please help me!
<br>>> I really appreciate your reading.
<br>>>
<br>>> CREATE OR REPLACE TRIGGER get_hacker
<br>>>&nbsp;&nbsp;&nbsp; BEFORE UPDATE OF sal ON emp
<br>>>&nbsp;&nbsp;&nbsp; FOR EACH ROW
<br>>>&nbsp;&nbsp;&nbsp; WHEN (old.job != 'PRESIDENT')
<br>>> BEGIN
<br>>>&nbsp;&nbsp;&nbsp; INSERT INTO guard(hacker, mod_date, old_sal, new_sal)
<br>>>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES(user,
<br>>>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; to_char(sysdate,'yy/mm/dd


hh24:mi:ss'),


<br>>>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :old.sal,
:new.sal);

<br>>>
<br>>>&nbsp;&nbsp;&nbsp; RAISE_APPLICATION_ERROR(-20500,
<br>>>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'You may olny update Salary


of PRESIDENT. !!');

<br>>> END;
<br>>> /
<br>></blockquote>
</html>





--------------3CBE9E31A2FCDEF561153728--



Received on Tue Dec 29 1998 - 09:13:18 CET












Original text of this message