| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: HELP - Insert/Update trigger
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" <BNorrell_at_QuadraMed.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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: fred_fred_1_at_hotmail.com
>
>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: ListGuru_at_fatcity.com (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: BNorrell_at_QuadraMed.com
>
>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: ListGuru_at_fatcity.com (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: Fred Smith INET: fred_fred_1_at_hotmail.com 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: ListGuru_at_fatcity.com (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: BNorrell_at_QuadraMed.com 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: ListGuru_at_fatcity.com (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).Received on Tue Feb 13 2001 - 13:42:32 CST
 |
 |