5
On another issue (In Trigger we have INSERTED, DELETED, but the "UPDATED"?) this doubt arose as to the form of the database execution, due to the form that the Trigger access an altered record.
As for a UPDATE (not in a Trigger), would like to know:
UPDATE the bank delete and then insert re-registration?7
It can be. It depends! It is not the same in all database management systems.
In a sense it is yes, but it doesn’t mean that it needs to be physically like this. It’s an implementation detail.
In databases that use MVCC is like this (most Dbs are or use a similar mechanism), it never writes over what exists, always creates a new one, so greatly facilitates competition and isolation.
And it can be more suitable to deal with changes in the key, after all the data are usually organized in trees with keys and values (the value is usually a tuple, an official term used in the area) and if the key changes the location of the tree where the tuple must be, it must delete where it is and insert it in a new location. It can be the primary key tree, or the secondary key tree.
But there are schemes that this is not so true. I know some banks that are not so. But they have other problems. Deletion itself is complicated and often it doesn’t even happen online, then the record to be deleted is only marked with invalid and in the future it is made a batch clean in everything that is marked, a kind of garbage collection.
But in secondary indexes I think it’s always like this. I don’t see how not to be in normal conditions. Not that that’s not possible, but it’s weird and it doesn’t seem like a very realistic solution. Understand that the normal table that everyone calls is usually the primary key itself, contrary to what many people think, it is also used to be an index.
But in general it’s more of a INSERT followed by a DELETE, even to be easier to deal with atomicity. Only when the insert is complete and done all it needs is that it can consider that the old one should be deleted.
In case of use of Trigger something new is inserted and after the end of the operation the old is deleted, so both are visible. Alias, this is one more reason to be separate operations.
See more about indexes:
Answered and given homework!
0
No, UPDATE is a change of a database field , which in its operation is not performed a delete before , this is visible when Voce makes a Rigger and Voce can take the information that is being changed and compare with the old (NEW and OLD) and Voce can also verify that the database has a senquential field called ID that it is not changed differently from when vc deletes and inserts
Browser other questions tagged sql database
You are not signed in. Login or sign up in order to post.
I know that in SQL Server it happens that way, have records in
DELETED, which correspond to the previous entry, andINSERTEDwhich correspond to the new registration.– João Martins
@Joãomartins where these DELETED and INSERTED records are stored?
– Luís Almeida
They are "tables" that are only available within the Trigger. Example:
SELECT * FROM insertedwill return the records that have been inserted in the table to which the Trigger is associated.– João Martins
@Joãomartins Important to have said, I changed the answer so it doesn’t look like it’s about Rigger!
– rbz