Consider the table below,
employeeDetails
emp_id emp_name emp_address emp_dob
----------------------------------------------
1 RAHUL BANGALORE 1983-02-02
2 SACHIN MUMBAI 1990-03-031
3 SOURAV KOLKATTA 1980-01-01
Creating a trigger that prevent the deletion of employee 'SACHIN'
CREATE TRIGGER NOdeleteEmployeeSACHIN
ON employeeDetails
FOR DELETE
AS
DECLARE @testvalue VARCHAR(50);
SELECT @testvalue=emp_name FROM deleted;
IF(@testvalue='SACHIN')
BEGIN
PRINT 'Deleting SACHIN is not allowed';
ROLLBACK TRAN;
END
To check the trigger,Please run the following SQL
DELETE FROM employeeDetails WHERE emp_name='SACHIN'
Will get a message like this,
Deleting SACHIN is not allowed
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
That means the trigger is executing fine...!!!