Re: Can this be done with a trigger?

From: Aya the Vampire Slayer <>
Date: Tue, 5 Aug 2008 20:26:48 +0000 (UTC)
Message-ID: <g7ad28$583$> wa:
>On Aug 5, 3:07 pm, Aya the Vampire Slayer
><> wrote:
>> wa:
>> >On Aug 5, 12:16 pm, Aya the Vampire Slayer
>> ><> wrote:
>> >> wa:
>> >> <snip>
>> >> >The SOURCE is not needed in this table. But, this would fire the
>> >> >trigger where SOURCE is needed. The SOURCE will be derived within the
>> >> >PL/SQL code. I do not think this is possible. I may have to let the
>> >> >trigger fire, then go back and update the row.....
>> >> Create a View on Table1 selecting * from Table1 and also adding a null
>> >> column called SOURCE at the end.
>> >> create or replace view vw_tbl1 as
>> >> select t.*,
>> >> null SOURCE
>> >> from table1 t
>> >> ;
>> >> Change your PL/SQL procedure to insert into the view instead of directly
>> >> into the table, passing whatever it is that the PL/SQL is able to figure
>> >> out for the SOURCE column as part of the INSERT parameters (since you
>> >> are now inserting into the view where SOURCE exists as a column).
>> >> Then, create your trigger on the view and have the trigger insert all of
>> >> the information into Table2, including the SOURCE column, after
>> >> inserting into table1.
>> >> If you are worried about people inserting directly into the table, then
>> >> simply deny access to the table and only allow access to the view. (here
>> >> is me hoping you aren't letting users log in directly to the schema that
>> >> contains all of these objects...)
>> >I must say, I do like your approach, that is, if if it did not yield
>> >this result:
>> >Tue Aug 5 14:41:58 2008
>> >Errors in file /u01/NI00/admin/udump/ni00_ora_13221.trc:
>> >ORA-07445: exception encountered: core dump [_intel_fast_memcpy.J()
>> >+30] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
>> >More investigation is needed. Hope I do not have to log an SR with
>> >Oracle....
>> I'm not sure if it would make a difference for what your error is, but I
>> guess I should also note that I almost always use INSTEAD OF triggers
>> with views (and not AFTER triggers). For INSTEAD OF, you'll obviously
>> have to write both table inserts (for table1 and table2) into the
>> trigger explicitly.

>Ok, Well, maybe I'm a dingbat here, but help me out:


>That creates a view identical to TABLE1 with an extra column named

>Now, in my INSTEAD OF trigger I can insert into any table I code for,
>right? So, within the body of the trigger I can say: INSERT INTO
>TABLEB VALUES (.........).

>That should work, right? I'm just confused on where the originating
>DML operation goes. Is the purpose of the trigger to not insert into
>the base tables, but just execute the code?

For an INSTEAD OF trigger, you will have to write both the "INSERT INTO TABLE1 [...]" and "INSERT INTO TABLE2 [...]" DML statements into the body of the trigger.

"INSTEAD OF", as the name implies, does whatever is in the body of the trigger instead of doing the actual DML action that triggered it. So, for an INSTEAD OF INSERT trigger, if you want it to actually do the INSERT that triggered it in the first place, you will have to write it into the body of the trigger explicitly. (for the record, INSTEAD OF triggers can only be used with views)

"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator
Received on Tue Aug 05 2008 - 15:26:48 CDT

Original text of this message