Solved

MSSql Trigger returns a subquery error

Posted on 2010-09-03
12
451 Views
Last Modified: 2012-06-27
I have the following trigger below which works great in the case of an insert or a single row update but returns the following error

Msg 512, Level 16, State 1, Procedure UPDATE_TSD_DOW, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


when I execute a SQL statement such as "update tsdetail set tsd_min=tsd_min*1

I realize it has to do with the fact that I am trying to update the same row that the trigger is based on but that is what I need.  I also do not think I can use an INSTEAD OF trigger since I won't know what other columns are in the table and my understanding is that the INSTEAD OF replaces the INSERT so I would need to know the columns being inserted.

Is there anoher way to do this in MSSql?  Oracle uses something like ":NEW.TSD_WED :" which you can set in a BEFORE INSERT, UPDATE trigger.  Can I do something like this in MSSql?



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[UPDATE_TSD_DOW] ON [dbo].[TSDETAIL]
AFTER INSERT,UPDATE
AS
SET NOCOUNT ON
DECLARE @v_dow CHAR(3)
DECLARE @TSD_DATE SMALLDATETIME
DECLARE @TSD_KEY NUMERIC(11)

BEGIN
    SET @TSD_DATE=(SELECT CAST(inserted.TSD_DATE AS SMALLDATETIME) FROM inserted)
    SET @TSD_KEY=(SELECT TSD_KEY FROM inserted)
    SET @v_dow = LEFT(UPPER(DATENAME(weekday, @TSD_DATE)),3)

    IF @v_dow = 'MON'
      UPDATE [dbo].[TSDETAIL] SET TSD_MON = [dbo].TSDETAIL.TSD_MIN,
      TSD_TUE = 0,TSD_WED = 0,TSD_THU = 0,TSD_FRI = 0,TSD_SAT = 0,TSD_SUN = 0
      WHERE [dbo].[TSDETAIL].TSD_KEY = @TSD_KEY

    IF @v_dow = 'TUE'
      UPDATE [dbo].[TSDETAIL] SET TSD_TUE = [dbo].TSDETAIL.TSD_MIN,
      TSD_MON = 0,TSD_WED = 0,TSD_THU = 0,TSD_FRI = 0,TSD_SAT = 0,TSD_SUN = 0
      WHERE [dbo].[TSDETAIL].TSD_KEY = @TSD_KEY

    IF @v_dow = 'WED'
      UPDATE [dbo].[TSDETAIL] SET TSD_WED = [dbo].TSDETAIL.TSD_MIN,
      TSD_MON = 0,TSD_TUE = 0,TSD_THU = 0,TSD_FRI = 0,TSD_SAT = 0,TSD_SUN = 0
      WHERE [dbo].[TSDETAIL].TSD_KEY = @TSD_KEY

    IF @v_dow = 'THU'
      UPDATE [dbo].[TSDETAIL] SET TSD_THU = [dbo].TSDETAIL.TSD_MIN,
      TSD_MON = 0,TSD_TUE = 0,TSD_WED = 0,TSD_FRI = 0,TSD_SAT = 0,TSD_SUN = 0
      WHERE [dbo].[TSDETAIL].TSD_KEY = @TSD_KEY

    IF @v_dow = 'FRI'
      UPDATE [dbo].[TSDETAIL] SET TSD_FRI = [dbo].TSDETAIL.TSD_MIN,
      TSD_MON = 0,TSD_TUE = 0,TSD_WED = 0,TSD_THU = 0,TSD_SAT = 0,TSD_SUN = 0
      WHERE [dbo].[TSDETAIL].TSD_KEY = @TSD_KEY

    IF @v_dow = 'SAT'
      UPDATE [dbo].[TSDETAIL] SET TSD_SAT = [dbo].TSDETAIL.TSD_MIN,
      TSD_MON = 0,TSD_TUE = 0,TSD_WED = 0,TSD_THU = 0,TSD_FRI = 0,TSD_SUN = 0
      WHERE [dbo].[TSDETAIL].TSD_KEY = @TSD_KEY

    IF @v_dow = 'SUN'
      UPDATE [dbo].[TSDETAIL] SET TSD_SUN = [dbo].TSDETAIL.TSD_MIN,
      TSD_MON = 0,TSD_TUE = 0,TSD_WED = 0,TSD_THU = 0,TSD_FRI = 0,TSD_SAT = 0
      WHERE [dbo].[TSDETAIL].TSD_KEY = @TSD_KEY
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


0
Comment
Question by:hmstechsupport
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 33596939
ms sql server does not have a "for each row" version...
you have to change the logic to something like this:
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



ALTER TRIGGER [dbo].[UPDATE_TSD_DOW] ON [dbo].[TSDETAIL]

AFTER INSERT,UPDATE

AS

SET NOCOUNT ON



BEGIN

      UPDATE d

          SET TSD_MON = CASE WHEN LEFT(UPPER(DATENAME(weekday, i.TSD_DATE)),3) = 'MON' THEN [dbo].TSDETAIL.TSD_MIN ELSE 0 END

            , TSD_TUE = CASE WHEN LEFT(UPPER(DATENAME(weekday, i.TSD_DATE)),3) = 'TUE' THEN [dbo].TSDETAIL.TSD_MIN ELSE 0 END

            , TSD_WED = CASE WHEN LEFT(UPPER(DATENAME(weekday, i.TSD_DATE)),3) = 'WED' THEN [dbo].TSDETAIL.TSD_MIN ELSE 0 END

            --- etc for the other fields ---

       FROM INSERTED i

       JOIN [dbo].[TSDETAIL] d

         ON d.TSD_KEY = i.TSD_KEY







