question

JR03 avatar image
JR03 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
triggercheck
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Are required to use a trigger? If not, it might be more straightforward to just add a unique constraint to the columns of interest.
0 Likes 0 ·
I thought of both options, Trigger and Unique constraint. And both send the same result. When a duplicate entry is added, it inserts a NULL or blank in the field as its value. So Im pretty much stumped..
0 Likes 0 ·
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
0 Likes 0 ·
HI Ken This works, but i need to know how i can put this in my code?
0 Likes 0 ·
HI Ken This works, but i need to know how i can put this in my code?
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.