SQL 2012 Trigger

Question

SQL 2012 Trigger

mtz676

SSCrazy Eights

Points: 9180
More actions
December 12, 2018 at 3:23 am

#381025

If there is a new row in table ABC then it gets inserted into ABCtt as a result of the below code.
This below code is part of a SQLJob which runs every 2 minutes.
INSERT into ABCtt(Servername)
(
select * from ABC
except
select * from ABCtt
)
Trigger:
CREATE TRIGGER [dbo].[ABC_INSERT]
   ON [dbo].[ABCtt]
AFTER INSERT
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @ServerName varchar(50)
     declare @body1 varchar(2000)
   SELECT @ServerName = INSERTED.ServerName
   FROM INSERTED
     SET @body1 = @ServerName+ ' has been added'
EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'abcd.org'
    @subject = 'A New SQLServer has been added',
     @body = @body1
END
My trigger is supposed to send an email only if there is a new record inserted into table ABCtt, but now it is sending out an email every time the SQL job runs inspite of no new rows being added to ABCtt(which means no row is added to even ABC).
////////////////////////////////////////////
Similarly if I delete a row from ABC then it should get deleted from ABCttt as a result of the below code which runs svery 2 minutes.
delete from ABCtt where ServerName =
(
select * from ABCtt
except
select * from ABC
)
Trigger:
CREATE TRIGGER [dbo].[ABC_DELETE]
   ON [dbo].[ABCtt]
FOR DELETE
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @ServerName varchar(50)
     declare @body1 varchar(2000)
   SELECT @ServerName = DELETED.ServerName
   FROM DELETED
     SET @body1 = @ServerName+ ' has been deleted '
EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'abc.org',
    @subject = 'One Server has been deleted',
     @body = @body1
END
My trigger is supposed to send an email only if there is a record deleted from table ABCtt , but now it is sending out an email every time the SQL job runs inspite of no row being deleted from ABCtt(which means no row is deleted from even ABC).
How can I fix this?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

Well two problems, first the triggers run every time statements are run against the tables regardless of how many rows are affected. Second it doesn't look like either will handle multiple rows being processed at the same time.

mtz676 SSCrazy Eights Points: 9180 More actions · Answer 2

Multiple rows will never be inserted at the same time so it is ok for the code to not handle that situation.
Only 1 row will be inserted at any time.
Thanks

mtz676 SSCrazy Eights Points: 9180 More actions · Answer 3

if EXISTS (SELECT 1 from inserted)
if EXISTS (SELECT 1 from deleted)
adding the above two before begin in both add and delete triggers did the job.
Thanks