create or replace trigger trigger_name
before update of column3 on table1
for each row
when (old.column3 != new.column3)
declare
pragma autonomous_transaction; -- Oracle8i-specific
begin
insert into table2 values(:new.column3 /* and whatever else you want to insert */ );
commit; -- this will commit ONLY the insert due to autonomous transaction pragma
- while original transaction activated the trigger may be rolled back, giving
- you some kind of audit on column3 updates, either failed or successful.
- if you don't want such behavior, remove pragma and commit and
- change before update to after update so that trigger will only activate if
- update itself worked and didn't violate any constraints. insert will be
- committed or rolled back along with updating transaction in this case.
end;
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Manoj Nair" <debo_nair_at_yahoo.com> wrote in message
news:92a0e580.0109071414.456626d4_at_posting.google.com...
> How do i handle this situation?
>
> I have 2 tables...First table has 3 columns......I can update column 2
> and column 3. When I change values in column 3 ( by UPDATE ) and if
> this new value is different from old value in column 3 then I need to
> insert a record in second table...how should i go about this?
>
> Thanks
Received on Sat Sep 08 2001 - 11:32:35 CDT
