If you have write access to your SQL database in Polaris, any time you run an UPDATE, INSERT or DELETE in SQL Server, you should wrap it in a transaction. This gives you a chance to review the impact before making changes permanent.
How it works
-
BEGIN TRAN starts the transaction
-
Run your statement
-
COMMIT saves the change
-
ROLLBACK undoes it (up to the BEGIN TRAN part of the statement)
Nothing is permanent until you commit it.
Why this is important
Mistakes happen, even small ones like forgetting a WHERE clause can have huge and expensive consequences. Without a transaction, that mistake can update far more rows than intended, potentially requiring a costly restore from a backup. With a transaction, you can immediately roll it back.
Example
Adapted from: Bulk Update Patron Addresses Based on Given Address
BEGIN TRAN
UPDATE Polaris.Polaris.PatronAddresses
SET AddressID = 1234
WHERE AddressTypeID = 2
AND PatronID IN (
SELECT PatronID
FROM Polaris.Polaris.PatronRecordSets
WHERE RecordSetID = 5678
);
-- COMMIT; -- Uncomment or highlight and run TWICE, if everything looks right
-- ROLLBACK; -- Uncomment or highlight and run TWICE, if something looks wrong
Check the row count and sample records before committing.
Double it up
I also always recommend running the COMMIT or ROLLBACK command TWICE. You should get an error the second time. Why twice? Because if you don’t COMMIT or ROLLBACK you leave the transaction open which can crash your SQL server.
If you get in the habit of doing it twice, it is much less likely you’ll crash your server.
Caution on when to update
If you’re updating a busy table, like CircItemRecords, it is recommended you do this during non-work hours. Opening an exclusive lock on a busy table can cause major problems, even if you only have it locked for a few minutes.
Bottom line
Transactions should be your default for data changes. They turn risky updates into reversible ones and protect you from simple but costly mistakes.