SQL insert, delete, update

Question

So i see that SQL has some tables which hold the newly inserted and deleted data from tables one may refer. I didn't notice such a table for updated data. I am currently working with triggers and i need to apply a trigger to an update. How do i do that?

USE [examene]
GO
/****** Object:  Trigger [dbo].[trig1]    Script Date: 6/8/2013 6:48:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[trig1] on [dbo].[participari]
after insert,update,delete
as
begin
    if (exists (select * from deleted))
        rollback

    if (exists (select * from inserted,proiecte
where inserted.idpr = proiecte.idpr
and deadline<dela union 
select * from inserted,proiecte
where inserted.idpr = proiecte.idpr and inserted.panala>proiecte.deadline))
        rollback
end

this is my trigger so far

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

There is no such thing as updated virtual table. When update occurs old values might be found in deleted and new values in inserted

Use the inserted and deleted Tables
The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table.

The deleted table and the trigger table ordinarily have no rows in common. The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

so basically all the update info is newly inserted after deleting the old entries? — morgred, Jun 08 '13 at 04:06
@user1889231 That only means that from trigger's point of view it's a pair of delete and insert operations. Included next paragraph from docs. — peterm, Jun 08 '13 at 04:19

SQL insert, delete, update

1 Answers1