1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Is a selective update of a column via a Trigger possible here?

Discussion in 'SQL PL/SQL' started by mtrunyan, Sep 26, 2012.

  1. mtrunyan

    mtrunyan Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    United States
    Hi, I'm very out of touch with PL/SQL, and I need to know if I can get this working SQL statement:

    update
    notifymessages
    set message = replace(message,'mydocs','mydocs-ext')
    where
    userid in
    (select
    u.id
    from
    kuaf u,
    kuaf g
    where
    u.groupid = g.id
    and g.name like '%External');

    to work in a before or after insert trigger on the notifymessages table. So I want to update the message text for certain userids. First of all I don't know if I need to arrange the where clause logic into some type of cursor construct, and I know I may have problems with a mutating trigger. But I figure since this is only an "on insert" trigger, if an update is applied, it shouldn't re-fire the on insert trigger and I might be Ok. Any clues on how to construct the trigger will be appreciated, and I can see if I can make some progress. Thanks! - Mark
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    For all releases of oracle placing that code in any trigger that is created on the same table you're modifying will return the ugly:

    ORA-04091: table <owner>.notifymessages is mutating, trigger/function may not see it

    Even compound triggers in 11gR2 return that ugly message:

    Code (SQL):
    SQL> CREATE OR REPLACE TRIGGER after_emp_updt
      2  FOR UPDATE OF sal ON emp
      3  compound TRIGGER
      4
      5  TYPE salary_t IS TABLE OF emp.sal%TYPE;
      6  salary salary_t;
      7
      8  TYPE empno_t  IS TABLE OF emp.empno%TYPE;
      9  empnum empno_t;
     10
     11  BEFORE statement IS
     12  BEGIN
     13          SELECT sal, empno
     14          bulk collect INTO salary, empnum
     15          FROM emp;
     16  END BEFORE statement;
     17
     18  after each ROW IS
     19  BEGIN
     20          FOR i IN 1..salary.COUNT() loop
     21                  IF :NEW.sal >= salary(i) THEN
     22                          UPDATE emp
     23                          SET comm = salary(i)*.1
     24                          WHERE empno = empnum(i);
     25                  END IF;
     26          END loop;
     27  END after each ROW;
     28  END after_emp_updt;
     29  /
     
    TRIGGER created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> UPDATE emp
      2  SET sal=sal*1.15
      3  WHERE comm IS NULL;
    UPDATE emp
           *
    ERROR at line 1:
    ORA-04091: TABLE BING.EMP IS mutating, TRIGGER/FUNCTION may NOT see it
    ORA-06512: at "BING.AFTER_EMP_UPDT", line 20
    ORA-04088: error during execution OF TRIGGER 'BING.AFTER_EMP_UPDT'

    SQL>
    You could put that code into a procedure that can be called from a trigger, however that also errors out:

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE set_comm(p_empno IN NUMBER, p_sal IN NUMBER, p_oldsal NUMBER)
      2  IS
      3  pragma autonomous_transaction;
      4  BEGIN
      5          IF p_sal >= p_oldsal*1.10 THEN
      6                  UPDATE emp
      7                  SET comm = p_sal*.10
      8                  WHERE empno = p_empno;
      9          END IF;
     10  END;
     11  /
     
    PROCEDURE created.
     
    SQL>
    SQL> CREATE OR REPLACE TRIGGER after_emp_updt
      2  after UPDATE OF sal ON emp
      3  FOR each ROW
      4  BEGIN
      5                  set_comm(:NEW.empno, :NEW.sal, :OLD.sal);
      6  END;
      7  /
     
    TRIGGER created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> UPDATE emp
      2  SET sal=sal*1.15
      3  WHERE comm IS NULL;
    UPDATE emp
           *
    ERROR at line 1:
    ORA-00060: deadlock detected while waiting FOR resource
    ORA-06512: at "BING.SET_COMM", line 6
    ORA-06512: at "BING.AFTER_EMP_UPDT", line 2
    ORA-04088: error during execution OF TRIGGER 'BING.AFTER_EMP_UPDT'

    SQL>
    You will need to schedule a job to update the table if you want this to automatically occur; of course scheduling that job will be difficult as you won't know when updates are occurring and you won't be able to synchronize the job with the updates.

    There is no easy way to implement what you want given the code you provided.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Actually no need for a Job. This Can be done with help of BEFORE INSERT TRIGGER.

    Code (SQL):
    SQL> CREATE TABLE kgaf
      2  (
      3  groupid         NUMBER(5) ,
      4  name            VARCHAR2(20),
      5  CONSTRAINT kgaf_pk PRIMARY KEY (groupid)
      6  );

    TABLE created.

    SQL>
    SQL> CREATE TABLE kuaf
      2  (
      3  userid         NUMBER(5) ,
      4  groupid        NUMBER(10),
      5  CONSTRAINT kuaf_pk PRIMARY KEY (userid),
      6  CONSTRAINT kuaf_fk FOREIGN KEY (groupid) REFERENCES kgaf(groupid)
      7  );

    TABLE created.

    SQL>
    SQL> CREATE TABLE notifymessages
      2  (
      3  userid      NUMBER(5) ,
      4  message     VARCHAR2(100),
      5  CONSTRAINT notifymessages_fk
      6    FOREIGN KEY (userid)
      7    REFERENCES kuaf (userid)
      8  ) ;

    TABLE created.

    SQL> INSERT ALL
      2  INTO kgaf (groupid, name)
      3  VALUES (1000, 'User External')
      4  INTO kgaf (groupid, name)
      5  VALUES (1001, 'User Internal')
      6  INTO kgaf (groupid, name)
      7  VALUES (1002, 'Agency External')
      8  INTO kgaf (groupid, name)
      9  VALUES (1003, 'Agency Internal')
     10  SELECT *
     11  FROM Dual;

    4 ROWS created.

    SQL>
    SQL>
    SQL> INSERT ALL
      2  INTO kuaf (userid, groupid)
      3  VALUES (100, 1000)
      4  INTO kuaf (userid, groupid)
      5  VALUES (101, 1001)
      6  INTO kuaf (userid, groupid)
      7  VALUES (102, 1002)
      8  INTO kuaf (userid, groupid)
      9  VALUES (103, 1003)
     10  INTO kuaf (userid, groupid)
     11  VALUES (104, 1000)
     12  INTO kuaf (userid, groupid)
     13  VALUES (105, 1001)
     14  INTO kuaf (userid, groupid)
     15  VALUES (106, 1002)
     16  INTO kuaf (userid, groupid)
     17  VALUES (107, 1003)
     18  INTO kuaf (userid, groupid)
     19  VALUES (108, 1000)
     20  INTO kuaf (userid, groupid)
     21  VALUES (109, 1001)
     22  SELECT *
     23  FROM Dual;

    10 ROWS created.

    SQL>
    SQL> Commit;

    Commit complete.

    SQL>
    SQL>
    SQL> SELECT * FROM kgaf;

       GROUPID NAME
    ---------- --------------------
          1000 USER External
          1001 USER Internal
          1002 Agency External
          1003 Agency Internal

    SQL>
    SQL> SELECT * FROM kuaf;

        USERID    GROUPID
    ---------- ----------
           100       1000
           101       1001
           102       1002
           103       1003
           104       1000
           105       1001
           106       1002
           107       1003
           108       1000
           109       1001

    10 ROWS selected.

    SQL> CREATE OR REPLACE TRIGGER before_kuaf_insrt
      2  BEFORE INSERT  ON notifymessages
      3  FOR EACH ROW
      4  DECLARE
      5  external_flg    NUMBER;
      6  BEGIN
      7
      8      SELECT COUNT(*)
      9      INTO external_flg
     10      FROM kuaf u,
     11           kgaf g
     12      WHERE u.userid = :NEW.USERID
     13      AND   u.groupid = g.groupid
     14      AND   g.name LIKE '%External';
     15
     16      IF external_flg =1
     17      THEN
     18
     19          :NEW.message  :=  REPLACE (:NEW.message,'mydocs','mydocs-ext' ) ;
     20
     21      END IF;
     22
     23  END;
     24  /

    TRIGGER created.

    SQL> INSERT INTO notifymessages (USERID, MESSAGE )
      2  VALUES (100, 'The file is generated in K:\Project\mydocs') ;

    1 ROW created.

    SQL>
    SQL> INSERT INTO notifymessages (USERID, MESSAGE )
      2  VALUES (101, 'The file is generated in K:\Project\mydocs') ;

    1 ROW created.

    SQL>
    SQL> INSERT INTO notifymessages (USERID, MESSAGE )
      2  VALUES (102, 'The file is generated in K:\Project\mydocs') ;

    1 ROW created.

    SQL>
    SQL> INSERT INTO notifymessages (USERID, MESSAGE )
      2  VALUES (103, 'The file is generated in K:\Project\mydocs') ;

    1 ROW created.

    SQL>
    SQL> commit;

    Commit complete.
    SQL> COLUMN message format A50
    SQL> SELECT * FROM notifymessages;

        USERID MESSAGE
    ---------- --------------------------------------------------
           100 The file IS generated IN K:\Project\mydocs-ext
           101 The file IS generated IN K:\Project\mydocs
           102 The file IS generated IN K:\Project\mydocs-ext
           103 The file IS generated IN K:\Project\mydocs

    SQL>
     
  4. mtrunyan

    mtrunyan Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    United States
    Raj you're awesome!! It worked like a charm. Now external users get proper hyperlinks in their e-mail notifications. Thanks! Zargon, you had good points, but the key was I didn't need an on update trigger but an on insert trigger which apparently avoids the mutating trap.

    - Mark
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    Either trigger would have worked; an insert trigger does NOT avoid a mutating table 'trap' simply because it's an 'insert' trigger. Note how Rajavu wrote the trigger; it is NOT updating the table data, it's modifying the supplied value before it gets to the table. If we write this as a before update trigger:

    Code (SQL):
    SQL> CREATE OR REPLACE TRIGGER before_kuaf_update
      2  BEFORE UPDATE ON notifymessages
      3  FOR EACH ROW
      4  DECLARE
      5   external_flg       NUMBER;
      6  BEGIN
      7
      8  SELECT COUNT(*)
      9  INTO external_flg
     10  FROM kuaf u,
     11            kgaf g
     12  WHERE u.userid = :NEW.USERID
     13  AND   u.groupid = g.groupid
     14  AND   g.name LIKE '%External';
     15
     16   IF external_flg =1
     17   THEN
     18
     19            :NEW.message  := REPLACE (:NEW.message,'mydocs','mydocs-ext' ) ;
     20
     21   END IF;
     22
     23   END;
     24   /
     
    TRIGGER created.
     
    SQL>
    SQL> UPDATE notifymessages
      2  SET message = 'The file is generated in K:\Project\mydocs'
      3  WHERE userid = 100 ;
     
    1 ROW updated.
     
    SQL>
    SQL> UPDATE notifymessages
      2  SET message = 'The file is generated in K:\Project\mydocs'
      3  WHERE userid = 101 ;
     
    1 ROW updated.
     
    SQL>
    SQL> UPDATE notifymessages
      2  SET message = 'The file is generated in K:\Project\mydocs'
      3  WHERE userid = 102 ;
     
    1 ROW updated.
     
    SQL>
    SQL> UPDATE notifymessages
      2  SET message = 'The file is generated in K:\Project\mydocs'
      3  WHERE userid = 103 ;
     
    1 ROW updated.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> COLUMN message format A50
    SQL> SELECT * FROM notifymessages;
     
        USERID MESSAGE
    ---------- --------------------------------------------------
           100 The file IS generated IN K:\Project\mydocs-ext
           101 The file IS generated IN K:\Project\mydocs
           102 The file IS generated IN K:\Project\mydocs-ext
           103 The file IS generated IN K:\Project\mydocs
    SQL>
    it still works.

    My apologies for having to work for a living such that I don't have time to investigate every possibiility.