Exec master.xp.sendmail 'EmailID', 'EmailMessage'
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailTest',
@description = 'Sent Mail using MSDB',
@email_address = 'umashankar@queryingsql.com',
@display_name = 'umashankar',
@username='umashankar@queryingsql.com',
@password='password',
@mailserver_name = 'mail.queryingsql.com'
CREATE TRIGGER trig_mail
ON TRANSDETAILS
FOR INSERT, UPDATE
AS
...
There will be no inserted table for deletes so it is a waste of resources to call the trigger when you are deleting data..
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_name = 'MailTest',
@description = 'Sent Mail using MSDB',
@email_address = 'umashankar@queryingsql.com',
@display_name = 'umashankar',
@username='umashankar@queryingsql.com',
@password='password',
@mailserver_name = 'mail.queryingsql.com',
@enable_ssl = 1
If (Select (LocalUnitPrice-LocalCost)/(LocalUnitPrice*100) from inserted ) < 20
toIf (Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20
to get the margin percentage.. @body = @body_msg,
USE ABC
GO
If EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig_mail' AND type = 'TR')
DROP TRIGGER trig_mail
GO
CREATE TRIGGER trig_mail
ON TRANSDETAILS
FOR INSERT, UPDATE
AS
declare @AccountingRef varchar(100)
declare @body_msg varchar(1000)
SELECT @AccountingRef = AccountingRef FROM INSERTED
SELECT @body_msg = 'A product with AccountingRef: ' + @AccountingRef + ' has been sold to a Customer where the margin is less than 20%'
If (Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20
Begin
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'abm123@gmail.com',
@body = @body_msg,
@subject = 'Automated Success Message - Margin Warning' ;
END
GO
If (Select (LocalUnitPrice-LocalCost)*100/LocalUnitPrice from inserted ) < 20
and (Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20
should trigger the alert for the second case, kindly check..
USE ABC
GO
If EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig_mail' AND type = 'TR')
DROP TRIGGER trig_mail
GO
CREATE TRIGGER trig_mail
ON TRANSDETAILS
AFTER INSERT, UPDATE
AS
declare @AccountingRef varchar(100)
declare @body_msg varchar(1000)
SELECT @AccountingRef = AccountingRef FROM INSERTED
SELECT @body_msg = 'A product with AccountingRef: ' + ISNULL(@AccountingRef, '') + ' has been sold to a Customer where the margin is less than 20%'
If (Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20
Begin
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'abm123@gmail.com',
@body = @body_msg,
@subject = 'Automated Success Message - Margin Warning' ;
END
GO
Open in new window