asked
How to code a Check constraint in a Trigger
I want to incorporate a Check constraint within a trigger, based on the below but im struggling with the coding. Assuming that is an Alphanumeric field you should be checking what value is in the alphanumeric column from inserted, comparing it with whatever is in the AMGR_User_Fields_Tbl to see if there’s a duplicate This is my Trigger: ALTER TRIGGER [dbo].[DUPLICATES] ON [dbo].[AMGR_User_Fields_Tbl] FOR INSERT, UPDATE AS DECLARE @Alphanumericcol VARCHAR (750) -- This trigger has been created to check that duplicate rows are not inserted into table. -- Check if row exists SELECT @Alphanumericcol FROM Inserted i, AMGR_User_Fields_Tbl t WHERE t.AlphaNumericCol = i.AlphaNumericCol AND t.Client_Id = i.Client_Id -- (@Alphanumericcol = 1) -- Display Error and then Rollback transaction BEGIN RAISERROR ('This row already exists in the table', 16, 1) ROLLBACK TRANSACTION END
It shouldn't. What does this do? create table #AMGR_User_Fields_Tbl (AlphaNumericCol varchar(750), Client_id int) go alter table #AMGR_User_Fields_Tbl add constraint uq_temp_AMGR_User_Fields_Tbl unique(AlphaNumericCol, Client_id) go -- we can duplicate the AlphaNumericCol across different clients insert #AMGR_User_Fields_Tbl (AlphaNumericCol, Client_id) values ('potentially-duplicate value', 1), ('potentially-duplicate value', 2) select * from #AMGR_User_Fields_Tbl -- but not within a single client insert #AMGR_User_Fields_Tbl (AlphaNumericCol, Client_id) values ('potentially-duplicate value', 1) /* Msg 2627, Level 14, State 1, Line 11 Violation of UNIQUE KEY constraint 'uq_temp_AMGR_User_Fields_Tbl'. Cannot insert duplicate key in object 'dbo.#AMGR_User_Fields_Tbl'. The duplicate key value is (potentially-duplicate value, 1). The statement has been terminated. */ -- we still have the original two rows. The attempted 3rd row failed select * from #AMGR_User_Fields_Tbl drop table #AMGR_User_Fields_Tbl
Thought I'd pull this all together from the comments... If you are not required to use a trigger then you might consider using a unique constraint to simplify your approach. You can drop the trigger then use an ALTER TABLE statement to add the unique constraint. This will prevent duplicate values from being added to the table. For example, using a temporary table with the two columns in your example: create table #AMGR_User_Fields_Tbl (AlphaNumericCol varchar(750), Client_id int) go alter table #AMGR_User_Fields_Tbl add constraint uq_temp_AMGR_User_Fields_Tbl unique(AlphaNumericCol, Client_id) go -- we can duplicate the AlphaNumericCol across different clients insert #AMGR_User_Fields_Tbl (AlphaNumericCol, Client_id) values ('potentially-duplicate value', 1), ('potentially-duplicate value', 2) select * from #AMGR_User_Fields_Tbl -- but not within a single client insert #AMGR_User_Fields_Tbl (AlphaNumericCol, Client_id) values ('potentially-duplicate value', 1) /* Msg 2627, Level 14, State 1, Line 11 Violation of UNIQUE KEY constraint 'uq_temp_AMGR_User_Fields_Tbl'. Cannot insert duplicate key in object 'dbo.#AMGR_User_Fields_Tbl'. The duplicate key value is (potentially-duplicate value, 1). The statement has been terminated. */ -- we still have the original two rows. The attempted 3rd row failed select * from #AMGR_User_Fields_Tbl drop table #AMGR_User_Fields_Tbl This allows you to insert a single combination of AlphaNumericCol and Client_Id and rejects any attempt to add a duplicate row. Just add the unique constraint to your table then you don't have to write any additional code to prevent duplication.
15 People are following this question.
