Sql Triggers and Transactions

Sql Triggers and Transactions
Nikhil
Published on 2021-08-15 06:26:45

CREATE TRIGGER

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
ON MyTable
AFTER INSERT
AS
BEGIN
-- insert audit record to MyAudit table
INSERT INTO MyAudit(MyTableId, User)
(SELECT MyTableId, CURRENT_USER FROM inserted)
END

Use Trigger to manage a "Recycle Bin" for deleted items

CREATE TRIGGER BooksDeleteTrigger
ON MyBooksDB.Books
AFTER DELETE
AS
INSERT INTO BooksRecycleBin
SELECT *
FROM deleted;
GO

Simple Transaction

BEGIN TRANSACTION
INSERT INTO DeletedEmployees(EmployeeID, DateDeleted, User)
(SELECT 123, GetDate(), CURRENT_USER);
DELETE FROM Employees WHERE EmployeeID = 123;
COMMIT TRANSACTION

Rollback Transaction

When something fails in your transaction code and you want to undo it, you can rollback your transaction:
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Users(ID, Name, Age)
VALUES(1, 'Bob', 24)
DELETE FROM Users WHERE Name = 'Todd'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

ATutorialHub Related Guide

Comments (8)

Leave a Comment

Your email address will not be published. Required fields are marked*

User Comments

html tutorial comments

panduranga gupta

2021-07-05 07:03:13

good website for learning and help me a lot

html tutorial comments

raju

2021-09-25 14:58:47

The awsome website i am looking like for a long time, good work atutorialhub team keep doing

html tutorial comments

Shivani

2021-09-01 15:03:56

Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

html tutorial comments

Harshitha

2021-09-10 15:05:45

It is very helpful to students and easy to learn the concepts

html tutorial comments

Sowmya

2021-09-14 15:06:41

Great job Tutorials are easy to understand Please make use of it

html tutorial comments

Zain Khan

2021-09-18 15:07:23

Great content and customized courses.

html tutorial comments

Rudrakshi Bhatt

2021-09-09 15:08:10

Well structured coursed and explained really well!

html tutorial comments

Pavana Somashekar

2021-09-11 15:09:08

Good platform for beginners and learn a lot on this website