SQL Server - Preventing duplicate column value inset in table using Trigger

Hi

Can anyone tell me how to prevent duplicate insetting values for   selected column (exp : id, name) in sql server 2005 manually

I am not required Unique contains.(I have to check multiple table Coolum )

I please give me Trigger from Preventing duplicate column value inset in table

 

 I tried this below code. using this I am unable to insert rows in slq server table.

 

create TRIGGER trg_test1 ON view_test

INSTEAD OF INSERT

AS

BEGIN

   

    IF   EXISTS ( select id,name from dbo.test group by  id,name HAVING COUNT(*) > 1)

        begin

              Print 'Cannot insert duplicate values!------ test'

         

              --RAISERROR('Cannot insert duplicate values!------ NSU test',1,1)    

              ROLLBACK TRANSACTION

         

        end

 

    else

   

    begin

    Print 'INSERT-------------! test'

    end

   

end

Hi Masuri,

You could try something like this:

CREATE TRIGGER trg_test1 ON view_test
INSTEAD OF INSERT
AS
BEGIN
    IF EXISTS (SELECT id, name FROM test AS a
      WHERE EXISTS (SELECT id, name FROM INSERTED AS b WHERE b.id = a.id AND b.name = a.name)
       )
    BEGIN
      PRINT 'Cannot insert duplicate values!------ test'
    END
    ELSE
    BEGIN
      INSERT INTO view_test (id, name)
      SELECT id, name FROM INSERTED
      PRINT 'Value inserted successfully!----- test'
    END
END
GO


The SELECT id, name FROM test AS a WHERE EXISTS (SELECT id, name FROM INSERTED AS b WHERE b.id = a.id AND b.name = a.name)
would check for the given id and name value, if it not exists. You could use your statement with group and havint count provided there will be no duplicates at all in that table, else it can return something, even if the value to be inserted would not really be a duplicate and thus stop it from being inserted.

Hope this helps,
Robert.

Thanks Dude !!!!

It Really work for me.

Thanks a Lot......