October 6, 2005 at 4:34 pm
i am writing a trigger which on insert or update of a table wull update another table..but it is not working...
how to debug a trigger
IF EXISTS (SELECT name FROM sysobjects WHERE
name = 'trUpdateAssessmentResponse' AND type = 'TR')
DROP TRIGGER trUpdateAssessmentResponse
GO
CREATE TRIGGER trUpdateAssessmentResponse
ON tblAssessmentControlValue
FOR INSERT, UPDATE
AS
Begin Tran T1
Declare @ControlID uniqueIdentifier
Declare @EntityID uniqueIdentifier
Declare @TimePeriodID uniqueIdentifier
Declare @Response varchar(50)
--Insert into activityresponse
select @ControlID = i.ControlId,
@EntityID = x.entityID,
@TimePeriodID = x.TimePeriodID,
@Response = i.ControlValueDescription
from tblassessment x
INNER JOIN tblAssessmentControlGroup a
ON x.AssessmentID = a.assessmentID
INNER JOIN tblAssessmentControl b
ON a.assessmentID = b.assessmentId
AND a.ControlGroupId = b.ControlGroupId
INNER JOIN tblAssessmentControlValue c
ON b.AssessmentId = c.AssessmentId
AND b.ControlId = c.ControlId
AND b.ControlValueId = c.ControlValueId
INNER JOIN inserted i
ON i.AssessmentId = c.AssessmentId
AND i.ControlId = c.ControlId
AND i.ControlValueId = c.ControlValueId
IF Exists(select * from ActivityResponse where ControlID = @ControlID
AND EntityId = @EntityID
AND TimePeriodID = @TimePeriodID)
Begin
UPDATE ActivityResponse set Response = @Response
where ControlID = @ControlID
AND EntityId = @EntityID
AND TimePeriodID = @TimePeriodID
End
Else
Begin
INSERT INTO ActivityResponse
SELECT @ControlID ,
@EntityID ,
@TimePeriodID ,
@Response
End
If @@ERROR = 0
BEGIN
COMMIT TRANSACTION T1
END
Else
BEGIN
INSERT INTO RCTSExceptionLog
VALUES('E999','','Error occured in trigger trUpdateAssessmentResponse for ControlID = '
+ Convert(varchar(50),@ControlID) + ' EntityID: ' + Convert(Varchar(50), @EntityID) + ' TimePeriodID: ' + Convert(varchar(50),@TimePeriodID) + ' Response: ' + convert(varchar(50),@Response), getdate())
ROLLBACK TRANSACTION T1
END
Go
any help will be highly appriciated
October 6, 2005 at 7:48 pm
Well Without specifically telling you whats wrong with your trigger, you need to understand that triggers should be written to handle record sets.
select @ControlID = i.ControlId,
@EntityID = x.entityID,
@TimePeriodID = x.TimePeriodID,
@Response = i.ControlValueDescription
from tblassessment x
INNER JOIN tblAssessmentControlGroup a
ON x.AssessmentID = a.assessmentID
INNER JOIN tblAssessmentControl b
ON a.assessmentID = b.assessmentId
AND a.ControlGroupId = b.ControlGroupId
INNER JOIN tblAssessmentControlValue c
ON b.AssessmentId = c.AssessmentId
AND b.ControlId = c.ControlId
AND b.ControlValueId = c.ControlValueId
INNER JOIN inserted i
ON i.AssessmentId = c.AssessmentId
AND i.ControlId = c.ControlId
AND i.ControlValueId = c.ControlValueId
This code here can only handle 1 record at a time. It may not seem like a big deal, but if you perform say an insert select with more than 1 record, your trigger will not properly update your records.
you do not need to explicitly declare a transaction because if a trigger fails it will automatically roll back the insert/update.
I would recommend you do this work in 2 different triggers.
1 insert, and 1 update.
For your update do a update from
Update A
set Response = i.ControlValueDescription
from ActivityResponse
inserted on i.ControlId = a.ControlID
join ...
....
and your insert do a select into.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply