October 30, 2013 at 7:17 am
I have this Update Trigger:
CREATE TRIGGER trgUpdateInsert
ON [dbo].[Direct_Test]
FOR UPDATE
AS
declare @DirectID int;
declare @DirectName varchar(100);
declare @DirectAmt decimal(10,2);
select @DirectID = i.Direct_ID from inserted i;
select @DirectName = i.Direct_Name from inserted i;
select @DirectAmt = i.Direct_Amt from inserted i;
if update(Direct_Name)
PRINT 'Direct_Name UPDATED'
if update(Direct_Amt)
PRINT 'Direct_Amt UPDATED'
GO
Can I change this trigger to update a record as it does, but if the record doesn't exist then to create one?
October 30, 2013 at 7:24 am
That sort of Upsert logic makes more sense in a stored procedure. Can you create a stored procedure to handle this case? If the @key already exists then update (if something is different) else insert.
To answer your question you could create an after trigger to handle the insert, but this may cause confusion later on. Keep in mind that if the record doesn't exist there will be nothing in the inserted or deleted tables, so you won't have the data that was trying to be updated to the missing record.
October 31, 2013 at 4:06 am
You trigger will work only when a single row is modified.
It won't handle multiple rows.
INSERTED represents a table and you have to account for multiple rows in it.
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply