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 2005 Forums

Transact-SQL (2005)

Trigger problem

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-23 : 09:17:53

[code]
Create Trigger [dbo].TR_UPDATE_GROUPMASTER ON GROUPMASTER
For Update
AS
Declare @LOC_ID NUMERIC(20,0),
@STATUS Numeric(20,0),
@GROUP_ID Numeric(20,0)
BEGIN
set @STATUS = (SELECT STATUS FROM Inserted);
set @GROUP_ID = (SELECT GROUP_ID FROM Deleted);
DECLARE Locations CURSOR STATIC for SELECT DISTINCT LOCATION_ID FROM LOCATIONMASTER WHERE GROUP_ID = @GROUP_ID
OPEN Locations
FETCH NEXT FROM Locations INTO @LOC_ID
WHILE @@FETCH_Status = 0
BEGIN

IF (@STATUS = 1)
BEGIN
UPDATE EPRESCRIBECONSDETAILS SET REGISTRATION_STATUS = 1 WHERE LOCATION_ID =@LOC_ID
UPDATE INTERFACECONNECTIONS SET CONNECTION_STATUS = 1 WHERE GROUP_ID = @GROUP_ID
END
ELSE
BEGIN
UPDATE EPRESCRIBECONSDETAILS SET REGISTRATION_STATUS = 3 WHERE LOCATION_ID = @LOC_ID
UPDATE INTERFACECONNECTIONS SET CONNECTION_STATUS = 3 WHERE GROUP_ID= @GROUP_ID
END
FETCH NEXT FROM Locations INTO @LOC_ID
END
CLOSE Locations
DEALLOCATE Locations
END
GO

[/code]

when i give query
update groupmaster set status=-1 where group_id>1502

exception like this

Msg 512, Level 16, State 1, Procedure TR_UPDATE_GROUPMASTER, Line 8
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

i know that = is failing for
set @STATUS = (SELECT STATUS FROM Inserted);

wat else needs to be changed for trigger to work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 09:22:19

you're assuming that inserted and deleted tables will have only one row always which is not true. thats why the error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 09:29:57

Actually you want this


Create Trigger [dbo].TR_UPDATE_GROUPMASTER ON GROUPMASTER
For Update
AS
BEGIN  
UPDATE e
SET e.REGISTRATION_STATUS = CASE WHEN i.STATUS = 1 THEN 1 ELSE  3 END
FROM EPRESCRIBECONSDETAILS  e
JOIN Deleted d
ON d.PK= l.PK
JOIN INSERTED i
ON i.PK=d.PK
JOIN LOCATIONMASTER l
ON l.GROUP_ID = d.GROUP_ID 
UPDATE e
SET CONNECTION_STATUS = CASE WHEN i.STATUS = 1 THEN 1 ELSE 3 END
FROM INTERFACECONNECTIONS  e
JOIN Deleted d
ON d.PK= l.PK
JOIN INSERTED i
ON i.PK=d.PK
END
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-23 : 13:01:49

ok Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 14:00:53

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-08-24 : 01:50:20

Visakh, for pk what needs to be placed in my trigger like its a bit of confusing for me.can you clarify in this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:43:52

pk means your primary key of table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources