December 6, 2003 at 11:45 pm
Hello All,
I have a table with about 100000 rows.
When someone is inserting SSN on the ASP page
I invoke a stored procedure, which checks if that SSN already exists; and if exists - than it sends an Error to the ASP page, where it prevents the user from moving further (by showing a VBS message box).
If that SSN doesnot exist, than the same Stored Proc would insert SSN (& related data ) into the DB.
Everything works fine. No problem at all.
Few days ago I saw here another solution- using Instead of trigger with the following:
CREATE TRIGGER tr_verifyDuplicates
ON tblAL
INSTEAD OF INSERT
AS
BEGIN
INSERT tblAL (column1,column2,column3)
SELECT column1,column2,column3
FROM INSERTED i
WHERE NOT EXISTS (
SELECT * FROM tblAL
WHERE column1=i.column1
AND column2=i.column2
AND column3=i.column3
)
END
My question is: what would be more preferable method for checking duplicates in the SQL 2K database. Or there might be another way of doing that too.
Thanks
ad
December 8, 2003 at 8:24 am
Triggers are better in that anyone trying to insert data from any location including direct table inserts get caught where an SP only catches what comes thru it. Suppose someone writes another SP later and forgets to do the same check, it is then missed where a trigger will not. Also since should be unique I suggest maybe using a UNIQUE CONSTRAINT non-clustered index as opposed to a UNIQUE index since it works even better to prevent duplicates.
December 8, 2003 at 10:10 am
Besides Antares686 constraint which will guarantee data quality, you could write your procedure as
insert into Table_Name VALUES (@id,@value1)
Where Table_ID <> @ID
IF @@ROWCOUNT = 0
Return = - 1 --insert Failed
ELSE
Return = 0 --Success
That way you are using the procedure for BOTH checks and inserts!!
HTH
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply