Link to home
Start Free TrialLog in
Avatar of hmstechsupport
hmstechsupport

asked on

MSSql Trigger returns a subquery error

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


ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.
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
Avatar of hmstechsupport
hmstechsupport

ASKER

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.
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.
For computed column, try again:

Alter yourtable add tsd_mon as CASE WHEN DATEPART( dw,[TSD_DATE]) = 2 THEN [TSD_MIN] ELSE 0 END
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?
Yes, as well as SQL Server 7.
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.
>>a computed column will really add unnecessary load to keep computing the column.<<
More than TRIGGER?  I doubt it.