Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    Slovakia
    Posts
    32

    Question Unanswered: How to reference the primary key of a newly added record inside my trigger?

    Please help me somebody solve my problem with my first trigger:

    ALTER TRIGGER partner_update
    ON dbo.partner
    FOR UPDATE
    AS
    INSERT INTO partner (name) SELECT name FROM deleted
    UPDATE invoice SET id_partner= *** WHERE id_partner = (SELECT id_partner FROM deleted)


    *** - here I want to add a "reference" to the newly added record's automatically generated primary key (not to the updated!) Is it possible?
    Last edited by sztomi; 06-24-04 at 12:27.
    (sz)Tomi

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    ALTER TRIGGER partner_update
    ON dbo.partner
    FOR UPDATE
    AS
    BEGIN
       INSERT INTO partner (name)
          SELECT name FROM deleted
    
       UPDATE o
          SET id_partner= i.id_partner
          FROM dbo.invoice AS o
          INNER JOIN deleted AS d
             ON (d.id_partner = o.id_partner)
          LEFT OUTER JOIN inserted AS i
             ON (i.id_partner = o.id_partner)
    END
    This trigger takes a rather perverse view of the universe, allowing for the possiblity that some low-down, high-smelling, bundle-of-dung might possibly change the value of the id_partner column. The invoice table might or might not like that, since the trigger would then NULL out the invoice.id_partner column!

    -PatP

Posting Permissions

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