This discussion is archived
12 Replies Latest reply: Mar 27, 2013 11:38 PM by OBYYS RSS

Automatic Trigger Update

OBYYS Newbie
Currently Being Moderated
Hi I use Oracle database 11g release 2 and SQLDeveloper Version 3.2.20.09
I have two tables, table A (ID, Name, Phone)… and table B (ID, salary, Bonus, Phone)….. Please can someone give me the proper syntax for a Trigger, that with every entry made in table B, the Trigger automatically updates the phone column in table B with data contained in phone column in table A, where ID in table A and ID entered in table B matches…
Thank you, please I await your helpful reply..
  • 1. Re: Automatic Trigger Update
    Justin Cave Oracle ACE
    Currently Being Moderated
    Why do you want to violate normalization by storing the same data in two different places?

    Justin
  • 2. Re: Automatic Trigger Update
    damorgan Oracle ACE Director
    Currently Being Moderated
    And why have you named columns with reserved words?

    My recommendation is that you burn this to the ground and start over with a better design and a proper object naming convention.

    This appears to be school work and if it is, and your instructor is telling you to do these things, there may be a good lesson here but it is obscured by many issues your instructor needs to consider. Feel free to have that person contact me directly if they wish. http://www.morganslibrary.org/contact.html.
  • 3. Re: Automatic Trigger Update
    OBYYS Newbie
    Currently Being Moderated
    Thanks Morgan for the reply and contact.... though it is not a school work, will explain better via email...
  • 4. Re: Automatic Trigger Update
    OBYYS Newbie
    Currently Being Moderated
    Thanks for your reply JUSTIN,
    The point is that I'm working with another software which needs to automatically pull and deliver SMS messages to various members in the database. However, this software requires a particular table that will hold the phone number of receivers (i.e. Members in the database), and various unique messages that will be entered constantly in relation to each members ID_number, which needs to be delivered to each member via SMS.
    This is what gave rise to my request for a proper Trigger syntax that would automatically update the column PHONE in Table B with data contained in the column PHONE in table A (with every entry made in Table B, where ID entered in B is thesame with I'd existing in A).
    Thanks for your concern, i await your reply

    OBI
  • 5. Re: Automatic Trigger Update
    Justin_Mungal Journeyer
    Currently Being Moderated
    OBYYS wrote:
    Thanks for your reply JUSTIN,
    The point is that I'm working with another software which needs to automatically pull and deliver SMS messages to various members in the database. However, this software requires a particular table that will hold the phone number of receivers (i.e. Members in the database), and various unique messages that will be entered constantly in relation to each members ID_number, which needs to be delivered to each member via SMS.
    This is what gave rise to my request for a proper Trigger syntax that would automatically update the column PHONE in Table B with data contained in the column PHONE in table A (with every entry made in Table B, where ID entered in B is thesame with I'd existing in A).
    Thanks for your concern, i await your reply

    OBI
    So your trigger is just a way of working around the app, from what I understand. Is it possible to adjust the app? Working around an application isn't a good reason to break normalization.
  • 6. Re: Automatic Trigger Update
    OBYYS Newbie
    Currently Being Moderated
    Hi JUSTIN,

    The App cannot be adjusted and I tried writing the TRIGGER syntax and I came up with this

    CREATE OR REPLACE TRIGGER “MEMSCOPY” AFTER INSERT ON TABLEA FOR EACH ROW BEGIN UPDATE TABLEA SET (PHONE_NUMBER) = (SELECT T.PHONE_NUMBER FROM TABLEB T);END;

    But when I perform any INSERT in the TABLEA table it gives me the following error:

    Error report:
    SQL Error: ORA-04091: table SAJA.TABLEA is mutating, trigger/function may not see it
    ORA-06512: at "SAJA.MEMSCOPY", line 1
    ORA-04088: error during execution of trigger 'SAJA.MEMSCOPY'
    04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
    *Cause:    A trigger (or a user defined plsql function that is referenced in
    this statement) attempted to look at (or modify) a table that was
    in the middle of being modified by the statement which fired it.
    *Action:   Rewrite the trigger (or function) so it does not read that table.

    Please how do I rectify this problem and what are the implications for violating normalization?
    I appreciate your time…
    OBI
  • 7. Re: Automatic Trigger Update
    sb92075 Guru
    Currently Being Moderated
    CREATE VIEW then data is always available & NOT duplicated.
  • 8. Re: Automatic Trigger Update
    OBYYS Newbie
    Currently Being Moderated
    Hi,

    @sb92075 maybe you didnt get the part when i said that the SMS gateway software needs to periodically check for newly inserted rows in the table autmatically, which it will send out as message...... will creating a VIEW aid that function/task?
  • 9. Re: Automatic Trigger Update
    sb92075 Guru
    Currently Being Moderated
    OBYYS wrote:
    Hi,

    @sb92075 maybe you didnt get the part when i said that the SMS gateway software needs to periodically check for newly inserted rows in the table autmatically, which it will send out as message...... will creating a VIEW aid that function/task?
    If the VIEW_NAME is the same as the TABLE_NAME; how will the application know the difference?
  • 10. Re: Automatic Trigger Update
    damorgan Oracle ACE Director
    Currently Being Moderated
    Look at this:
    http://www.morganslibrary.org/reference/pkgs/dbms_change_notification.html

    No application should ever be querying a table to see if something has changed unless scalability is never going to be an issue.
  • 11. Re: Automatic Trigger Update
    OBYYS Newbie
    Currently Being Moderated
    Hi,

    @sb92075, I think what you said makes sense, but whereby I create a view to query one table for the phone_number and then another table (the inserted table) for the data I need to be sent as message, and then name the View to be the same name with the table that is being inserted e.g. TABLEB…. When I create a trigger or sql statement to be used by the SMS software and then specify “from TABLEB”, how will it know I meant to query the View rather than the inserted table, as they both have the same name?

    Thanks for the reply tho....
  • 12. Re: Automatic Trigger Update
    OBYYS Newbie
    Currently Being Moderated
    @damorgan..... I couldnt make-out anything from the link... what does it really emphasis on and the statements as well?

    Thanks..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points