Re: how to get the old value in a oracle event alert ?

<rmoleveld_at_gmail.com> wrote in message
news:1132653584.869321.181470_at_f14g2000cwb.googlegroups.com...
> Hi all,
>
> I want to create an alert that tells me the changes on a specific field
> of a table when done.
> Therefor I also need the old and the new value from that specific
> object.
> Example:
>
> mtl_material_items contains my items.
> when the item_type changes from AA to BB I want to receive an email.
>
> This can be done with a event alert on table mtl_material_items but
> just checking and/or comparing the old and new value from item_type is
> not known by me.
>
> #1. is this possible
> #2. ifso, how an I achieve this ?
>
> Many thanks on forehand.
>
> Ronald.
>

You can do this in an after update trigger on that table and column. I would have the trigger call a procedure that used dbms_job to schedule sending you the email immediately. Why dbms_job? By using dbms_job the job will only fire once a commit of the data is done. If a rollback occurs (and thus the change does not occur) then you will not be notified.

asktom.oracle.com is your friend. You can do a search there for examples. Jim Received on Tue Nov 22 2005 - 08:45:11 CST