CODEWITHSUNDEEP
sqlsql-serversql-server-2008triggers
syedcic
syedcic

Reputation: 307

SQL Server Trigger to send email on insert with conditions and using table values

I have made a trigger to run upon insert of record, which should first check the current time and if it is beyond a particular hour and minute, it should fire the trigger and should take data from a table and use those values in the subject and body of the email.

Below is the trigger which is working fine but i need to add time condition and I tried to query a table based on the record inserted into the table but I am not getting the desired email.

As you can see that I tried to use a table value to be sent in the subject but I get the email with SQL Server Message in the subject.

Another thing I want to add is to query the table and check if the INSERTED.USERID is available in the table or not, if it is not available, then no need to fire the trigger.

USE [Attendance]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[after_insert_ATT_LOG] ON [dbo].[ATT_LOG]
FOR INSERT
AS
BEGIN TRY

DECLARE @PositionCode NVARCHAR(5), @FullNameE NVARCHAR(45), @FullNameE2 NVARCHAR(40), @Email NVARCHAR(50), @EDescription NVARCHAR(100), @ReportingToShortNo NVARCHAR(5), @ReportingtoFullNameE NVARCHAR(45), @ReportingEmail NVARCHAR(50)

Select  @PositionCode=ED.POsitionCode, @FullNameE=EP.FullNameE,@FullNameE2=ED.FullNameE, @Email=ED.Email,  @EDescription=EP.EDescription, @ReportingToShortNo=EP.ReportingToShortNo, @ReportingtoFullNameE=EP.ReportingtoFullNameE, @ReportingEmail=(Select Email
FROM HRSystem.dbo.employeedetails WHERE PositionCode= EP.ReportingToShortNo) FROM HRSystem.dbo.employeedetails ED, HRSystem.dbo.Position EP WHERE ED.PositionID = EP.PositionID AND ED.PositionCode = (Select INSERTED.USERID FROM INSERTED) COLLATE DATABASE_DEFAULT;

EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@profile_name = 'Alert',
@subject = @FullNameE,
@body = 'Test Alert';

END TRY 
BEGIN CATCH

DECLARE @dummy int
SET @dummy = 1
END CATCH

Upvotes: 1

Views: 9698

Answers (1)

t1t1an0
t1t1an0

Reputation: 281

as for your second concern

Another thing I want to add is to query the table and check if the INSERTED.USERID is available in the table or not, if it is not available, then no need to fire the trigger.

a FOR INSERT trigger fires right after a row is inserted. meaning, the USERID you wanna look for is sure to be in table ATT_LOG.

and as for your first issue. please try the t-sql code below:

CREATE TRIGGER [dbo].[after_insert_ATT_LOG] ON [dbo].[ATT_LOG]
FOR INSERT
AS
BEGIN TRY

DECLARE @PositionCode NVARCHAR(5), @FullNameE NVARCHAR(45), @FullNameE2 NVARCHAR(40), @Email NVARCHAR(50), @EDescription NVARCHAR(100), @ReportingToShortNo NVARCHAR(5), @ReportingtoFullNameE NVARCHAR(45), @ReportingEmail NVARCHAR(50)

Select @PositionCode=ED.POsitionCode, @FullNameE=EP.FullNameE,@FullNameE2=ED.FullNameE
  , @Email=ED.Email,  @EDescription=EP.EDescription
  , @ReportingToShortNo=EP.ReportingToShortNo
  , @ReportingtoFullNameE=EP.ReportingtoFullNameE
  , @ReportingEmail=
      (Select Email
      FROM HRSystem.dbo.employeedetails
      WHERE PositionCode= EP.ReportingToShortNo)
FROM HRSystem.dbo.employeedetails ED, HRSystem.dbo.Position EP
WHERE ED.PositionID = EP.PositionID
  AND ED.PositionCode =
    (Select INSERTED.PositionCode FROM INSERTED) COLLATE DATABASE_DEFAULT;

EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@profile_name = 'Alert',
@subject = @FullNameE,
@body = 'Test Alert';

END TRY 
BEGIN CATCH

DECLARE @dummy int
SET @dummy = 1
END CATCH

Upvotes: 2

Related Questions