Sql Triggers and Transactions
This example creates a trigger that inserts a record to a second table (MyAudit) after a record is inserted into the table the trigger is defined on (MyTable). Here the "inserted" table is a special table used by Microsoft SQL Server to store affected rows during INSERT and UPDATE statements; there is also a special "deleted" table that performs the same function for DELETE statements.
CREATE TRIGGER MyTrigger
-- insert audit record to MyAudit table
INSERT INTO MyAudit(MyTableId, User)
(SELECT MyTableId, CURRENT_USER FROM inserted)
Use Trigger to manage a "Recycle Bin" for deleted items
CREATE TRIGGER BooksDeleteTrigger
INSERT INTO BooksRecycleBin
INSERT INTO DeletedEmployees(EmployeeID, DateDeleted, User)
(SELECT 123, GetDate(), CURRENT_USER);
DELETE FROM Employees WHERE EmployeeID = 123;
When something fails in your transaction code and you want to undo it, you can rollback your transaction:
INSERT INTO Users(ID, Name, Age)
VALUES(1, 'Bob', 24)
DELETE FROM Users WHERE Name = 'Todd'
ATutorialHub Related Guide
panduranga gupta2021-07-05 07:03:13
good website for learning and help me a lot
The awsome website i am looking like for a long time, good work atutorialhub team keep doing
Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience
It is very helpful to students and easy to learn the concepts
Great job Tutorials are easy to understand Please make use of it
Zain Khan2021-09-18 15:07:23
Great content and customized courses.
Rudrakshi Bhatt2021-09-09 15:08:10
Well structured coursed and explained really well!
Pavana Somashekar2021-09-11 15:09:08
Good platform for beginners and learn a lot on this website