SQLTeam.com | Weblogs | Forums

Trigger to send email?

Hello,

I hope everyone is staying safe.

I'd appreciate some thoughts on the following; my company want to load data into a table via an excel file driven by VBA......that bit's nice and easy, we do lots of that kind of stuff.

What they would like to happen is if a record is loaded into a table where the value in one of the columns is greater than $250, an email should be auto-generated to a specific individual. There may be a dozen of these individuals, who gets the email is dependent on another column value in the record.

Is this even remotely possible? I'm thinking maybe some sort of fancy trigger on a table?

Many thanks for reading and for any input.

Putting that kind of business rule in a trigger seems a bit out of place imo.

For example lets say it reached 300, then you trigger an email that gets queued, but while that is waiting to process, the value drops to 175. Do you still want the email sent?

1 Like

Once the value is in the table, it won't change.

so a few options

which are you more comfortable with

1 Like

Yes, this is rather easy to implement in a trigger.

Either the trigger itself would send the email(s), or you could have a job that sends the email(s).

If you'd like sample code for the trigger, just let me know. Be sure to post DDL (CREATE TABLE) for the table involved.

1 Like

Trigger Disadvantages

With all the mentioned advantages of the SQL Server triggers, the triggers increase the complexity of the database. If the trigger is badly designed or overused, it will lead to major performance issues, such as blocked sessions, due to extending the life of the transaction for longer time, extra overhead on the system due to executing it each time an INSERT, UPDATE or DELETE action is performed or it may lead to data loss issues. Also, it is not easy to view and trace the database triggers, especially if there is no documentation about it as it is invisible to developers and the applications.

Trigger Alternatives … Enforce Integrity

If it is found that the triggers are harming the performance of your SQL Server instance, you have to replace them with other solutions. For example, rather than using the triggers to enforce the entity integrity, it should be enforced at the lowest level by using the PRIMARY KEY and UNIQUE constraints. The same is applied to the domain integrity that should be enforced through CHECK constraints, and the referential integrity that should be enforced through the FOREIGN KEY constraints. You can use the DML triggers only if the features supported by a specific constraint cannot meet your application requirements.

Trigger Alternatives … Auditing

As we mentioned previously, the triggers can be also used to audit and track the changes performed on a specific table. If this auditing method causes a performance degradation in your SQL Server instance, you can easily replace it with the OUTPUT clause. The OUTPUT clause returns information about each row affected by the INSERT, UPDATE or DELETE operation, in the shape of a confirmation message or a value that can be inserted into the historical table. The OUTPUT clause method provides us also with more control on the executed code, as it will be added to the data insertion, modification or deletion statement itself whenever you want, opposite to the trigger that will be always executed.

1 Like

Sample code would be fantastic. Here's the table:

CREATE TABLE [dbo].[WorkOut](
    [WorkOutID] [bigint] IDENTITY(1,1) NOT NULL,
    [TimeSheetDate] [datetime] NOT NULL,
    [DateOut] [datetime] NOT NULL,
    [EmployeeID] [int] NOT NULL,
    [IsMainWorkPlace] [bit] NOT NULL,
    [DepartmentUID] [uniqueidentifier] NOT NULL,
    [WorkPlaceUID] [uniqueidentifier] NULL,
    [TeamUID] [uniqueidentifier] NULL,
    [WorkShiftCD] [nvarchar](10) NULL,
    [WorkHours] [real] NULL,
    [AbsenceCode] [varchar](25) NULL,
    [PaymentType] [char](2) NULL,
    [CategoryID] [int] NULL,
    [Year]  AS (datepart(year,[TimeSheetDate])),
 CONSTRAINT [PK_WorkOut] PRIMARY KEY CLUSTERED 
(
    [WorkOutID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[WorkOut] ADD  
CONSTRAINT [DF__WorkOut__IsMainW__2C1E8537]  DEFAULT ((1)) FOR [IsMainWorkPlace]

ALTER TABLE [dbo].[WorkOut]  WITH CHECK ADD  CONSTRAINT [FK_WorkOut_Employee_EmployeeID] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])

ALTER TABLE [dbo].[WorkOut] CHECK CONSTRAINT [FK_WorkOut_Employee_EmployeeID]

WorkHours is the column I need to monitor, as soon as any record is loaded into the table where this value is greater than 250 that's when the trigger needs to kick in.

That can be said for anything you implement in SQL without proper vetting

This is the general idea. I didn't have full details but you should be able to flesh those out.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER dbo.WorkOut__TR_INS
ON dbo.WorkOut
AFTER INSERT
AS
SET NOCOUNT ON;
IF EXISTS(SELECT 1 FROM inserted WHERE WorkHours > 250)
BEGIN
    DECLARE @body nvarchar(max)
    DECLARE @subject nvarchar(255)
    DECLARE @recipients varchar(8000)
    DECLARE @sql_to_send_emails nvarchar(max)
    SELECT @sql_to_send_emails = STUFF((
        SELECT '; EXEC msdb.dbo.sp_send_dbmail @subject = ''Work Hours Exceed 250'', ' +
            '@recipients = ''?@?'', ' +
            '@body = ''Employee Id ' + CAST(i.EmployeeID AS varchar(10)) + ' exceeded 250 work hours.''' 
        FROM inserted i
        /*join to other table to get recipients for email!?*/
        WHERE i.WorkHours > 250
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'),
        1, 2, '')
    /*for testing ONLY*/
    PRINT @sql_to_send_emails
    EXEC(@sql_to_send_emails)
END /*IF*/
/*end of trigger*/
GO
1 Like

Or you can use a method that shows you know something about SQL Server, such as Change Tracking or CDC.

hi Scott

Not sure What you had in mind !! with this comment

" ... that shows you know something about SQL Server .."

Please let me know whats on your mind
I am very much interested in what your thoughts behin that comment are

That the OUTPUT clause has nothing to do with "auditing" and could never be used for true auditing.
OUTPUT can't be used as a source in a trigger. Instead, the developer of the SQL has to use the OUTPUT clause to capture the data and then write it. But that is logging, not auditing. By definition, auditing means that the developer has no direct control over it, can't stop it / not do it no matter what they do.