Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSSql Trigger returns a subquery error

Posted on 2010-09-03
12
Medium Priority
?
459 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 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 1000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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