Results 1 to 4 of 4

Thread: Trigger T-SQL

  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: Trigger T-SQL

    This is an update trigger

    I have never worked with triggers b4 I understand most of the stuff im looking at except below.

    When looking at it I am guessing that if the update is an insert get the Ref_No (PK) and then execute the mySproc SP. Is that correct?

    SELECT @Ref_No = INSERTED.Ref_No
    FROM INSERTED

    EXEC mySproc @Ref_No

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope.
    If there has been an insert OR update, get ONE of the ref_no (there could be bazillions) and run the sproc for that.

    Check out CREATE TRIGGER in Books Online
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by mikezx10
    SELECT @Ref_No = INSERTED.Ref_No
    FROM INSERTED
    Never write triggers that way. A trigger must perform correctly whether 0, 1 or many rows are updated. In this case, if more than one row is updated the INSERTED table will contain multiple rows but only one (arbitrary) value will get assigned to @Ref_No.

    Also, ask yourself why you can't do whatever task this trigger performs in a normal stored proc rather than a trigger. In general, avoid using triggers for any process that updates or modifies data. It's usually much better to do it in a normal stored proc rather than a trigger.

  4. #4
    Join Date
    Oct 2003
    Posts
    233
    Thanks!

    When we FIX this db we will not use any triggers (we will also add primary keys AND! relationships!)

Posting Permissions

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