October 6, 2016 at 7:11 am
I have created this trigger below, this will insert into Department_Audit if there is any update on Department Column no matter of the value.
But, I want it to insert into the audit table only if the Department Column has been changed to something different than the existing value.
For e.g: if department has been changed from "Fire" to "Fire", i don't want it to be inserted into the Audit, but if this has been changed from
"Fire" to "IT" then I want it to be inserted into Audit table and send out email.
CREATE Trigger [dbo].[Test_UpdateTrigger_1] ON [dbo].[Department] FOR UPDATE AS
BEGIN
SET NOCOUNT ON
INSERT into Department_Audit(
[ID]
,[Department]
,
,Actionname,RowType)
SELECT
[ID]
,[Department]
,'Updated','Old'
FROM Deleted
END
Thank you in advance.
October 6, 2016 at 7:22 am
Use the Inserted virtual table to compare the value of Department before and after, and only insert those rows where it differs.
John
October 6, 2016 at 7:26 am
Yes, got that part:
But how do I send out email ?
CREATE Trigger [dbo].[Test_UpdateTrigger_1] ON [dbo].[Department] FOR UPDATE AS
BEGIN
SET NOCOUNT ON
INSERT into Department_Audit(
[ID]
,[Department]
,
,Actionname,RowType)
(
SELECT
D.[ID]
,D.[Department]
,'Updated','Old'
FROM
INSERTED I
JOIN
DELETED D
ON D.[ID] = I.[ID]
AND D.[Department]<> I.[Department]
)
END
October 6, 2016 at 7:37 am
Something like this:
IF EXISTS (
SELECT ID
FROM Inserted i
JOIN Deleted d ON D.ID = I.ID AND D.Department <> I.Department
)
BEGIN
<Your INSERT statement here>
EXEC sp_send_dbmail <mail parameters here>
END
John
October 6, 2016 at 7:51 am
I used this and it worked great, thanks for the help.
CREATE Trigger [dbo].[Test_UpdateTrigger_1] ON [dbo].[Department] FOR UPDATE AS
IF EXISTS (
SELECT i.ID
FROM Inserted i
JOIN Deleted d ON D.ID = I.ID AND D.Department <> I.Department
)
BEGIN
INSERT into Department_Audit(
[ID]
,[Department]
,
,Actionname,RowType)
(
SELECT
D.[ID]
,D.[Department]
,'Updated','Old'
FROM
INSERTED I
JOIN
DELETED D
ON D.[ID] = I.[ID]
AND D.[Department]<> I.[Department]
)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AB',
@recipients = 'A@test.org',
@body = 'Data in AppUser has been changed',
@subject = 'Your records have been changed'
END
October 6, 2016 at 10:34 am
Just in case you want to touch it up a bit:
CREATE Trigger [dbo].[Test_UpdateTrigger_1]
ON [dbo].[Department]
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(Department)
BEGIN
INSERT into Department_Audit (
[ID]
,[Department]
,Actionname,RowType)
(
SELECT
D.[ID]
,D.[Department]
,'Updated','Old'
FROM
INSERTED I
JOIN
DELETED D
ON D.[ID] = I.[ID]
AND (D.[Department] <> I.[Department] OR (D.[Deparment] IS NULL))
);
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AB',
@recipients = 'A@test.org',
@body = 'Data in AppUser has been changed',
@subject = 'Your records have been changed'
END /*IF*/
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 6, 2016 at 10:39 am
Do you really want to send an email from a trigger?
This may work well with one person performing an update, but as the number of users increase, this may very well cause performance issues.
You may want to insert a row into a "queue" table, and set up a job to query the queue and send the emails.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply