Results 1 to 12 of 12

Thread: Simple trigger

  1. #1
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16

    Unanswered: Simple trigger

    Hi

    im trying to implement a simple trigger, i had it working fine in oracle but am finding it difficult to convert it to the MS SQL 2005 layout.

    I have two tables:
    1) don (columns A,B,C)
    2) cur (columns A,B)

    Basically i just want to insert the value of A and B from table don into table cur after an insert.

    this is what i have:

    ________________________________

    ALTER TRIGGER [TG_doncur]
    ON don
    AFTER INSERT
    AS
    BEGIN
    INSERT cur
    SET A = A, B=B
    END
    _______________________________

    i have an if clause aswell but i just wanna get the basics first.

    any help would be great

    Bil

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    This is an except from Books Online ... under the topic triggers, creating:


    deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:
    SELECT *FROM deleted



    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16
    Hi thanks for that

    so i tried:

    ALTER TRIGGER [TG_doncur]
    ON don
    AFTER INSERT
    AS
    BEGIN
    INSERT cur values ((select A from A), (Select B from B))
    END

    This does not work
    error = Subqueries are not allowed in this context

    Please advise, it cant be that hard moving a value from one table to another once a value in the first table has been inserted

    Bil

  4. #4
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16
    Does any one have any idea how to do this type of function.

    It seems so easy in oracle but obviously not in MS SQL.

    please help

    Bil

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am guessing what you want is this????
    Code:
    ALTER TRIGGER [TG_doncur]
    ON don
    AFTER INSERT
    AS
    BEGIN
    INSERT cur (select A, B from inserted)
    END

  6. #6
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16
    HI thanks for replying

    That method does not work

    i can have a statement such as:

    Insert cur Values (23,34)

    but i cant insert values that have been passed in from the 'after insert' statement.

    The oracle way would be along the lines of:
    insert into cur values (:NEW.A,:NEW.B);

    i just dont know ho to do this in MS SQL.

    many thanks

    Bil

  7. #7
    Join Date
    Feb 2007
    Posts
    5
    how about:

    alter trigger dbo.tg_doncur

    on don

    for insert

    as

    begin
    insert into cur (a,b)
    select a,b from inserted
    end

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How about this. Suppose the trigger is in place, and everything works as you want it to. I run the following command:

    Code:
    insert into don (A, B, C)
    values (1, 2, 3)
    What is the exact entire outcome?

  9. #9
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16
    ***SOLUTION***

    Hi guys thanks for your help on this matter.

    The answer is kinda like what has been said

    INSERT cur (A,B)
    Select Distinct i.A, i.B
    From inserted i

    Looks so simple,
    note: the 'inserted' is the key word. I was looking for the value in the first table (don) but infect it looks like u can use the word inserted to get the value that was put into the original table.

    U can also leave particular fields blank (if not null allowed of course) by just not including them in the insert statement i.e i could just insert a value for A
    e.g.
    INSERT cur (A)
    Select Distinct getdate()
    From inserted i


    Many thanks for all your help and hopefully this will help some other MS SQL noobies

    Bil

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "inserted" and "deleted" are two virtual tables that have the same schema as the target table. You can reference them in any joins exactly as you would a permanent table or a temporary table, or a view.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by blindman
    "inserted" and "deleted" are two virtual tables that have the same schema as the target table. You can reference them in any joins exactly as you would a permanent table or a temporary table, or a view.
    So what am I ... chopped liver?

    That's exactly what i quoted for the questioner from BOL. Maybe he's on a "tell me twice" circuit?

    -- This is all just a Figment of my Imagination --

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hey, YOU just repeated what was in Books Online. So what is Books Online - chopped pancreas?

    Besides, by prose was much more elegant.
    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
  •