SQL Insert when Inserted but Skip Duplicate

Asked

Viewed 34 times

1

    USE [RF_World]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER dbo.UserStatusUpdater
ON dbo.tbl_base
AFTER INSERT AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Just INSERT everything from the [inserted] pseudotable into
--the target table
INSERT INTO [BILLING].dbo.tbl_UserStatus
(id, Status, DTStartPrem, DTEndPrem, Cash, Bonus)
SELECT Account, '1', '2019-08-01 13:04:12.400','2019-08-01 13:04:12.400', '1','0'
FROM inserted;

END
GO

I created this code to add lines in tbl user Status when something is inserted in tbl base in another DB, however, this is a problem because it tries to duplicate the wrong one, and the system crashes, someone can help me to make this code ignore duplicate?

sql sql-server

  • Not evaluating whether a trigger is the best solution to your problem, which it doesn’t seem to be, in a BEFORE trigger you could check if it already exists in the table before including.

1 answer

0

You can include a clause where validating whether it already exists, for example with not exists:

INSERT INTO [BILLING].dbo.tbl_UserStatus
(id, Status, DTStartPrem, DTEndPrem, Cash, Bonus)
SELECT Account, '1', '2019-08-01 13:04:12.400','2019-08-01 13:04:12.400', '1','0'
FROM inserted
WHERE NOT EXISTS (SELECT * 
                    FROM [BILLING].dbo.tbl_UserStatus US
                   WHERE US.id = inserted.id
                 )

If id is not the Primary key, can add other fields in where to ensure that registration does not exist.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.