END

GO



SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

Open in new window

0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 250 total points
ID: 33597008
Since you are updating the same record again by TSD_KEY, you may get into a recursive loop updating itself and triggering.
You need a WHERE condition to stop when no changes need to be made.

Tweak to angelIII's code
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



ALTER TRIGGER [dbo].[UPDATE_TSD_DOW] ON [dbo].[TSDETAIL]

AFTER INSERT,UPDATE

AS

SET NOCOUNT ON



BEGIN

      UPDATE d

          SET TSD_MON = CASE WHEN LEFT(UPPER(DATENAME(weekday, i.TSD_DATE)),3) = 'MON' THEN [dbo].TSDETAIL.TSD_MIN ELSE 0 END

            , TSD_TUE = CASE WHEN LEFT(UPPER(DATENAME(weekday, i.TSD_DATE)),3) = 'TUE' THEN [dbo].TSDETAIL.TSD_MIN ELSE 0 END

            , TSD_WED = CASE WHEN LEFT(UPPER(DATENAME(weekday, i.TSD_DATE)),3) = 'WED' THEN [dbo].TSDETAIL.TSD_MIN ELSE 0 END

            --- etc for the other fields ---

       FROM INSERTED i

       JOIN [dbo].[TSDETAIL] d

         ON d.TSD_KEY = i.TSD_KEY

       WHERE TSD_MON <> CASE WHEN LEFT(UPPER(DATENAME(weekday, i.TSD_DATE)),3) = 'MON' THEN [dbo].TSDETAIL.TSD_MIN ELSE 0 END

            OR TSD_TUE <> CASE WHEN LEFT(UPPER(DATENAME(weekday, i.TSD_DATE)),3) = 'TUE' THEN [dbo].TSDETAIL.TSD_MIN ELSE 0 END

            --- etc for the other fields ---

       OR TSD_MON IS NULL OR TSD_TUE IS NULL --- etc for the other fields ---



END

GO



SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
ID: 33597236
I agree with both suggestions for your trigger.  But it looks like you forgo the trigger altogether and use computed columns instead.

Alter yourtable drop column tsd_mon
go
Alter yourtable add tsd_mon as CASE WHEN LEFT(UPPER(DATENAME(weekday, i.TSD_DATE)),3) = 'MON' THEN [dbo].TSDETAIL.TSD_MIN ELSE 0 END
go
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33597284
dmdq

It all depends on read/write ratio.
Trigger favours read-heavy
Computed favours infrequent reads *

* That is unless you can index the computed column - in which case why not just maintain the columns in a trigger.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33597288
oops...just noticed a typo, should be:

Alter yourtable add tsd_mon as CASE WHEN LEFT(UPPER(DATENAME(weekday, [dbo].TSD_DATE)),3) = 'MON' THEN [dbo].TSDETAIL.TSD_MIN ELSE 0 END
go


Also, whichever way you go, I think this is an improvement:

-- 2 equates to mon, 3, wed, etc
CASE WHEN DATEPART( dw,[dbo].TSD_DATE) = 2 THEN [dbo].TSDETAIL.TSD_MIN ELSE 0 END
0
 

Author Comment

by:hmstechsupport
ID: 33597599
Thanks Guys

The first and second give me these errors.  Not sure why or what it's saying
Msg 4104, Level 16, State 1, Procedure UPDATE_TSD_DOW1, Line 8
The multi-part identifier "dbo.TSDETAIL.TSD_MIN" could not be bound.
Msg 4104, Level 16, State 1, Procedure UPDATE_TSD_DOW1, Line 8
The multi-part identifier "dbo.TSDETAIL.TSD_MIN" could not be bound.
Msg 4104, Level 16, State 1, Procedure UPDATE_TSD_DOW1, Line 8
The multi-part identifier "dbo.TSDETAIL.TSD_MIN" could not be bound.
Msg 4104, Level 16, State 1, Procedure UPDATE_TSD_DOW1, Line 8
The multi-part identifier "dbo.TSDETAIL.TSD_MIN" could not be bound.
Msg 4104, Level 16, State 1, Procedure UPDATE_TSD_DOW1, Line 8
The multi-part identifier "dbo.TSDETAIL.TSD_MIN" could not be bound.
Msg 4104, Level 16, State 1, Procedure UPDATE_TSD_DOW1, Line 8
The multi-part identifier "dbo.TSDETAIL.TSD_MIN" could not be bound.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:hmstechsupport
ID: 33597609
The 3rd option as  a computed field gives me the error

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "i.TSD_DATE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.TSDETAIL.TSD_MIN" could not be bound.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33597774
For computed column, try again:

Alter yourtable add tsd_mon as CASE WHEN DATEPART( dw,[TSD_DATE]) = 2 THEN [TSD_MIN] ELSE 0 END
0
 

Author Comment

by:hmstechsupport
ID: 33598194
Ok.  That worked for me.  
Alter table tsdetail  add tsd_mon1 as CASE WHEN DATEPART( dw,[TSD_DATE]) = 2 THEN [TSD_MIN] ELSE 0 END

What version of SQL will this work under ...2005, 2008, what about 2000?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33601324
Yes, as well as SQL Server 7.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33601887
If this is read intensive, like a schedule table where you have a custom job agent polling - a computed column will really add unnecessary load to keep computing the column.  Consider creating an index on it to store the computed result.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33603554
>>a computed column will really add unnecessary load to keep computing the column.<<
More than TRIGGER?  I doubt it.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now