Trigger for each row | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Trigger for each row

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.
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 />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
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.
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.
Rakib, welcome to the forums!.
This thread is 8 years old.:)
]]>