CODEWITHSUNDEEP
sqlsql-serverdatabase
Tom Limpens
Tom Limpens

Reputation: 59

Only update the deleted row on a trigger

I have a commenting system in my application and I want to make a trigger that activates on delete of a Comment. I want the trigger to change the Comment text to [Deleted]. Here's what I have so far, how do I make sure it only affects the row that would have been deleted?

    Create trigger tr_Comment_On_Delete on Comment
Instead of delete
AS
begin
    update Comment
    SET CommentText = '[Deleted]'

end

Upvotes: 0

Views: 91

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Assuming you have a primary key on column id in your Comment table, you could refer to deleted table to get the specific row on which you want to act (perform an update).

CREATE TRIGGER tr_Comment_On_Delete on Comment
INSTEAD OF delete
AS
BEGIN
  UPDATE Comment
  SET CommentText = '[Deleted]'
  FROM Comment
  JOIN deleted d ON Comment.id = d.id
END

tested on SQL Server 2014 EE

Full test scenario (delete comment with id = 2):

create table Comment(id int, CommentText text);
insert into Comment values (1, 'asd'), (2, 'bcf');
select * from comment;
go

CREATE TRIGGER tr_Comment_On_Delete on Comment
INSTEAD OF delete
AS
begin
  UPDATE Comment
  SET CommentText = '[Deleted]'
  FROM Comment
  JOIN deleted d ON Comment.id = d.id

end
go

delete from comment where id = 2;
select * from comment;

Upvotes: 1

Related Questions