Trigger question
Hallo, I would like to write the code to atrigger that makes insert into another table inly if there is the condition PREFERED_NUMBER = 1 and TELEPHONE_TYPE_ID in (1,2, 5) How can write this in a simple way? Thanks in advance. Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Trigger question
On 01/30/2004 09:29:26 AM, [EMAIL PROTECTED] wrote: Hallo, I would like to write the code to atrigger that makes insert into another table inly if there is the condition Oracle9i Application Developer's Guide - Fundamentals, Ch. 15. - Using Triggers. There are examples there, too. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Trigger question
Hallo everyone, I have the table system_change and I I have the insert statement here below be run when there is an update or insert of a value in any ofthe fields ORGANIZATION_NUMBER LEGAL_NAME COMPANY_FORM_ID ORDER_STOP in table COMPANY insert into system_change values(system_change_id.nextval, 1, null,null, null, null, null, 1, SYSDATE, 1, SYSDATE,1 ) How can I write the code in the easiest way? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Trigger question
Enable auditing on COMPANY? Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === Hallo everyone, I have the table system_change and I I have the insert statement here below be run when there is an update or insert of a value in any ofthe fields ORGANIZATION_NUMBER LEGAL_NAME COMPANY_FORM_ID ORDER_STOP in table COMPANY insert into system_change values(system_change_id.nextval, 1, null,null, null, null, null, 1, SYSDATE, 1, SYSDATE,1 ) How can I write the code in the easiest way? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Carel-Jan Engel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Trigger question
Hallo, I have the table system_change and I I have the insert statement here below be run when there is an update or insert of any of the fields ORGANIZATION_NUMBER LEGAL_NAME COMPANY_FORM_ID ORDER_STOP in table COMPANY The script bo be run is this one: insert into system_change values(system_change_id.nextval, 1, null,null, null, null, null, 1, SYSDATE, 1, SYSDATE,1 ) How can I write the code in the easiest way? All help is appreciated! Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Trigger question
The easiest way to write code for me is to use a text editor and sqlplus, always works for me. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, January 22, 2004 9:24 AM To: Multiple recipients of list ORACLE-L Hallo, I have the table system_change and I I have the insert statement here below be run when there is an update or insert of any of the fields ORGANIZATION_NUMBER LEGAL_NAME COMPANY_FORM_ID ORDER_STOP in table COMPANY The script bo be run is this one: insert into system_change values(system_change_id.nextval, 1, null,null, null, null, null, 1, SYSDATE, 1, SYSDATE,1 ) How can I write the code in the easiest way? All help is appreciated! Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Trigger Question - Thanks for all the help
Hi All, Thanks for all the tips and links to web sites. Really appreciated. I did have a mutating trigger - but I also had it calling a pre-existing procedure that had a commit in it (which from what I read today is not allowed) In the end I put the code in an existing procedure - it took only about 5 lines or so which was similar to implement than the mutating trigger solution. Regards, N. :--Original Message- :-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] :-Behalf Of :-GovindanK :-Sent: 08 January 2004 21:20 :-To: Multiple recipients of list ORACLE-L :-Subject: Re: Trigger Question :- :- :-Ok. Here you go. :- :-http://osi.oracle.com/~tkyte/Mutate/index.html :- :-Let me know if you find this useful. :- :-HTH :- :-GovindanK :-OCP 8,8i :-Brainbench Certified Master DBA(8) :- :- :-On Thu, 08 Jan 2004 11:24:25 -0800, Nuala Cullen :-[EMAIL PROTECTED] :-said: :- :- Hi All, :- :- Firstly my apologies if this seems like a very *stupid* :-question but I'm :- a :- tad confused (and it's late in the evening) :- :- When an AFTER INSERT trigger is fired (row level) has the row been :- committed :- to the database at this stage? :- :- If so is it ok to call a package in the trigger that :-selects that row and :- changes some values in the row? :- :- Thanks, :- :- N. :- :- :--- :-http://www.fastmail.fm - Or how I learned to stop worrying and :- love email again :--- :-Please see the official ORACLE-L FAQ: http://www.orafaq.net :--- :-Author: GovindanK :- INET: [EMAIL PROTECTED] :- :-Fat City Network Services-- 858-538-5051 http://www.fatcity.com :-San Diego, California-- Mailing list and web hosting services :-- :-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.net -- Author: Nuala Cullen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Trigger Question
I'm sure I will be corrected if I'm wrong. Answers inline At 12:24 PM 1/8/2004, you wrote: Hi All, Firstly my apologies if this seems like a very *stupid* question but I'm a tad confused (and it's late in the evening) When an AFTER INSERT trigger is fired (row level) has the row been committed to the database at this stage? No. You could raise an error as part of what the trigger does in order to reject the action. If so is it ok to call a package in the trigger that selects that row and changes some values in the row? a) it is not so and b) you can not do anything with that row (or that table for that matter) in either the trigger or any called package or procedure. You'll get a mutating table error. Thanks, N. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Trigger Question
See in-line replies. Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is. --Unknown -Original Message- Nuala Cullen Sent: Thursday, January 08, 2004 2:24 PM To: Multiple recipients of list ORACLE-L Hi All, Firstly my apologies if this seems like a very *stupid* question but I'm a tad confused (and it's late in the evening) When an AFTER INSERT trigger is fired (row level) has the row been committed to the database at this stage? M.Bobak No, this action happens after the insert but before the completion of the INSERT statement. In other words, the trigger actions are atomic to the statement that caused them to be executed. If the INSERT is committed, the trigger changes are also committed. If the INSERT is rolled back, so are the trigger modifications. If so is it ok to call a package in the trigger that selects that row and changes some values in the row? M.Bobak No, this would likely cause mutation when doing multi-row inserts. If this is necessary, you may want to re-consider your app design. If that's out of the question, check MetaLink for the standard mutation solution. M.BobakHope that helps! M.BobakPS Autonomous transactions are different, and an exception to the above. See the documentation for more details Thanks, N. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Trigger Question
Row has not been commited yet. The AFTER INSER trigger fires after the row has been inserted. Rollback can still undo all the changes performed by both insert and trigger. And no, trigger cannot select from the table it is defined on, not even through a procedure. On 01/08/2004 02:24:25 PM, Nuala Cullen wrote: Hi All, Firstly my apologies if this seems like a very *stupid* question but I'm a tad confused (and it's late in the evening) When an AFTER INSERT trigger is fired (row level) has the row been committed to the database at this stage? If so is it ok to call a package in the trigger that selects that row and changes some values in the row? Thanks, N. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Trigger Question
Hi All, Firstly my apologies if this seems like a very *stupid* question but I'm a tad confused (and it's late in the evening) When an AFTER INSERT trigger is fired (row level) has the row been committed to the database at this stage? If so is it ok to call a package in the trigger that selects that row and changes some values in the row? Thanks, N. winmail.dat
RE: Trigger Question
Wolfgang, Yes you may, within the trigger only, change values of that row only. it's known as :new.column_name := whatever; Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, January 08, 2004 2:39 PM To: Multiple recipients of list ORACLE-L I'm sure I will be corrected if I'm wrong. Answers inline At 12:24 PM 1/8/2004, you wrote: Hi All, Firstly my apologies if this seems like a very *stupid* question but I'm a tad confused (and it's late in the evening) When an AFTER INSERT trigger is fired (row level) has the row been committed to the database at this stage? No. You could raise an error as part of what the trigger does in order to reject the action. If so is it ok to call a package in the trigger that selects that row and changes some values in the row? a) it is not so and b) you can not do anything with that row (or that table for that matter) in either the trigger or any called package or procedure. You'll get a mutating table error. Thanks, N. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Trigger Question
You can do some funky stuff with Autonomous Transactions (trigger independent from initiating transaction), but I need lots more coffee before even attempting more detail on this. Henry -Original Message- Mladen Gogala Sent: Thursday, January 08, 2004 2:45 PM To: Multiple recipients of list ORACLE-L Row has not been commited yet. The AFTER INSER trigger fires after the row has been inserted. Rollback can still undo all the changes performed by both insert and trigger. And no, trigger cannot select from the table it is defined on, not even through a procedure. On 01/08/2004 02:24:25 PM, Nuala Cullen wrote: Hi All, Firstly my apologies if this seems like a very *stupid* question but I'm a tad confused (and it's late in the evening) When an AFTER INSERT trigger is fired (row level) has the row been committed to the database at this stage? If so is it ok to call a package in the trigger that selects that row and changes some values in the row? Thanks, N. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Poras, Henry R. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Trigger Question
Of course. Silly me. At 12:54 PM 1/8/2004, you wrote: Wolfgang, Yes you may, within the trigger only, change values of that row only. it's known as :new.column_name := whatever; If so is it ok to call a package in the trigger that selects that row and changes some values in the row? a) it is not so and b) you can not do anything with that row (or that table for that matter) in either the trigger or any called package or procedure. You'll get a mutating table error. -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Trigger Question
Ok. Here you go. http://osi.oracle.com/~tkyte/Mutate/index.html Let me know if you find this useful. HTH GovindanK OCP 8,8i Brainbench Certified Master DBA(8) On Thu, 08 Jan 2004 11:24:25 -0800, Nuala Cullen [EMAIL PROTECTED] said: Hi All, Firstly my apologies if this seems like a very *stupid* question but I'm a tad confused (and it's late in the evening) When an AFTER INSERT trigger is fired (row level) has the row been committed to the database at this stage? If so is it ok to call a package in the trigger that selects that row and changes some values in the row? Thanks, N. -- http://www.fastmail.fm - Or how I learned to stop worrying and love email again -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: GovindanK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Trigger Question
When an AFTER INSERT trigger is fired (row level) has the row been committed to the database at this stage? No If so is it ok to call a package in the trigger that selects that row and changes some values in the row? No See the following snippet from the fine SQL manual. Jared PS. Your email had a virus attached to it. AFTER Specify AFTER to cause Oracle to fire the trigger after executing the triggering event. For row triggers, the trigger is fired after each affected row is changed. Restrictions on AFTER Triggers * You cannot specify an AFTER trigger on a view or an object view. * You cannot write either the :OLD or the :NEW value. On Thu, 2004-01-08 at 11:24, Nuala Cullen wrote: Hi All, Firstly my apologies if this seems like a very *stupid* question but I'm a tad confused (and it's late in the evening) When an AFTER INSERT trigger is fired (row level) has the row been committed to the database at this stage? If so is it ok to call a package in the trigger that selects that row and changes some values in the row? Thanks, N. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Trigger Question
Dick, you cannot do that in an AFTER trigger. Jared On Thu, 2004-01-08 at 11:54, Goulet, Dick wrote: Wolfgang, Yes you may, within the trigger only, change values of that row only. it's known as :new.column_name := whatever; Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, January 08, 2004 2:39 PM To: Multiple recipients of list ORACLE-L I'm sure I will be corrected if I'm wrong. Answers inline At 12:24 PM 1/8/2004, you wrote: Hi All, Firstly my apologies if this seems like a very *stupid* question but I'm a tad confused (and it's late in the evening) When an AFTER INSERT trigger is fired (row level) has the row been committed to the database at this stage? No. You could raise an error as part of what the trigger does in order to reject the action. If so is it ok to call a package in the trigger that selects that row and changes some values in the row? a) it is not so and b) you can not do anything with that row (or that table for that matter) in either the trigger or any called package or procedure. You'll get a mutating table error. Thanks, N. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Trigger question
Hallo, What is wrong with this trigger: I want the trigger to fire(to run the statement : UPPER(SUBSTR(:OLD.namn, 1, 1)) || SUBSTR(:OLD.namn,2); after new record is inserted in this table. The thing to happen should be this: I want that script to be run on the same record that has been inserted in the table. So the UPPer command should run on the namn field that has been inserted in the table. Hope anyone can´help me. CREATE OR REPLACE TRIGGER AFTER_INSERT_ROWins_ON_test after insert ON test REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN :NEW.namn :=UPPER(SUBSTR(:OLD.namn, 1, 1)) || SUBSTR(:OLD.namn,2); -- :NEW.namn := UPPER(SUBSTR(:OLD.namn, 1, 1) || SUBSTR(:OLD.namn), 2); EXCEPTION WHEN OTHERS THEN raise_application_error(-2, 'ERROR IN TRIGGER AFTER_INSERT_ROWins_ON_test: ' || SQLERRM); END; / Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: Trigger question
As the this is a NEW record there is no OLD value! (and would it be bette to use a BEFORE insert?) Regards torben [EMAIL PROTECTED] writes: Hallo, What is wrong with this trigger: I want the trigger to fire(to run the statement : UPPER(SUBSTR(:OLD.namn, 1, 1)) || SUBSTR(:OLD.namn,2); after new record is inserted in this table. The thing to happen should be this: I want that script to be run on the same record that has been inserted in the table. So the UPPer command should run on the namn field that has been inserted in the table. Hope anyone can´help me. CREATE OR REPLACE TRIGGER AFTER_INSERT_ROWins_ON_test after insert ON test REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN :NEW.namn :=UPPER(SUBSTR(:OLD.namn, 1, 1)) || SUBSTR(:OLD.namn,2); -- :NEW.namn := UPPER(SUBSTR(:OLD.namn, 1, 1) || SUBSTR(:OLD.namn), 2); EXCEPTION WHEN OTHERS THEN raise_application_error(-2, 'ERROR IN TRIGGER AFTER_INSERT_ROWins_ON_test: ' || SQLERRM); END; / Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). Torben Holm Miracle A/S mobil: +45 2527 7104 mail : [EMAIL PROTECTED] http://www.miracleas.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: Trigger question
Roland, First thing would be that as it is an on insert trigger the OLD reference is not valid (only valid for update and delete I think) though I'm surprised that it accepts the referencing old as old part. Iain Nicoll -Original Message- Sent: Wednesday, April 03, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Hallo, What is wrong with this trigger: I want the trigger to fire(to run the statement : UPPER(SUBSTR(:OLD.namn, 1, 1)) || SUBSTR(:OLD.namn,2); after new record is inserted in this table. The thing to happen should be this: I want that script to be run on the same record that has been inserted in the table. So the UPPer command should run on the namn field that has been inserted in the table. Hope anyone can´help me. CREATE OR REPLACE TRIGGER AFTER_INSERT_ROWins_ON_test after insert ON test REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN :NEW.namn :=UPPER(SUBSTR(:OLD.namn, 1, 1)) || SUBSTR(:OLD.namn,2); -- :NEW.namn := UPPER(SUBSTR(:OLD.namn, 1, 1) || SUBSTR(:OLD.namn), 2); EXCEPTION WHEN OTHERS THEN raise_application_error(-2, 'ERROR IN TRIGGER AFTER_INSERT_ROWins_ON_test: ' || SQLERRM); END; / Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Nicoll, Iain (Calanais) 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).
RE: Trigger question
BDY.RTF Description: RTF file
Re: Trigger question
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 03, 2002 14:08 Hallo, What is wrong with this trigger: many things are wrong with this trigger, for example: - the OLD pseudo column is defined only for UPDATE and DELETE statements, but not for INSERT ones - the NEW pseudo column cannot be changed in AFTER trigger read the docs starting with Using Triggers from the Oracle9i Application Developer's Guide hth, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov 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).
Trigger question
Hallo, anyone who can please helpme with a trigger script that does the following: I want this script to run when I have updated a field in a table. update test set namn = upper(substr(namn,1,1) || substr(namn),2) I want this trigger to fire only for the selected record(row), that is going to be updated. Please help me with this, it might seem simple but I cant get it right. Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: Trigger question
Title: RE: Trigger question This should work: CREATE TRIGGER BEFORE_ROWUPD_ON_TEST BEFORE UPDATE OF namn ON test REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN :NEW.namn := UPPER(SUBSTR(:OLD.namn, 1, 1) || SUBSTR(:OLD.namn), 2); EXCEPTION WHEN OTHERS THEN raise_application_error(-2, 'ERROR IN TRIGGER BEFORE_ROWUPD_ON_TEST: ' || SQLERRM); END; / Med vänliga hälsningar/ Kind regards -- Veriba AB Per Berghäll Brigadgatan 10 581 31 Linköping Tel: +46 (0)13-362600 Fax: +46 (0)13-362625 mailto:[EMAIL PROTECTED] http://www.veriba.se -- -- Från: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] Svara till: [EMAIL PROTECTED] Skickat: Thursday, March 28, 2002 12:33 PM Till: Multiple recipients of list ORACLE-L Angående: Trigger question Hallo, anyone who can please helpme with a trigger script that does the following: I want this script to run when I have updated a field in a table. update test set namn = upper(substr(namn,1,1) || substr(namn),2) I want this trigger to fire only for the selected record(row), that is going to be updated. Please help me with this, it might seem simple but I cant get it right. Thanks in advance Roland S -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: Trigger question
Oh my! (Is that gentle enough Jared?)Roland,I admire your ability to participate in this forum using the English language, I certainly could not pose questions within a Swedish discussion group. Since you have the ability to use the language, might I suggest once again you subscribe at http://otn.oracle.com (do they have a Swedish version as well?), then type the word 'trigger' into the search box. Triggers are a wonderful, varied construct and all their glory (including samples of just the type you wish to create) will be revealed.Just a thought. No offense.David A. BarbourOracle DBA, OCPAISD512-414-1002[EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]03/28/2002 03:33 AM PSTPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: bcc: Subject: Trigger question Hallo,anyone who can please helpme with a trigger script that does the following:I want this script to run when I have updated a field in a table.update test set namn = upper(substr(namn,1,1) || substr(namn),2)I want this trigger to fire only for the selected record(row), that is going to be updated. Please help me with this, it might seem simple but I cant get it right.Thanks in advanceRoland S--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: Trigger question
Roland (and those, who thinks, that RTFM is not such a good advice), When you are saying, that you "can't get the trigger right", please be more specific: post trigger code, that you wrote, post errors, that you are getting, or if there is no errors, tell, what your trigger does not do, that you want it to do. If you don't post all these "little" things, you will be getting "RTFM" advice. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Thursday, March 28, 2002 8:19 AM Subject: Re: Trigger question Oh my! (Is that gentle enough Jared?) Roland, I admire your ability to participate in this forum using the English language, I certainly could not pose questions within a Swedish discussion group. Since you have the ability to use the language, might I suggest once again you subscribe at http://otn.oracle.com (do they have a Swedish version as well?), then type the word 'trigger' into the search box. Triggers are a wonderful, varied construct and all their glory (including samples of just the type you wish to create) will be revealed. Just a thought. No offense. David A. BarbourOracle DBA, OCPAISD512-414-1002 [EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]03/28/2002 03:33 AM PSTPlease respond to ORACLE-LTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]cc: bcc: Subject: Trigger question Hallo,anyone who can please helpme with a trigger script that does the following:I want this script to run when I have updated a field in a table.update test set namn = upper(substr(namn,1,1) || substr(namn),2)I want this trigger to fire only for the selected record(row), that is going to be updated. Please help me with this, it might seem simple but I cant get it right.Thanks in advanceRoland S--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author:INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Trigger Question
List, I have some problem with Trigger, I want to Insert a record as Primary Key on each insert into Table1 and the same time create another Primary key in table2 plus PK from Tabl1 inserting in Table2 as Forign Key, Is there any way to do all these steps in one trigger and assign this trigger to table1 or some thing like this or not? THanks Alot Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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).
Re: Trigger Question
Hamid Alavi wrote: List, I have some problem with Trigger, I want to Insert a record as Primary Key on each insert into Table1 and the same time create another Primary key in table2 plus PK from Tabl1 inserting in Table2 as Forign Key, Is there any way to do all these steps in one trigger and assign this trigger to table1 or some thing like this or not? THanks Alot Hamid Alavi Office 818 737-0526 Cell818 402-1987 Convoluted logic. Use a sequence as the key for table1 and refer to seq.currval inside the trigger to insert the FK into table2. Do not try to do in triggers what Oracle does perfectly well as a standard feature. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
RE: A Trigger question
Jared , thanks a lot ! Yes , i did it a minute after i posted this question (just did not think of it earlier). But i'm still interested to know Is there a way to let the user insert into tableA , and capture the inserts into tableB that the trigger should perform , and do those inserts manually later . Thanks a lot ! -Original Message- Sent: Wednesday, August 22, 2001 7:34 PM To: Multiple recipients of list ORACLE-L Tsk, tsk Andrey, this doesn't even require RTFM. :) create table a; ( insert your columns here) create table b; ( insert your columns here) create trigger a_insert_b; ( your trigger code here ) alter trigger a_insert_b disable. insert into a(columns) values(your values). And, the docs are online in several places. www.oradoc.com otn.oracle.com Jared Andrey Bronfin andreyb@elrontel To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] esoft.com cc: Sent by:Subject: A Trigger question [EMAIL PROTECTED] 08/22/01 08:56 AM Please respond to ORACLE-L Dear gurus ! sorry for this RTFMable question , just don't have the docs in front of me here . Assume i have a trigger my_trig that performs an insert into tableB after an insert into tableA . The question is : what happens if the trigger gets broken (either disabled , invalid or unable to do what it should do (for example if the tableB has reached maxextents) ) ? Does the user that inserts into tableA receive an ORA- error ? Is there a way to let the user insert into tableA , and capture the inserts into tableB that the trigger should perform , and do those inserts manually later ? Thanks a lot. Andrey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: 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: Andrey Bronfin 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).
RE: A Trigger question (LENGTHY!)
THANKS A LOT FOR ALL WHO REPLIED !
-Original Message-
Sent: Wednesday, August 22, 2001 7:12 PM
To: Multiple recipients of list ORACLE-L
Yes. I was curious, so I did a little test:
Create 3 tables. Note the different datatypes.
SQL create table dbm_1
2 (a varchar2(5) not null);
Table created.
SQL create table dbm_2
2 (a number not null);
Table created.
SQL create table dbm_3
2 (a varchar2(5) not null);
Table created.
Now create a trigger to populate dbm_2 with the values from dbm_1.
Note that the trigger will fail if the datatypes mismatch:
SQL create or replace trigger dbm_1_ariu
2 after insert or update
3 on dbm_1
4 for each row
5 begin
6insert into dbm_2(a) values (:NEW.a);
7 end;
8 create trigger dbm_1_ariu
9 after insert or update
10 on dbm_1
11 for each row
12 begin
13insert into dbm_2(a) values (:NEW.a);
14 exception
15when others then
16 raise;
17 end;
18 /
Trigger created.
Test the trigger:
SQL insert into dbm_1 values (1);
1 row created.
SQL select count(*) from dbm_1;
COUNT(*)
--
1
SQL select count(*) from dbm_2;
COUNT(*)
--
1
SQL insert into dbm_1 values ('A');
insert into dbm_1 values ('A')
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at DBM.DBM_1_ARIU, line 5
ORA-04088: error during execution of trigger 'DBM.DBM_1_ARIU'
SQL rollback;
Rollback complete.
Now, modify the trigger to insert values into dbm_3 if the insert fails.
SQL create or replace trigger dbm_1_ariu
2 after insert or update
3 on dbm_1
4 for each row
5 begin
6insert into dbm_2(a) values (:NEW.a);
7 exception
8when others then
9 insert into dbm_3(a) values (:NEW.a);
10 end;
11
12 /
Trigger created.
SQL insert into
2
SQL insert into dbm_1(a) values (1);
1 row created.
SQL select count(*) from dbm_1;
COUNT(*)
--
1
SQL select count(*) from dbm_2;
COUNT(*)
--
1
SQL select count(*) from dbm_3;
COUNT(*)
--
0
SQL insert into dbm_1(a) values ('Brian');
1 row created.
SQL select count(*) from dbm_1;
COUNT(*)
--
2
SQL select count(*) from dbm_2;
COUNT(*)
--
1
SQL select count(*) from dbm_3;
COUNT(*)
--
1
Notice that the trigger handled the exception perfectly. Of
course, you'd want to code the exception more accurately, but
I think this example proves that it is possible.
Brian
--
--
| Brian McGraw -- Oracle DBA |
| Central Alabama Oracle Users Group |
||
| mailto:[EMAIL PROTECTED] |
| http://bmcgraw.home.mindspring.com |
--
Andrey Bronfin wrote:
Dear gurus !
sorry for this RTFMable question , just don't have the docs in front of me
here .
Assume i have a trigger my_trig that performs an insert into tableB after
an
insert into tableA .
The question is : what happens if the trigger gets broken (either disabled
,
invalid or unable to do what it should do (for example if the tableB has
reached maxextents) ) ?
Does the user that inserts into tableA receive an ORA- error ?
Is there a way to let the user insert into tableA , and capture the
inserts
into tableB that the trigger should perform , and do those inserts
manually
later ?
Thanks a lot.
Andrey.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
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: Brian McGraw
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:
A Trigger question
Dear gurus ! sorry for this RTFMable question , just don't have the docs in front of me here . Assume i have a trigger my_trig that performs an insert into tableB after an insert into tableA . The question is : what happens if the trigger gets broken (either disabled , invalid or unable to do what it should do (for example if the tableB has reached maxextents) ) ? Does the user that inserts into tableA receive an ORA- error ? Is there a way to let the user insert into tableA , and capture the inserts into tableB that the trigger should perform , and do those inserts manually later ? Thanks a lot. Andrey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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).
Re: A Trigger question (LENGTHY!)
Yes. I was curious, so I did a little test:
Create 3 tables. Note the different datatypes.
SQL create table dbm_1
2 (a varchar2(5) not null);
Table created.
SQL create table dbm_2
2 (a number not null);
Table created.
SQL create table dbm_3
2 (a varchar2(5) not null);
Table created.
Now create a trigger to populate dbm_2 with the values from dbm_1.
Note that the trigger will fail if the datatypes mismatch:
SQL create or replace trigger dbm_1_ariu
2 after insert or update
3 on dbm_1
4 for each row
5 begin
6insert into dbm_2(a) values (:NEW.a);
7 end;
8 create trigger dbm_1_ariu
9 after insert or update
10 on dbm_1
11 for each row
12 begin
13insert into dbm_2(a) values (:NEW.a);
14 exception
15when others then
16 raise;
17 end;
18 /
Trigger created.
Test the trigger:
SQL insert into dbm_1 values (1);
1 row created.
SQL select count(*) from dbm_1;
COUNT(*)
--
1
SQL select count(*) from dbm_2;
COUNT(*)
--
1
SQL insert into dbm_1 values ('A');
insert into dbm_1 values ('A')
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at DBM.DBM_1_ARIU, line 5
ORA-04088: error during execution of trigger 'DBM.DBM_1_ARIU'
SQL rollback;
Rollback complete.
Now, modify the trigger to insert values into dbm_3 if the insert fails.
SQL create or replace trigger dbm_1_ariu
2 after insert or update
3 on dbm_1
4 for each row
5 begin
6insert into dbm_2(a) values (:NEW.a);
7 exception
8when others then
9 insert into dbm_3(a) values (:NEW.a);
10 end;
11
12 /
Trigger created.
SQL insert into
2
SQL insert into dbm_1(a) values (1);
1 row created.
SQL select count(*) from dbm_1;
COUNT(*)
--
1
SQL select count(*) from dbm_2;
COUNT(*)
--
1
SQL select count(*) from dbm_3;
COUNT(*)
--
0
SQL insert into dbm_1(a) values ('Brian');
1 row created.
SQL select count(*) from dbm_1;
COUNT(*)
--
2
SQL select count(*) from dbm_2;
COUNT(*)
--
1
SQL select count(*) from dbm_3;
COUNT(*)
--
1
Notice that the trigger handled the exception perfectly. Of
course, you'd want to code the exception more accurately, but
I think this example proves that it is possible.
Brian
--
--
| Brian McGraw -- Oracle DBA |
| Central Alabama Oracle Users Group |
||
| mailto:[EMAIL PROTECTED] |
| http://bmcgraw.home.mindspring.com |
--
Andrey Bronfin wrote:
Dear gurus !
sorry for this RTFMable question , just don't have the docs in front of me
here .
Assume i have a trigger my_trig that performs an insert into tableB after an
insert into tableA .
The question is : what happens if the trigger gets broken (either disabled ,
invalid or unable to do what it should do (for example if the tableB has
reached maxextents) ) ?
Does the user that inserts into tableA receive an ORA- error ?
Is there a way to let the user insert into tableA , and capture the inserts
into tableB that the trigger should perform , and do those inserts manually
later ?
Thanks a lot.
Andrey.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
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: Brian McGraw
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).
Re: A Trigger question
Tsk, tsk Andrey, this doesn't even require RTFM. :) create table a; ( insert your columns here) create table b; ( insert your columns here) create trigger a_insert_b; ( your trigger code here ) alter trigger a_insert_b disable. insert into a(columns) values(your values). And, the docs are online in several places. www.oradoc.com otn.oracle.com Jared Andrey Bronfin andreyb@elrontel To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] esoft.com cc: Sent by:Subject: A Trigger question [EMAIL PROTECTED] 08/22/01 08:56 AM Please respond to ORACLE-L Dear gurus ! sorry for this RTFMable question , just don't have the docs in front of me here . Assume i have a trigger my_trig that performs an insert into tableB after an insert into tableA . The question is : what happens if the trigger gets broken (either disabled , invalid or unable to do what it should do (for example if the tableB has reached maxextents) ) ? Does the user that inserts into tableA receive an ORA- error ? Is there a way to let the user insert into tableA , and capture the inserts into tableB that the trigger should perform , and do those inserts manually later ? Thanks a lot. Andrey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: 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).
