Trigger Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger Problem

Author

Topic

Murray_67
Starting Member

11 Posts

Posted - 2005-08-15 : 13:28:24

UPDATE Sellers
SET Memo = 'Snooker3'

Everytime I try to update table I get this error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Sellers
PK SellerID / InvoiceNum / Amount / Memo
1 | 159 | 12 |
2 | 160 | 69 |
3 | 161 | 75 |
4 | 162 | 36 |

I also have a trigger on this table that declares and set variables to run a stored procedure. I need the stored procedure to run. I noticed that when I took out the declares and set this update worked fine. But I really need those stored procedures to run also.

CREATE TRIGGER [UpdateSellersLogEntry] ON dbo.Sellers
FOR UPDATE
AS
DECLARE UserID nvarchar (20)
SET @UserID = (SELECT UserID FROM INSERTED)

Currently I have took out the store procedure and left just what you see above in bold and I still get that error. Any ideas anyone

Thanks You

Murray

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-15 : 13:38:18

You also have to understand that inserted table may have 1 to many rows....so assign it to a variable will only get you the last value of the result set.

And running stored procs in a trigger is not a very good idea.

post the rest of the DDL and read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Murray_67
Starting Member

11 Posts

Posted - 2005-08-15 : 14:58:09

--1.
CREATE TABLE [dbo].[Sellers] (
[SellerID] [bigint] IDENTITY (1, 1) NOT NULL ,
[InvoiceNum] [bigint] NULL ,
[Amount] [money] NULL ,
[Memo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Sellers] WITH NOCHECK ADD
CONSTRAINT [PK_Sellers] PRIMARY KEY CLUSTERED
(
[SellerID]
) ON [PRIMARY]

CREATE TRIGGER [[UpdateSellersLogEntry] ON [dbo].[Sellers]
FOR UPDATE
AS
DECLARE @UserID nvarchar (20), @SellerID bigint

SET @UserID = (SELECT UserID FROM INSERTED)
SET @SellerID = (SELECT SellerID FROM INSERTED)

BEGIN
INSERT INTO SellersLog (SellerID, InvoiceNum, Amount, Memo,UserID )
SELECT SellerID, InvoiceNum, Amount, @SellerID, @UserID FROM INSERTED
END

CREATE TABLE [dbo].[SellersLog] (
[SellersLogID] [bigint] IDENTITY (1, 1) NOT NULL ,
[SellerID] [bigint] NULL ,
[InvoiceNum] [bigint] NULL ,
[Amount] [money] NULL ,
[Memo] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--2.
Insert Sellers Select '111',12,NULL,'JH'
Insert Sellers Select '112',18,NULL,'LP'
Insert Sellers Select '113',55,NULL,'PO'

--3.
UPDATE Sellers
SET Memo = 'Snooker3'
--After the update the log should also be updated.

--4.
SellersLog
PK SellersLogID / SellerID / InvoiceNum / Amount / Memo /UserID
1 | 1 |111 | 12 | Snooker3 | JH
2 | 2 |112 | 18 | Snooker3 | LP
3 | 3 |113 | 55 | Snooker3 | PO

Murray

Murray_67
Starting Member

11 Posts

Posted - 2005-08-16 : 09:00:48

Problem solved thanks for your help

Murray

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources