Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Mulit row update triggers Expand / Collapse
Author
Message
Posted Monday, July 22, 2002 11:33 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:32 PM
Points: 349, Visits: 50
How can I create an update trigger that can allow for multiple rows being updated at the same time? I recieve this error now:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.




"Keep Your Stick On the Ice" ..Red Green
Post #5621
Posted Monday, July 22, 2002 1:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:08 AM
Points: 31,371, Visits: 15,839
Can you post your code? Basically you have to code your trigger as though you are joining a table, not a single value.

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #38058
Posted Monday, July 22, 2002 1:16 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:32 PM
Points: 349, Visits: 50
Thanks Steve, I had been testing things and finally worked through it, and you were dead on with that. Here is the before and after code in case anyone else may be interested.

Before....didn't work............
..
..
IF (SELECT HospitalID FROM deleted) IN (1098,2440,2481,2770,4165,4427,4603,6184,6564)
BEGIN
INSERT INTO [Nursery_Transactions]
SELECT 'D',
[HospitalID],
[CollageID],
GETDATE()
FROM deleted
END
..
..
After.......did work..........
..
..
INSERT INTO [Nursery_Transactions]
SELECT 'U',
[HospitalID],
[CollageID],
GETDATE()
FROM deleted
WHERE deleted.[HospitalID] IN (1098,2440,2481,2770,4165,4427,4603,6184,6564)




"Keep Your Stick On the Ice" ..Red Green
Post #38059
Posted Tuesday, January 8, 2013 10:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 2, 2014 9:15 PM
Points: 4, Visits: 158
Hi

I am executing this against database i got above error .

Basically using this to track db growth of all database on a server.colud you please suggest anybody on this.


insert all_dbs_file_size(dbs_name,rec_model,dbf_size_in_MB,log_size_in_mb)
select
d.name
,d.recovery_model_desc
,convert(decimal(18,2),(sum(size)*8)/1024.0) as dbf_size_in_mb
,(select (size*8)/1024.0 from sys.sysaltfiles where dbid=saf.dbid and groupid=0) as log_size_in_mb
from sys.sysaltfiles saf
join sys.databases d on saf.dbid=d.database_id
where groupid>0
group by dbid,d.name,d.compatibility_level,d.recovery_model_desc



Regards,
Sreen.
Post #1404545
Posted Wednesday, January 9, 2013 5:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
venki83k (1/8/2013)
Hi

I am executing this against database i got above error .

Basically using this to track db growth of all database on a server.colud you please suggest anybody on this.


insert all_dbs_file_size(dbs_name,rec_model,dbf_size_in_MB,log_size_in_mb)
select
d.name
,d.recovery_model_desc
,convert(decimal(18,2),(sum(size)*8)/1024.0) as dbf_size_in_mb
,(select (size*8)/1024.0 from sys.sysaltfiles where dbid=saf.dbid and groupid=0) as log_size_in_mb
from sys.sysaltfiles saf
join sys.databases d on saf.dbid=d.database_id
where groupid>0
group by dbid,d.name,d.compatibility_level,d.recovery_model_desc



Regards,
Sreen.


Don't hijack threads of other posters, always create a new thread for your request

Anyways, here is the solution for your problem

INSERT	all_dbs_file_size( dbs_name, rec_model, dbf_size_in_MB, log_size_in_mb )
SELECT D.name, D.recovery_model_desc,
CONVERT(DECIMAL(18,2), SUM( CASE WHEN S.groupid = 1 THEN S.size ELSE 0 END ) * 8 / 1024.00 ) AS dbf_size_in_mb,
CONVERT(DECIMAL(18,2), SUM( CASE WHEN S.groupid = 0 THEN S.size ELSE 0 END ) * 8 / 1024.00 ) AS log_size_in_mb
FROM sys.sysaltfiles AS S
INNER JOIN sys.databases AS D ON S.dbid = D.database_id
GROUP BY D.name, D.recovery_model_desc




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1404711
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse