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!

two triggers to one table

Discussion in 'General' started by monkey, Apr 20, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Dear All!
    I appologise, am completely new to databases&oracle, and I wanted to ask what to do if we have only one table in the db :

    name surname
    n1 s1
    n2 s2
    n3 s3

    and we want to create 2 DIFFERENT triggers:
    one, which would change name1 if we update surname1

    and another

    which would change name2 if we update surname2.

    Please, how would the code look like in oracle? Just logical?

    Many thanks!!!
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I have no idea why you would want to separate out an update into two triggers in that fashion, but you could with something like the following:


    Code (Text):
    CREATE OR REPLACE TRIGGER surname_s1
      BEFORE
        UPDATE OF surname
      ON employees
      FOR EACH ROW
        WHEN (OLD.surname = 's1')
    BEGIN
      NEW.name = 'George';
    END surname_s1;
     
    and

    Code (Text):
    CREATE OR REPLACE TRIGGER surname_s2
      BEFORE
        UPDATE OF surname
      ON employees
      FOR EACH ROW
        WHEN (OLD.surname = 's2')
    BEGIN
      NEW.name = 'Fred';
    END surname_s2;
     

    However, the following single trigger does the work of the two above:

    Code (Text):
    CREATE OR REPLACE TRIGGER surname_any
      BEFORE
        UPDATE OF surname
      ON employees
      FOR EACH ROW
    BEGIN
      CASE OLD.surname
        WHEN 's1' THEN
          NEW.name = 'George';
        WHEN 's2' THEN
          NEW.name = 'Fred';
        ELSE
          NULL;
      END CASE;
    END surname_any;
     
    Mind you, none of the above code has been compiled or in any way been tested...
     
  3. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    I tried this and got ora - 04098


    Please, can someone help?
    Many thanks!!!!
    p.s trigger is ok, error is in the update line:

    Code (Text):
    create trigger t1 after update of msurname on m for each row when(old.,surname='AAA')
    BEGIN
    UPDATE m
    new.mname='trigger1';
    end t1;
    /









    update m
    set msurname='UPDATED'
    where idm=1;
     
  4. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    p.s. error is in line : update m

    Plase, help someone!!!
    Many thanks!!!
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Pay attention...
    An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.

    It is the incorrect trigger

    Code (SQL):


    CREATE TRIGGER t1
    after UPDATE OF msurname ON m
      FOR each ROW
      WHEN(OLD.surname='AAA')
    BEGIN

    -- error!
     :NEW.mname='trigger1';

    END t1;
     /
     
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    hi,

    here use SET command and then remove new.
     
  7. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Trigger was ok when I did it, but when I wrote then update paragraph, which was ment to start the tirgger, then I got mistake...
     
  8. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    is it working now?