Results 1 to 4 of 4

Thread: Trigger Problem

  1. #1
    Join Date
    Jan 2012
    Posts
    4

    Unanswered: Trigger Problem

    Hi,I come form china , my english is very very poor, i have a question, my code
    is here:
    the database version:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production

    My trigger:
    CREATE OR REPLACE TRIGGER T1_BEFORE_INSERT
    BEFORE INSERT ON T_1
    FOR EACH ROW
    DECLARE
    V_T2_EXP EXCEPTION;
    V_FORMTYPE T_1.FORMTYPE%TYPE := :NEW.FORMTYPE;
    BEGIN
    IF V_FORMTYPE = '1' THEN
    RAISE V_T2_EXP;
    END IF;
    EXCEPTION
    WHEN V_T2_EXP THEN
    INSERT INTO t_2 VALUES (:NEW.id,:NEW.Formtype);
    END D_BEFORE_DELETE;

    MY TABLE :
    CREATE TABLE T_1
    (
    ID VARCHAR2(100),
    formtype VARCHAR2(100)
    )
    CREATE TABLE T_2
    (
    ID VARCHAR2(100),
    formtype VARCHAR2(100)
    )
    -------------------------
    my question:
    when the formtype = 1 ,i want to insert data to t_2,others insert t_1,i use user define exception,but i fell it does not work,when i raise a user excption,i think,the t_1 should insert data,while t_2 should insert data,but ,t_1,t_2 all insert data,heip me ,thank you very much.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am not sure what you actually expect/desire
    Code:
    18:24:53 SQL> connect user2/user2
    Connected.
    18:25:11 SQL> @t_1
    18:25:14 SQL> CREATE TABLE T_1
    18:25:14   2  (
    18:25:14   3  ID VARCHAR2(100),
    18:25:14   4  formtype VARCHAR2(100)
    18:25:14   5  );
    
    Table created.
    
    18:25:16 SQL> CREATE TABLE T_2
    18:25:16   2  (
    18:25:16   3  ID VARCHAR2(100),
    18:25:16   4  formtype VARCHAR2(100)
    18:25:16   5  );
    
    Table created.
    
    18:25:17 SQL> 
    18:25:17 SQL> CREATE OR REPLACE TRIGGER T1_BEFORE_INSERT
    18:25:17   2  BEFORE INSERT ON T_1
    18:25:17   3  FOR EACH ROW
    18:25:17   4  DECLARE
    18:25:17   5  V_T2_EXP EXCEPTION;
    18:25:17   6  V_FORMTYPE T_1.FORMTYPE%TYPE := :NEW.FORMTYPE;
    18:25:17   7  BEGIN
    18:25:17   8  IF V_FORMTYPE = '1' THEN
    18:25:17   9  RAISE V_T2_EXP;
    18:25:17  10  END IF;
    18:25:17  11  EXCEPTION
    18:25:17  12  WHEN V_T2_EXP THEN
    18:25:17  13  INSERT INTO t_2 VALUES (:NEW.id,:NEW.Formtype);
    18:25:17  14  END D_BEFORE_DELETE;
    18:25:17  15  /
    
    Trigger created.
    
    18:25:17 SQL> insert into t_1 values(1,'1');
    
    1 row created.
    
    18:25:57 SQL> select * from t_2;
    
    ID
    --------------------------------------------------------------------------------
    FORMTYPE
    --------------------------------------------------------------------------------
    1
    1
    
    
    18:26:10 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2012
    Posts
    4

    thank very much.

    i have a confuse, the exception raised , the t_1 data should not insert ,but, i have find many e_books,but nothing found. but i thank you very much.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If a trigger fails it will NOT write to the table that the trigger is on and any other inserts will fail. If it is successful everything is written. The only way you can do what you want is with a called function that is an autonomous transaction and raise an exception, which the calling program MUST handle.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •