July 22, 2002 at 11:33 am
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
July 22, 2002 at 1:11 pm
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
July 22, 2002 at 1:16 pm
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
January 8, 2013 at 10:26 pm
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.
January 9, 2013 at 5:31 am
venki83k (1/8/2013)
HiI 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
INSERTall_dbs_file_size( dbs_name, rec_model, dbf_size_in_MB, log_size_in_mb )
SELECTD.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
FROMsys.sysaltfiles AS S
INNER JOIN sys.databases AS D ON S.dbid = D.database_id
GROUP BY D.name, D.recovery_model_desc
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply