Reputation: 4341
Sql Server Triggers
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
Upvotes: 2
Views: 281
Answers (3)
Reputation: 135938
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.
Upvotes: 1
Reputation: 129559
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];
Upvotes: 1
