Sql Server Triggers

Question

I have a table which have 4 fields.

Col1 Guid Primary-Key (FK to another table)
Col2 Guid Primary-Key (FK to another table)
Col3 Int Primary-Key
Col4 Int

Now i want to do this: when a new record inserted by user which have same value as a record exist before on the table like below:

There was this Record:

X Y Z 2

New record

X Y Z 3

Update existing record to below:

X Y Z 5

and also prevent from inserting duplicate record. I wrote very basic trigger. now help me to complete this. Thanks in advance. ;)

CREATE TRIGGER [Tbl].[TriggerName] 
ON  [dbo].[Tbl] 
AFTER INSERT, UPDATE
AS 
BEGIN
SET NOCOUNT ON;
UPDATE [dbo].[Tbl]
SET [dbo].[Tbl].[Col4] += [Inserted].[Col4]
From [dbo].[Tbl] INNER JOIN [Inserted] ON [dbo].[Tbl].[Col1] = [Inserted].[Col1] AND
                                          [dbo].[Tbl].[Col2] = [Inserted].[Col2]
WHERE [dbo].[Tbl].[Col3] = [Inserted].[Col3];
//How to prevent from inserting duplicate record?
END

Answer 1

If you're using SQL Server 2008 you can use MERGE

Answer 2

As Joe Stefanelli said, the BEST approach is to replace the direct inserts into the table by having an insert-or-update stored proc and revoking the INSERT perms on the table.

However, if you are dead set on the trigger, try adding the following to the trigger

DELETE [dbo].[Tbl]
From [dbo].[Tbl] INNER JOIN [Inserted] ON [dbo].[Tbl].[Col1] = [Inserted].[Col1] AND
                                          [dbo].[Tbl].[Col2] = [Inserted].[Col2]
WHERE [dbo].[Tbl].[Col3] = [Inserted].[Col3] AND [dbo].[Tbl].[Col4] = [Inserted].[Col4];

Answer 3

I'm going to consolidate my comments into an answer.

I would avoid using a trigger in this case. I believe your best approach is to write a stored procedure that you can call from your code. That stored procedure would perform the if exists... update... else insert... logic.