SQL Server Performance

Trigger for each row

Discussion in 'T-SQL Performance Tuning for Developers' started by Pratap, Oct 5, 2005.

  1. Pratap New Member

    I have written a trigger for inserting data by each row in the foreign table depending on the inserted table.

    Trigger is like this.

    create trigger LkpElement_Insert
    on LkpElement
    for INSERT
    as
    IF EXISTS (SELECT * FROM inserted)
    begin
    declare @EID int
    declare @ALRMCNT bigint
    SELECT @EID = ELEMENT_ID FROM Inserted
    SELECT @ALRMCNT = ALARM_COUNT FROM AlarmModelCount LEFT JOIN Inserted ON Inserted.ELEMENT_NAME = AlarmModelCount.ELEMENT_NAME

    insert into ElementAlarms(ELEMENT_ID,date,ALARMCOUNT)
    values(@EID,GETDATE(),@ALRMCNT )
    end
    But problem is only last row is inserted i.e depending on the inserted table.
    Can anyone please tell me asap.
  2. ghemant Moderator

    Hi,<br />please post some sample data and datastructure in order to get good answer.<br /><br /><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  3. Adriaan New Member

    You can treat "inserted" as a regular table, so don't use a VALUES list but simply a SELECT query.

    create trigger LkpElement_Insert
    on LkpElement
    for INSERT
    as
    IF EXISTS (SELECT * FROM inserted)
    begin

    INSERT INTO ElementAlarms(ELEMENT_ID, date, ALARMCOUNT)
    SELECT inserted.ELEMENT_ID, GETDATE(), AlarmModelCount.ALARM_COUNT
    FROM AlarmModelCount INNER JOIN Inserted
    ON Inserted.ELEMENT_NAME = AlarmModelCount.ELEMENT_NAME

    end

    This is one of the annoyances in BOL: if you lookup INSERT, the first thing you find is the VALUES syntax, with no mention of the SELECT syntax.
  4. rakib New Member

    Create a Cursor From First Table1
    Loop through this table
    Next Insert statement from table1 to table2

    Insert Trigger will fire every time for each record.
  5. Luis Martin Moderator

    Rakib, welcome to the forums!.
    This thread is 8 years old.:)

Share This Page