Creating a trigger that prevent the deletion

Posted by CGN007 under Sql Server category on 11/2/2012 | Points: 40 | Views : 1678

Post Code |

Search Codes |

Code Home

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...!!!

Alert Moderator

Bookmark It

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

Latest Code Snipptes