SQL Trigger Syntax
I have this trigger: -
Does anyone know how to do this?
CREATE TRIGGER trNAME
ON ApSupplier
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
IF UPDATE (BankAccount)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TEST',
@recipients = "me@work.com",
@body = "TEST",
@subject = "SupplierName +BankAccount"
END
ENDDoes anyone know how to do this?
ASKER
OK, thanks but I get this: -
Incorrect syntax near '.'.
Incorrect syntax near '.'.
<correction>
Change the AFTER UPDATE to FOR UPDATE, then edit based on my suggestion, and execute.
AFTER UPDATE which executes AFTER the row is UPDATEd, and I'm not abundantly sure if the virtual tables inserted and deleted are available, so I'll step back to encourage other experts to respond.
Change the AFTER UPDATE to FOR UPDATE, then edit based on my suggestion, and execute.
AFTER UPDATE which executes AFTER the row is UPDATEd, and I'm not abundantly sure if the virtual tables inserted and deleted are available, so I'll step back to encourage other experts to respond.
If you're sure you'll only be updating 1 and only 1 bankaccount per UPDATE statement, you can do this:
CREATE TRIGGER trNAME
ON ApSupplier
AFTER UPDATE
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF UPDATE (BankAccount)
BEGIN
DECLARE @BankAccount varchar(40)
DECLARE @SupplierName varchar(100)
DECLARE @subject nvarchar(255)
SELECT TOP (1)
@BankAccount = BankAccount, @SupplierName = @SupplierName
FROM inserted
SET @subject = @SupplierName + ' ' + @BankAccount
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TEST',
@recipients = "me@work.com",
@body = "TEST",
@subject = @subject
END
CREATE TRIGGER trNAME
ON ApSupplier
AFTER UPDATE
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF UPDATE (BankAccount)
BEGIN
DECLARE @BankAccount varchar(40)
DECLARE @SupplierName varchar(100)
DECLARE @subject nvarchar(255)
SELECT TOP (1)
@BankAccount = BankAccount, @SupplierName = @SupplierName
FROM inserted
SET @subject = @SupplierName + ' ' + @BankAccount
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TEST',
@recipients = "me@work.com",
@body = "TEST",
@subject = @subject
END
ASKER
Hi Scott,
This works but fires for any change in the Supplier table, is there a way to limit the trigger to fire only if 'BankAccount' field is changed changed?
This works but fires for any change in the Supplier table, is there a way to limit the trigger to fire only if 'BankAccount' field is changed changed?
ASKER
My previous comment is incorrect, if I manually edit the table the solution works. When saved from our System front end any change fires the trigger so the 'Save' button must affect all fields. : (
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Scott,
This nearly works, if I change the Bank Account then I get an email with the details. If I change another field I get a blank email.
This nearly works, if I change the Bank Account then I get an email with the details. If I change another field I get a blank email.
ASKER
I added this and it now works! Thanks very much!!
SET @Text = 'Details: ' + @Supplier + ' ' + @SupplierName + ' ' + @BankAccount
if len(@Text)>0
SET @Text = 'Details: ' + @Supplier + ' ' + @SupplierName + ' ' + @BankAccount
if len(@Text)>0
>I would like the @Subject to be the SupplierName and BankAccount that has been amended.
Update triggers utilize two virtual tables, 'deleted' which is the old row values, and 'inserted' which is the new row values. So in your case, it would go something like this..
Open in new window
* You may have to CAST(inserted.BankAccountC
* The above format works even when these columns where not UPDATEd.