August 11, 2014 at 7:22 am
HI All
I have a trigger that searches for duplicates before inserting values.
I have written the trigger however its not inputting values into the column at all
Can someone please tell me why
Please see my trigger:
ALTER TRIGGER DUPLICATES
ONAMGR_User_Fields_Tbl
AFTERINSERT,
UPDATE
AS
Declare @AlphanumericCol varchar (750)
Declare @Counter integer
Declare @Value integer
set @Counter = (Select top 1 AlphanumericCol from AMGR_User_Fields_Tbl order by AlphanumericCol desc); -- this will bring back the last record
set @Value = @Counter + 1;
IF EXISTS(select AlphanumericCol from AMGR_User_Fields_Tbl where @Value = AlphanumericCol)
BEGIN
RAISERROR('This row already exists in the table', 16, 1)
ROLLBACK TRAN
END;
ELSE
IF NOT EXISTS (SELECT AlphanumericCol FROM AMGR_User_Fields_Tbl WHERE @Value = AlphanumericCol)
BEGIN
INSERT INTO AMGR_User_Fields_Tbl (Client_Id,Contact_number, Type_id, Code_id, Last_code_id, DateCol, NumericCol, AlphanumericCol, Record_id, Creator_id, Create_date, mmddDate, Modified_by_id, last_modify_date )
VALUES ('@Client_Id','@Contact_number', '@Type_id', '@Code_id', '@Last_code_id', '@DateCol', '@NumericCol', '@AlphanumericCol', '@Record_id', '@Creator_id', '@Create_date', '@mmddDate', '@Modified_by_id', '@last_modify_date' )
END;
Some help would be appreciated as im losing my sanity here :angry:
August 11, 2014 at 9:07 am
Three things immediately obvious:
1) This trigger can't handle multiple rows being inserted or updated.
2) A unique constraint on AlphanumericCol would probably be a better option
3) It's written more like an INSTEAD OF trigger than an AFTER trigger. When an AFTER trigger fires, the row has already been inserted. Your statement that this searches for values before inserting is false, by the point the trigger code runs, the insert has happened.
Why not a simple unique constraint?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2014 at 12:36 pm
I have to use a trigger.
What would you suggest I change in my coding
August 11, 2014 at 12:38 pm
I have to use a trigger.
What would you suggest I change in my coding
August 11, 2014 at 1:09 pm
Why not a unique constraint when all you're doing is enforcing uniqueness? Using a trigger for what a constraint does is poor design and a bad idea
As for what you need to change, the list of what you don't need to change would be shorter.
Main points:
- It should be an INSTEAD OF trigger, not an AFTER
- It needs to handle multiple inserts, not make assumptions about what row was added.
Start by deleting everything after
ALTER TRIGGER DUPLICATES
ON AMGR_User_Fields_Tbl
and then read up on Instead Of triggers and the pseudo-tables that are available inside triggers. That should get you started.
Edit: Oh, and I missed that your INSERT statement at the end would have inserted the literal string value "@Client_Id", etc, which is probably not quite what you wanted. Maybe also check up in Books Online the syntax for the two forms of insert, though the one you'll be needing here is the INSERT ..... SELECT
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2014 at 1:14 pm
julie.breetzke (8/11/2014)
I have to use a trigger.What would you suggest I change in my coding
Now for the next question, why do you have to use a trigger?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply