April 27, 2009 at 8:54 pm
Hi there, I'm doing a multiple insert thru a sproc on a table StudentLog (meaning there are several rows inserted at once).
StudentID StudentGrade StudentRemarks
---1-------------8
---2-------------9
---3-------------7
Now I'd like to update the column StudentRemarks with values from table ClassRemarks so I attached a For Insert trigger to StudentLog which selects StudentRemarks by StudentID, from ClassRemarks, and updates the StudentRemarks column for that student. At least that's the way I expected it to behave, i.e. row 1 gets inserted, the trigger fires, gets StudentRemark for Student 1, updates the StudentRemarks column, then moves to the next row, does the same with student 2, and so on. But what really happens is that all the StudentRemarks on row 2 and 3 get the same value of StudentsRemark from Student 1. so it seems that since this is a multiple insert (3 rows at once in this case) the trigger only fires at the beginning of the insert.
Does this work this way really? Is there an easy solution to it, other than writing an iterating procedure on the client side. I'd rather not since this is an asp.net app, which means I like to avoid unnecessary roundtrips to the db whenever I can (and the real data involves much more columns).
Appreciate any lights shed,
Jaime
April 27, 2009 at 9:36 pm
Hello,
You are correct in thinking that the Trigger will only fire one time when inserting multiple records at once.
There is an “Inserted” table available within the Trigger that you can use to process all the records that are to be inserted.
May be take a look at the BOL Topic “Using the inserted and deleted Tables” and the sample code in “Designing INSTEAD OF Triggers” for some pointers on using the Inserted table.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
April 28, 2009 at 6:37 am
Jaime,
As John said, triggers in SQL Server fire once per batch, so if your batch updates 1 row or 1 million rows the trigger fires only once. Try reading this article[/url] on triggers and if you have any questions post them here.
Also if you post some table defintions, trigger code, and some sample data like outlined int eh link in my signature then someone will be able to present you with some specific ideas about how to go about your task.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2009 at 10:24 am
Thank u guys for your attention. Well, here goes the code I tried (just 1 column for brevity 'sake):
ALTER TRIGGER EvalLogUpdate
ON dbo.EvalLog
FOR INSERT
AS
DECLARE @gStudentID uniqueidentifier
SELECT @gStudentID = StudentID FROM Inserted
exec EvalLogUpdateSproc @gStudentID = @gStudentID
ALTER PROCEDURE [dbo].[EvalLogUpdateSproc]
@gStudentID uniqueidentifier
AS
UPDATE [dbo].[EvalLog] SET
[StudentRemarks] = (SELECT StudentsLog.Remarks FROM ClassRemarks WHERE StudentID = @gStudentID)
Like I said, this will update all StudentRemarks with the remarks from StudentID 1.
I tried to add an extra where clause at the end of the procedure (in bold):
ALTER PROCEDURE [dbo].[EvalLogUpdateSproc]
@gStudentID uniqueidentifier
AS
UPDATE [dbo].[EvalLog] SET
[StudentRemarks] = (SELECT StudentsLog.Remarks FROM ClassRemarks WHERE StudentID = @gStudentID)
WHERE EvalLog.StudentID = @gStudentID
But then only the first row in EvalLog is updated.
Normally i'd solve this kind of problem doing a loop procedure on the client, but I was hoping for a better performing db-side solution.
Regards,
Jaime
April 28, 2009 at 10:48 am
In 2005 I see 3 (4) options in order of my preference:
1. Move all your code into original insert process.
2. Move the [dbo].[EvalLogUpdateSproc] code into the trigger using a join on the inserted table to get the student remarrks.
3. Create a temporary table in the trigger (Select Into #temp from inserted) and then access the temporary table in [dbo].[EvalLogUpdateSproc] to get the student remarks.
A possible offshoot of option 3 would be to populate an XML variable with the rows from inserted and pass it to [dbo].[EvalLogUpdateSproc].
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2009 at 12:37 pm
Jack Corbett (4/28/2009)
In 2005 I see 3 (4) options in order of my preference:2. Move the [dbo].[EvalLogUpdateSproc] code into the trigger using a join on the inserted table to get the student remarrks.
This would seem like the best option to me, but how would I be able to define a join between inserted and Classremarks, since the latter is in a subquery?
April 28, 2009 at 1:22 pm
I really think the best way is to put the logic into the code that is doing the insert. Why add the overhead of an update to the same table you are inserting into? It also puts all the logic in one place instead of "hiding" it in a trigger.
If I were forced to do the trigger I'd do it something like this:
IF OBJECT_ID('dbo.EvalLog') IS NOT NULL
BEGIN
DROP TABLE dbo.EvalLog
END
IF OBJECT_ID('dbo.ClassRemarks') IS NOT NULL
BEGIN
DROP TABLE dbo.ClassRemarks
END
CREATE TABLE dbo.EvalLog
(
EvalLogID INT IDENTITY(1,1),
StudentID INT,
StudentRemarks VARCHAR(10)
)
GO
CREATE TABLE dbo.ClassRemarks
(
StudentID INT,
Remarks VARCHAR(10)
)
GO
CREATE TRIGGER EvalLogUpdate ON dbo.EvalLog
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[EvalLog]
SET [StudentRemarks] = CR.Remarks
FROM
dbo.EvalLog AS EL JOIN
ClassRemarks CR ON
EL.StudentId = CR.StudentId JOIN
inserted AS I ON
CR.StudentID = I.StudentID AND
EL.EvalLogID = I.EvalLogId
END
GO
INSERT INTO dbo.ClassRemarks (
StudentID,
Remarks
)
Select
1,
'Test 1'
UNION ALL
SELECT
2,
'Test 2'
INSERT INTO dbo.evallog
(
StudentId
)
SELECT
StudentId
FROM
dbo.ClassRemarks AS CR
UNION ALL
SELECT
3
UPDATE dbo.ClassRemarks
SET Remarks = 'New Test 2'
WHERE
StudentID = 2
INSERT INTO dbo.evallog
(
StudentId
)
SELECT
StudentId
FROM
dbo.ClassRemarks AS CR
SELECT * FROM dbo.EvalLog
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2009 at 9:36 pm
Hi Jack, played a bit around with your suggestions, and I finally think the temp table approach will work better in my case, since I have all kinds of disparate data coming together in this update (that's why it can't be done during insert btw).
Thanks a lot for helping me sort it out
Regards,
Jaime
April 29, 2009 at 1:20 am
Hi Jaime,
I have some confusion in your code. here,
ALTER PROCEDURE [dbo].[EvalLogUpdateSproc]
@gStudentID uniqueidentifier
AS
UPDATE [dbo].[EvalLog] SET
[StudentRemarks] = (SELECT StudentsLog.Remarks FROM ClassRemarks WHERE StudentID = @gStudentID)
I think, you need to put where condition into above query, like below.
UPDATE [dbo].[EvalLog] SET
[StudentRemarks] = (SELECT StudentsLog.Remarks FROM ClassRemarks WHERE StudentID = @gStudentID)
where StudentID=@gStudentID
Please check.
"Don't limit your challenges, challenge your limits"
April 29, 2009 at 6:59 am
kruti (4/29/2009)
I think, you need to put where condition into above query, like below.
UPDATE [dbo].[EvalLog] SET[StudentRemarks] = (SELECT StudentsLog.Remarks FROM ClassRemarks WHERE StudentID = @gStudentID))
where StudentID=@gStudentID
Hi Kruti, thanks for taking your time.
Well a few posts up I already pointed out the bottom where clause, which also doesn't work, since it updates only the first row...
Regards,
premy
April 29, 2009 at 7:19 am
Hi Jaime,
Sorry! consider it as my reply in hurry....... 😛
"Don't limit your challenges, challenge your limits"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply