Creating a trigger that prevent the deletion

CGN007
Posted by CGN007 under Sql Server category on | Points: 40 | Views : 1483
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...!!!

Comments or Responses

Posted by: Muhsinathk on: 9/13/2012 Level:Bronze | Status: [Member] | Points: 10
A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Syntax of Triggers

The Syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

BEGIN

--- sql statements

END;

SQL Server 2000 has many types of triggers:

1. After Trigger
2. Multiple After Triggers
3.Instead Of Triggers
4. Mixing Triggers Type

Login to post response