Hello,

I am trying to write a trigger that will not allow an insert to take place if specific column entry already exists. The following code is what I've come up with. How can I make it better? Any suggestions?

CREATE TRIGGER trForInsert ON [dbo].[ProjectInfo]
FOR INSERT
AS
declare @EmpNo int
declare @ExistingEmpNo int
set @EmpNo = (select EmpNo from Inserted)
set @ExistingEmpNo = (select EmpNo from ProjectInfo)

if(@EmpNo = @ExistingEmpNo )
begin
Rollback transaction
end

Thanks,
Arsi

*******(*)*******

__________________
*******(*)*******

Do you really have to use trigger? Not sure about your design but can you use a Unique Constraint? or if you are using stored procedure to insert then something like this

Create Procedure AddInfo
@Empno int
AS
Begin
IF NOT EXISTS (SELECT EmpNo FROM ProjectInfo WHERE EmpNo=@EmpNo)
INSERT INTO ProjectInfo VALUES (@EmpNo)
End

Hello Thanks for your help I appreciate it!

I would like to use a trigger, but I don't have to. what you showed me helped a little, but i can't complete it. What am I doing wrong?
CREATE TRIGGER [CheckNum] ON [dbo].[Table]
FOR INSERT, UPDATE
AS
declare @Ac nchar(10)
IF UPDATE(Acronym)
IF EXISTS (SELECT Acronym FROM INSERTED WHERE Acronym = @Ac)
BEGIN
ROLLBACK TRANSACTION
END

The above works if I say WHERE Acronym = 'tst' or any other specific data contained in that column.

Also, is there a reason why it would work on one db and not on another?

*******(*)*******