Trigger problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger problem

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-04 : 10:56:59

Hi I have a trigger which seems to work sometimes but not others. I know there must be a pattern to this but can't see it.
If I update either my faultstart or faultend fields the duration field should update, but it doesn't seem to always work.
=================================================

USE [DB1]
GO
/****** Object: Trigger [dbo].[DURATION_UPDATE] Script Date: 08/04/2010 15:47:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <ICW>
-- Create date: <04/08/10>
-- Description: <Trigger to update the Duration field in calllog with the duration of a fault>
-- =============================================
ALTER TRIGGER [dbo].[DURATION_UPDATE] ON [dbo].[Detail]
FOR INSERT, UPDATE

AS

Declare @CalliD Varchar(8)
Declare @StartDate Datetime
Declare @EndDate Datetime

Select @callid=Callid FROM detail WHERE
isdate(faultstart)=1
and isdate(faultend)=1

IF @Callid IS NOT NULL
Begin

Select @StartDate = cast ([faultstart]+' '+[faultstarttime] as datetime)
from detail
where callid = @CallID

Select @EndDate = cast ([faultend]+' '+[faultendtime] as datetime)
from detail
where callid = @CallID

UPDATE CallLog
Set RepDuration = datediff(ss, @StartDate , IsNull(@EndDate,@StartDate))/60 FROM DETAIL
Where calllog.CallID = @CallID

END

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-04 : 11:04:25

seem to be working on insert but not update

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-08-04 : 11:15:12

quote:
Originally posted by icw

seem to be working on insert but not update

How about, as a test, make a TestTable and inside the trigger, log @StartDate, @EndDate, @CallID, datediff(ss, @StartDate , IsNull(@EndDate,@StartDate))/60 along with getdate() to the TestTable.

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-04 : 11:29:17

I've noticed it only updates the most recent row.
What could be casuing this?
Any ideas

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-04 : 12:03:54

just figured it out.

I had put
Select @callid=Callid FROM detail

but i should have put

Select @callid=Callid FROM inserted

Kristen
Test

22859 Posts

Posted - 2010-08-04 : 13:00:58

You are assuming that the trigger is only called with one row. The trigger will be called once for all rows in the Insert / Update, therefore you need to make the trigger able to handle multiple rows in Inserted / Deleted.

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-04 : 13:42:50

Sorry I don't understand
could you lay that out in a way a stupid person (like me) can understand

cheers

Kristen
Test

22859 Posts

Posted - 2010-08-04 : 15:36:27

I insert multiple rows:

INSERT INTO [dbo].[Detail] (Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM SomeOtherTable

or I update multiple rows:

UPDATE [dbo].[Detail]
SET Col1 = Col1 + 10
WHERE Col2 = 'XXX'

the trigger will only be called once, and INSERTED table will contain all the rows that were inserted / updated.

Your trigger code is assuming that there will only be ONE row in INSERTED

i.e. :

Select @callid=Callid FROM inserted

UPDATE CallLog
Set RepDuration = ...
Where calllog.CallID = @CallID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-04 : 15:38:11

http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-04 : 16:27:10

Thanks a lot guys.
In this case i think it's fine because the application is updating only one row at at a time and only ever updates one row at a time

Or did i not get it?

Kristen
Test

22859 Posts

Posted - 2010-08-04 : 16:30:53

No, you get it, but sooner or later someone / somehow multiple records will be updated.

At the least put something like:


IF (SELECT COUNT(*) FROM Inserted) <> 1
BEGIN
   RAISERROR(...
   ROLLBACK ...
   RETURN -1 ...
   ...
END

so that it doesn't silently only process one random record when someone, someday, does process multiple records.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-04 : 16:38:41

quote:
Originally posted by icw

In this case i think it's fine because the application is updating only one row at at a time and only ever updates one row at a time

That's no excuse for not programming it correctly. You've got to protect the data against all scenarios as you can't rely on the developer to not make a mistake.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-04 : 17:12:28

OK - I know when I'm being told off!

Problem is - I don't know how to do the "raiserror" thing. In fact I didn't even know about it
See below I've tried it but obviously got the syntax wrong.
Could you point out what i got wrong?
Thanks a lot

================================

USE [DB1]
GO
/****** Object: Trigger [dbo].[DURATION_UPDATE] Script Date: 08/04/2010 18:46:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <ICW>
-- Create date: <04/08/10>
-- Description: <Trigger to update the Duration field in calllog with the duration of a fault>
-- =============================================
ALTER TRIGGER [dbo].[DURATION_UPDATE] ON [dbo].[Detail]
FOR INSERT, UPDATE

AS

IF (SELECT COUNT(*) FROM Inserted) <> 1
BEGIN
RAISERROR( 'Cannot update more than one row')
ROLLBACK transaction
RETURN

ELSE

Declare @CalliD Varchar(8)
Declare @SwernCode Varchar (30)
Declare @FaultStart Varchar (10)
Declare @FaultEnd Varchar (10)
Declare @faultStartTime varchar (8)
Declare @FaultEndTime varchar (8)
Declare @DetailFS datetime
Declare @DetailFE datetime

Select @callid=Callid FROM inserted WHERE
isdate(faultstart)=1
and isdate(faultend)=1

IF @Callid IS NOT NULL
Begin

Select @DetailFS = Cast([Faultstart]+' '+[Faultstarttime] as datetime)
from detail
Where callid = @Callid

Select @DetailFE = Cast([Faultend]+' '+[Faultendtime] as datetime)
from detail
Where callid = @Callid

Select @Swerncode = SWERNAccServSubCallType
from detail
Where callid = @Callid

Select @FaultStart = Faultstart
from detail
where callid = @CallID

Select @FaultEndTime = FaultEndTime
from detail
where callid = @CallID

Select @FaultEnd = FaultEnd
from detail
where callid = @CallID

Select @FaultStartTime = FaultstartTime
from detail
where callid = @CallID

UPDATE CallLog
set calllog.SwernCode = @Swerncode,
calllog.faultstart = @FaultStart,
calllog.FaultEnd = @FaultEnd,
Calllog.FaultstartTime = @faultstarttime,
Calllog.faultendtime = @faultendtime,
RepDuration = datediff(ss,@DetailFS ,@DetailFE)/60
Where calllog.CallID = @CallID

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-04 : 17:18:24

Please check BOL for RAISERROR as it has many options. We typically use 16,1 WITH LOG but your mileage may vary.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-04 : 17:24:59

Thanks
I have referenced BOL and give the error a 16,1 and it parses correctly, howvere when I try to run the statemnt
It now gives me the following error

Msg 208, Level 16, State 1, Line 11
Invalid object name 'inserted'.

Whereas before it worked fine and recognised the inserted pseudo table no problem.

Kristen
Test

22859 Posts

Posted - 2010-08-05 : 03:26:34

"Whereas before it worked fine and recognised the inserted pseudo table no problem."

Sounds like what you are looking at, thinking its the problem, isn't.

Post the syntax and we'll have a look.

Get your fireproof underware on first !!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources