Re: Help Me (Oracel Trigger Problem)

A copy of this was sent to "Kang, Dale" <cs20-15_at_kccedu.co.kr> (if that email address didn't require changing) On Sat, 12 Dec 1998 14:07:28 +0900, you 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;
>/
>
>

It works -- you are just trying to do something that doesn't work.

If you update the row with 'PRESIDENT' in it -- the trigger does not fire. It works in that case.

If you update a non-president row, the trigger fires, the INSERT occurrs but then you RAISE_APPLICATION_ERROR. This UNDOES the update on emp AS WELL AS the insert into guard.

Once you raised the error, the entire update of EMP and any work performed by any triggers, declaritive RI, etc is UNDONE as well.

(btw: Oracle8i adds a feature called an autonomous transaction -- a separate transaction within a transaction. Oracle8i would allow you to program what you want by spawning a new transaction in the trigger -- doing the audit and committing it).

So, if you want to AUDIT attempts to modify salaries, you cannot raise an application error. Perhaps what you want to do in the meanwhile might be:

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);

   :new.sal := :old.sal;

END;
/

What that does is let you audit attempts to modify the sal but never lets the sal column get modified (it doesn't notify the person attempting the update either but they are doing something 'bad' anyway so maybe it doesn't matter)  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.