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?
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?
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.
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.
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
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.