Re: Can this be done with a trigger?

On Aug 5, 12:16 pm, Aya the Vampire Slayer
<ry...@xxxxxxxxxxxxxxxxxxxxxxxx> wrote:
artme...@xxxxxxxxx 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...)

--
"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


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....


.

Relevant Pages

  • Re: Can this be done with a trigger?
    ... trigger fire, then go back and update the row..... ... Create a View on Table1 selecting * from Table1 and also adding a null ... Change your PL/SQL procedure to insert into the view instead of directly ... out for the SOURCE column as part of the INSERT parameters (since you ...
    (comp.databases.oracle.misc)
  • Re: Can this be done with a trigger?
    ... trigger fire, then go back and update the row..... ... Create a View on Table1 selecting * from Table1 and also adding a null ... Change your PL/SQL procedure to insert into the view instead of directly ... out for the SOURCE column as part of the INSERT parameters (since you ...
    (comp.databases.oracle.misc)
  • Re: Can this be done with a trigger?
    ... trigger fire, then go back and update the row..... ... Create a View on Table1 selecting * from Table1 and also adding a null ... Change your PL/SQL procedure to insert into the view instead of directly ... out for the SOURCE column as part of the INSERT parameters (since you ...
    (comp.databases.oracle.misc)
  • Re: Deleting a record from a database
    ... ALTER TABLE Table1 ... Create a trigger that archives the "deleted file" To do this, ... a table exactly like the original table, but you call it _History. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Key Fields
    ... Just watch out of there's a foreign key that references the table. ... A traditional trigger will ... >> Table1 has an IP Address field ... >> All records in Table2 that have the foreign key set to the original IP ...
    (microsoft.public.sqlserver.programming)