We help IT Professionals succeed at work.

MSSql Trigger returns a subquery error

474 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


Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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.

Commented:
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

Author

Commented:
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.

Author

Commented:
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.

Commented:
For computed column, try again:

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

Author

Commented:
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?
CERTIFIED EXPERT
Top Expert 2012

Commented:
Yes, as well as SQL Server 7.
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>a computed column will really add unnecessary load to keep computing the column.<<
More than TRIGGER?  I doubt it.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.