trigger to send an email for duplicate records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

trigger to send an email for duplicate records

Author

Topic

raaj
Posting Yak Master

129 Posts

Posted - 2014-12-02 : 22:16:13

Hi,

Recently I got a task to find for the duplicate records on a table.
And I have written the below query and it gave correct result set :

Select Employee_Code,COUNT(*) from Employee
Group by Employee_Code
HAVING COUNT(*) > 1

Now they have come back to me and asked if they can get an email whenever there is a duplicate entry entered on the Employee table.

I am not sure how to write a trigger for the above process including sending an email to the recipients.

We are using SQL SERVER 2005.

Any ideas?
Is writing a trigger the only solution or is there any other alternative?

Thanks,
Raaj

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-12-03 : 11:07:36

[code]create trigger dbo.tr_iu_Employee_Duplicates on Employee
after insert, update
as
begin
if exists(
select e.Employee_code
from Employee e
inner join
inserted i
on i.Employee_code = e.Employee_code
Group by e.Employee_Code
HAVING COUNT(*) > 1
)
begin
EXEC msdb..sp_send_dbmail -- You'll need to configure this on your server
@profile_name = 'Provide Details',
@recipients = 'Provide Details',
@subject = 'Provide Details',
@body = 'Provide Details' -- Could be a Query, if preferred (Use above EXISTS query?)
end
end[/code]Haven't tried this but it should get you started...
HTH

No amount of belief makes something a fact. -James Randi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources