Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    5

    Question Unanswered: Instead_of Trigger

    I wish to create an INSTEAD OF TRIGGER on view vMarriage so the basis table (Marriage) can be updated by an UPDATE on vMarriage.
    How do I accomplish that? My own trigger wasn`t very successful

    Table Man(*M_S_security_num , M_Name)
    Table Woman(*W_S_Security_num,W_Name)

    Table Marriage looks like this: Marriage(*Man, *woman , start_of_marriage, end_of_marriage)

    View vMarriage(M_Name,W_Name, Married, Marriage_Ended)

    * Marks the PK
    Man and Woman in table Marriage is olso thr foreignkey of M_S_security_num and W_S_security_num

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Instead_of Trigger

    Your INSTEAD OF trigger would look something like this:

    CREATE OR REPLACE TRIGGER vmarriage_iou
    INSTEAD OF UPDATE ON vMarriage
    DECLARE
    m_id NUMBER;
    w_id NUMBER;
    BEGIN
    SELECT M_S_security_num INTO m_id FROM Man WHERE M_Name = :NEW.M_Name;
    SELECT W_S_security_num INTO w_id FROM Woman WHERE W_Name = :NEW.W_Name;
    UPDATE Marriage SET start_of_marriage = :NEW.Married,
    end_of_marriage = :NEW.Marriage_ended
    WHERE Man = m_id
    AND Woman = w_id;
    END;

    For this to work, M_Name and W_Name had better be UNIQUE on tables Man and Woman respectively!

  3. #3
    Join Date
    Nov 2002
    Posts
    5
    thanx this helped

Posting Permissions

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