RollBack in SQL

Question

I am having the ISSUE in ROLLBACK Have to delete the data from table using ROLLBACK in TRIGGER This is my table Table Name: Emp ID int (IDENTITY 11) PK NatID nvarchar(50) LoginID nvarchar(50) OrgID hierarchyid Level computed JobTitle nvarchar DOB date Mart_Status nchar(1) hiredate date

Manoj Bhoir · Answer

Hi You can use ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction. See this example : USE tempdb ; GO CREATE TABLE ValueTable ( [value] int ;) GO DECLARE @TransactionName varchar ( 20 ) = 'Transaction1' ; --The following statements start a named transaction --insert two rows and then roll back --the transaction named in the variable @TransactionName. --Another statement outside of the named transaction inserts two rows. --The query returns the results of the previous statements. BEGIN TRAN @TransactionName INSERT INTO ValueTable VALUES ( 1 ) ( 2 ); ROLLBACK TRAN @TransactionName ; INSERT INTO ValueTable VALUES ( 3 )( 4 ); SELECT [value] FROM ValueTable ; DROP TABLE ValueTable ; --Results --value ------------- --3 --4

Pankaj Kumar Choudhary · Answer

Hello @Shree can you provide some more details about your query(i.e.) 1. what kind of trigger you need(InsertUpdateDelete) 2. What will be the action type of trigger (After Instead Of) 3. At which condition Rollback Will occur with example (if possible)............