Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2005
    Posts
    2

    Unanswered: Problem Trigger/View

    I got a view that gest information från a certain table. Then i got a trigger that should copy new information from this view to a specific table. How should this trigger know when new information has been inserted in the View?

    CREATE TRIGGER copyRow
    ON person_View
    INSTEAD OF INSERT
    AS
    BEGIN
    INSERT into kontakt (nr, telefon, adress)
    values ('2', '3', '4')
    commit transaction
    END

  2. #2
    Join Date
    Aug 2005
    Posts
    9
    You are already creating a trigger that will know for sure when new data is added because it is an "Insted of Insert" trigger.
    If you are having a problem, it may be a good idea to describe the problem instead.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is important to understand that the trigger will fire whenever new data is inserted into the view, but will NOT fire if records are inserted directly into the tables upon which the view is based.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2005
    Posts
    28

    Unhappy

    Please remember that a view is a SELECT statement that gets executed at the time of usage (unlike oracle which as meta views). Hence, copyRow is triggered only for an insert statement executed on the person_View instead of the underlying table.
    E.g. INSERT INTO person_View (...
    as opposed to INSERT INTO <underlying table> (...

    If you have a trigger for INSERT on the underlying table then it is overridden by the copyRow trigger for an insert statement as above.


    Quote Originally Posted by paros
    I got a view that gest information från a certain table. Then i got a trigger that should copy new information from this view to a specific table. How should this trigger know when new information has been inserted in the View?

    CREATE TRIGGER copyRow
    ON person_View
    INSTEAD OF INSERT
    AS
    BEGIN
    INSERT into kontakt (nr, telefon, adress)
    values ('2', '3', '4')
    commit transaction
    END

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by skrishnamurthy
    Please remember that a view is a SELECT statement that gets executed at the time of usage (unlike oracle which as meta views). Hence, copyRow is triggered only for an insert statement executed on the person_View instead of the underlying table.
    E.g. INSERT INTO person_View (...
    as opposed to INSERT INTO <underlying table> (...

    If you have a trigger for INSERT on the underlying table then it is overridden by the copyRow trigger for an insert statement as above.
    Brilliant! I wish I'd said that! Oh, wait...I did...in the previous post...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2005
    Posts
    28

    Unhappy

    Yep! Guess who needs brilliance?
    Even though you answered the question correctly, you did not answer his question in clear terms

    "How should this trigger know when new information has been inserted in the View? "

    I was explaining that part and tried to ignore you. But you won't... Let's see how long we can go like this.


    Quote Originally Posted by blindman
    Brilliant! I wish I'd said that! Oh, wait...I did...in the previous post...
    Last edited by skrishnamurthy; 09-24-05 at 22:14.

  7. #7
    Join Date
    Sep 2005
    Posts
    2

    The problem

    I have 2 tables: "table1" (exist in database1) and "table2" (exist in database2).
    Then I have a view, called "view2" (exist in database2)

    When i insert data into "table1", i can read the data in "view2".
    What i want is: when new data is added to "table1" a trigger (on view2) copy some of the new information in "table2".

    Do i make myself clear?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, you make yourself clear. But unfortunately there is no way to fire a trigger on a view when the underlying records are directly updated. You will need to put your trigger on table1.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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