I have never worked directly with "instead of" triggers, but their intent is
to be used on a non-updateable view. For instance:
SQL> create view all_emps as select * from emps UNION select * from
archived_emps;
SQL> insert into all_emps (...);
The RDBMS then says "Which real table does this record need to go into? I'm
confused! ERROR! ERROR!"
SQL> create trigger emp_ins instead of insert on all_emps for each row
2 begin
3 -- noone inserts straight to archive, so we know they need to go in
emps
4 insert into emps values (new.a, ...);
5 end;
6 /
SQL> insert into all_emps (...);
Yeah! It works now!!!
I know that these were intended to be used on what would otherwise be a
non-updateable views. I do not think they can be created on other objects,
but I could be wrong - call Ripley :)
Brian Norrell
Senior Software Engineer
QuadraMed
972-831-6600
-----Original Message-----
Sent: Tuesday, February 13, 2001 12:51 PM
To: Multiple recipients of list ORACLE-L
What is the purpose of the non-updateable view?
-FS
>From: "Norrell, Brian" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: HELP - Insert/Update trigger
>Date: Tue, 13 Feb 2001 09:56:15 -0800
>
>Not with a normal table trigger. Try creating a non-updateable view with
>an
>"instead of" trigger for the insert.
>
>Brian Norrell
>Senior Software Engineer
>QuadraMed
>972-831-6600
>
>
>-----Original Message-----
>Sent: Tuesday, February 13, 2001 9:26 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Listers,
>
> I am attempting to write a trigger on a table that will have batch
>inserts nightly. I want each insert to check to see if a record with that
>primary key exists, and if it does, update the record. If the primary key
>does not yet exist in the table, insert the record. The batch load each
>night is a large series of inserts, no updates. I want the trigger to
>figure out it if needs to update or insert.
>
>Can this be done??
>
>Thank you,
> -Fred Smith
>_________________________________________________________________
>Get your FREE download of MSN Explorer at http://explorer.msn.com
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Fred Smith
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Norrell, Brian
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fred Smith
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Norrell, Brian
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
