Subquery returned more than 1 value in SQL Server 2005 trigger [2]

Question

Well,

I'm creating a job that checks the backup routines and send and e-mail whenever it stops (I hope that will become useful to others).

That's the way I'm doing:

1) I created a query that returns the db_name and the backup_type that had not been backed up within a certain period:

select name as "Nome da Base" , 'L' as Tipo from  sys.sysdatabases a 
where name not in ('master','tempdb','model'))
except
(select b.database_name, b.type from msdb..backupset b
where b.backup_start_date >= DATEADD (hour,-2, GETDATE())
and type='L')
union all
(select name as "Nome da Base", 'D' as Tipo from  sys.sysdatabases a 
where name not in ('tempdb','model'))
except
(select b.database_name, b.type from msdb..backupset b
where b.backup_start_date >= DATEADD (day,-1, GETDATE())
and type='D'

2) Created a table to record the returning rows of the query above:

CREATE TABLE [dbo].[Alerta_Log_Bkp](
    [Nome da Base] [nvarchar](50) NULL,
    [Tipo] [nvarchar](8) NULL,
    [Servidor] [nvarchar](10) NULL,
    [Hora Verificação] [datetime] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Alerta_Log_Bkp] ADD  CONSTRAINT [DF_Alerta_Log_Bkp_Servidor]  
  DEFAULT (N'Server_NAME') FOR [Servidor]
GO

ALTER TABLE [dbo].[Alerta_Log_Bkp] ADD  CONSTRAINT [DF_Alerta_Log_Bkp_Último Backup]  
  DEFAULT (getdate()) FOR [Hora Verificação]
GO

3) Created a job with an Insert Select:

INSERT INTO [dbo].[Alerta_Log_Bkp]
           ([Nome da Base],[Tipo])
(select name as "Nome da Base" , 'L' as Tipo from  sys.sysdatabases a 
where name not in ('master','tempdb','model','BA_CMA_OM'))
except
(select b.database_name, b.type from msdb..backupset b
where b.backup_start_date >= DATEADD (hour,-2, GETDATE())
and type='L')
union all
(select name as "Nome da Base", 'D' as Tipo from  sys.sysdatabases a 
where name not in ('tempdb','model'))
except
(select b.database_name, b.type from msdb..backupset b
where b.backup_start_date >= DATEADD (day,-1, GETDATE())
and type='D')
GO

--> OK, till this point everything is fine.

My problem lies on the creation of a trigger on the "Alerta_Log_Bkp" table that fires an e-mail when a line is inserted on it. If I test the insert with only 1 row, everything works fine. But, if the insert has more than 1 row, I get the following error: "Subquery returned more than 1 value. This is not permitted when the subquery "

I know that's happening because I'm setting a variable as a select from inserted. But I cannot figure another way out.

Here's the trigger:

Create TRIGGER [dbo].[TR_Alert_mail_BKP] ON [dbo].[Alerta_Log_Bkp] AFTER INSERT AS

DECLARE @base varchar(50)
DECLARE @tipo varchar(50)
DECLARE @servidor varchar(50)
DECLARE @last_bkp DATETIME


SET @base  = (SELECT "Nome da Base" FROM inserted)
SET @tipo = (SELECT tipo FROM inserted)
SET @servidor = (SELECT Servidor FROM inserted)

create table #temp (base varchar(50),
                    tipo varchar(50),
                    servidor varchar(50)) 
insert into #temp values (@base,@tipo,@servidor)

SET @last_bkp = (Select MAX(backup_start_date) from msdb..backupset
                Where database_name = (select base from #temp)
                            and type = (select tipo from #temp))

--SET @last_bkp = (Select MAX(backup_start_date) from msdb..backupset
--              Where database_name = @base and type = @tipo)

IF @tipo in ('D','L')
BEGIN
DECLARE @msg varchar(8000)
SET @msg = 'The "' +@tipo + '"  from database "' + @base + '", of the server "' 
    + @servidor + '", stoped at ' +  CAST(@last_bkp as varchar(50)) +''
EXEC msdb.dbo.sp_send_dbmail 
  @recipients=N'my_mail@domain.com', 
  @body= @msg, 
  @subject = 'Problemas no Backup'  , 
  @profile_name = 'PROFILE'
END

Does anyone have a clue of another way to write that trigger, so I won't get the subquery error?

Thanks, Fabrício

Answer 1

Here is my suggestion. Drop the trigger. Add a column to the Alerta_Log_Bkp table:

ALTER TABLE dbo.Alerta_Log_Bkp ADD Alerted BIT NOT NULL DEFAULT 0;
UPDATE dbo.Alerta_Log_Bkp SET Alerted = 1; -- so that all past entries are marked as alerted

Create the following stored procedure:

CREATE PROCEDURE dbo.AlertOnBackupHistoryRuleViolation
AS
BEGIN
    INSERT INTO [dbo].[Alerta_Log_Bkp]([Nome da Base],[Tipo])
    SELECT name, t = 'L' FROM sys.sysdatabases AS a 
      WHERE name NOT IN ('master','tempdb','model','BA_CMA_OM')
      EXCEPT SELECT b.database_name, b.type from msdb..backupset AS b
        WHERE b.backup_start_date >= DATEADD(HOUR, -2, CURRENT_TIMESTAMP) AND type = 'L'
    UNION ALL
    SELECT name, 'D' FROM sys.sysdatabases AS a 
      WHERE name NOT IN ('tempdb', 'model')
      EXCEPT SELECT b.database_name, b.type FROM msdb..backupset AS b
        WHERE b.backup_start_date >= DATEADD (day,-1, CURRENT_TIMESTAMP) AND type='D';

    IF @@ROWCOUNT > 0
    BEGIN
        DECLARE @body NVARCHAR(MAX); SET @body = N'';

        SELECT @body = @body + CHAR(13) + CHAR(10) 
            + 'The "' + a.Tipo + '"  from database "' + a.[Nome da Base] 
            + '", of the server "' + a.Servidor + '", stopped at ' 
            + COALESCE(CONVERT(VARCHAR(32), MAX(b.backup_start_date)), 'never')
        FROM dbo.Alerta_Log_Bkp AS a LEFT OUTER JOIN msdb.dbo.backupset AS b
        ON a.[Nome da Base] = b.database_name AND a.Tipo = b.[type] WHERE a.Alerted = 0
        GROUP BY a.Tipo, a.[Nome da Base], a.Servidor;

        EXEC msdb.dbo.sp_send_dbmail
             @recipients   = N'my_mail@domain.com', 
             @body         = @body, 
             @subject      = N'Problemas no Backup'  , 
             @profile_name = N'PROFILE';

        UPDATE dbo.Alerta_Log_Bkp SET Alerted = 1 WHERE Alerted = 0;
    END    
END
GO

Now call the stored procedure from the job. No trigger, no muss, no fuss. And just one single e-mail even if there are 40 databases that violate the rule.

Answer 2

Anytime you write a query setting a value of a query from inserted or deleted to a scalar variable, the trigger is wrong and needs to be rewritten. Triggers operate on sets of data not one row at a time. So you need to use set-based logic. Do you want to send an email for every record inserted or every batch?

Further it is not a good idea to send emails from a trigger. It is better to send the data to another table and then have a job check that table at intervals and send the emails.

to help you thinking about set-based convert this

insert into #temp 
values (@base,@tipo,@servidor) 

to

insert into #temp 
Select  "Nome da Base", tipo,servidor from inserted