Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSSql Trigger returns a subquery error

Posted on 2010-09-03
12
Medium Priority
?
461 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

971 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